Keeping the same cell when switching worksheets?

Welcome to the Board!

I believe that this is courtesy Tom Urtis from a few years ago.

In a new workbook, right-click on the Excel icon (next to "File") and select View-->Code.

Paste the following in the new window that opens on the right:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetSelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Sheet1.Range("IV65536").Value = ActiveCell.Address
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetActivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)
    Range(Sheet1.Range("IV65536").Value).Activate
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

ALT+Q to exit back to Excel and try it.

HTH,

Smitty

Smitty,

I was trying to do something like that but using a global variable instead of a Cell holding the variable.

Any Idea how to do that?


Edited ---

NM, I figured it out

Nice.

I think you'll want to add an error trap for if mycell doesn't become defined, like opening a sheet and immediately moving to another one without creating a new active cell.

Smitty

Uh, duuhhhhh, dooohhhhh @$#%$#!!!! Smitty and jonmo1 are the same cartoon character! Yowsa!

Perhaps do mycell = ActiveCell.Address on Workbook open as well.

[Edited because smitty's two names overflowed my memory banks causing an array content overflow and mental GPF]

Don't you mean pennysaver and jonmo1 are using the same character?

ok, back from lunch...

Good idea on the error trap... I would think defining it upon opening the workbook would work..

Don't you mean pennysaver and jonmo1 are using the same character?

Click to expand...

Okay, you're right, I fixed that now. The color vs. B/W really is a leap to connect for my little mind. Had the 2 posts not happened consecutively, and on a subject that I bothered to read, I would never have made that sick connection!

Man, the pages load slowly today.

Changing the code to something like this will let you select more than one cell and keep that range on all sheets:

'Define "myCurCell" at the top of the "ThisWorkbook" code module code only!
Public myCurCell$

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

On Error GoTo myErr
Set Sh = ActiveSheet

Sh.Range(myCurCell).Select
GoTo myEnd

myErr:
MsgBox "Lost the current cell address!" & vbLf & vbLf & _
"ReSetting the active cell to the default ""A1""", vbCritical + vbOKOnly, "Address Error!"

Sh.Range("A1").Select

myEnd:
End Sub

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

On Error GoTo myErr
Set Sh = ActiveSheet

myCurCell = Target.Address
GoTo myEnd

myErr:
Sh.Range("A1").Select

myEnd:
End Sub

actually, no need to use the open event...just use an If