VBA code help please

[Robera]

Can anyone suggest VBA code that will allow me to 'synchronise' when I step through multiple sheets within a workbook i.e. if I am in Row15 on Sheet 1 can I automatically be taken to Row15 when I select Sheet 2, Sheet 3, Sheet 4 etc

I'm sure this would be a very useful 'utility' for a lot of people - perhaps there's an easy answer!!

 

[Datsmart]

Start the Macro Recorder.
Hold down the Ctrl Key while clicking the Sheets you want to work with.
Now click the common cell you want selected in each sheet.
Click a sheet tab other than the ones you want.
Stop the Macro Recorder.
The resutlant code will be the VBA code you are after.

 

[Joe Was]

The Recorder will not help much here, you need to do this with event code!
To be usefull the code needs to be dynamic, recorded code is static!

Add All the code below to the Special Module Named: "ThisWorkbook"
Note: The Public Definition: "Public mySelect$" must be the first statement in the "ThisWorkbook" module!
This code will work for any Range: a single cell or a selected group of cells!



Public mySelect$


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!

mySelect = Target.Address
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook module code!

Sh.Range(mySelect).Select
End Sub


Note this code is ON all the time to turn it on and off you need to add a control flag test to see if it should run or not. To do this replace the above code with this new code: Note: "Sub syncRng" is the On and Off switch for the code, it Toggles the code on and off!



Public mySelect$, mySync As Boolean


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!
If mySync = True Then mySelect = Target.Address Else: Exit Sub
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook module code!

If mySync = True Then Sh.Range(mySelect).Select Else: Exit Sub
End Sub


Sub syncRng()
'ThisWorkbook module code!

If mySync = True Then
mySync = False
Else
mySync = True
mySelect = ActiveCell.Address
End If
End Sub

 

[Joe Was]

This last modification will switch [toggle] "Synchrinised cells" On or Off and update the StatusBar [with the message: "Synchronised Cells is On!"] when you do toggle synchronised cells to On!

And, re-set everything when it is Off!



Public mySelect$, mySync As Boolean, stBarStat As Boolean, myStCnt%


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!
If mySync = True Then mySelect = Target.Address Else: Exit Sub
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook module code!

If mySync = True Then Sh.Range(mySelect).Select Else: Exit Sub
End Sub


Sub syncRng()
'ThisWorkbook module code!

If myStCnt < 1 Then stBarStat = Application.DisplayStatusBar
myStCnt = myStCnt + 1
Application.DisplayStatusBar = True

If mySync = True Then
mySync = False
Application.DisplayStatusBar = stBarStat
Application.StatusBar = False
Else
mySync = True
mySelect = ActiveCell.Address
Application.StatusBar = """Synchronised Cells is On!"""
End If
End Sub