us fr nl

VBA : Display: Synchronize displayrange and activecell when changing worksheets   us

Options: Save as PDF | Save attached file | Toggle line numbers


Type: sub
Added By: Rembo
Short Description:
This code allows one to navigate between worksheets, where the visible range as well as the activecell are kept the same. For example: if one scrolls down and right and selects cell X55 (Activecell) and sets the zoom percentage to 75%, then moves to sheet2, then in this sheet the same screen will be shown including cell X55 selected.
I created this for a user on JMT Excel Q&A board
Added: Aug 10 2005 at 2:47 PM
Modified: Jun 13 2013 at 3:44 PM
Related URLs


There are two pieces of code. The StoreVisRange and the SetVisRange sub 
procedures are copied in a module. The sub procedures Worksheet_Activate and  
Worksheet_SelectionChange are copied into each worksheet that you want the code 
to work for.


Formatted | Unformatted
  1. ' This goes into the worksheets that you want this to work for
  2. Private Sub Worksheet_Activate()
  3. SetVisRange
  4. End Sub
  6. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  7. StoreVisRange
  8. End Sub
  11. ' This goes into a module
  12. Public intZoompct As Integer
  13. Public intTLRow As Integer
  14. Public intTLCol As Integer
  15. Public strCelladdr As String
  17. Sub StoreVisRange()
  18. intZoompct = ActiveWindow.Zoom
  19. With ActiveWindow.VisibleRange.Cells(1, 1)
  20. intTLRow = .Row
  21. intTLCol = .Column
  22. End With
  23. strCelladdr = ActiveCell.Address
  24. End Sub
  26. Sub SetVisRange()
  27. With ActiveWindow
  28. .Zoom = intZoompct
  29. .ScrollRow = intTLRow
  30. .ScrollColumn = intTLCol
  31. End With
  32. Range(strCelladdr).Select
  33. End Sub

User comments :

Add a new comment   Back to Top
Atom Feed
Contact | About This Application | Scriptorium Website