How do I return to the initial selected cell after running a macro that changes the selected cell?
How do I return to the initial selected cell after running a macro that changes the selected cell?
Save the address before you move and re-select it afterwards.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks, I'm new to VBA. It seems like I would need a line before and after my code. How would you write that?
I think it may be something like this:
[VBA]
Dim InitialCell As Range
Set InitialCell = Selection
'Whatever code you are running goes here
InitialCell.Select
[/VBA]
Greetings Enrique,
Just by example of using the address as Bob stated:
[vba]
Sub SeloldCell()
Dim strInitialCell As String
'// before selecting other cells
strInitialCell = ActiveCell.Address
'// Do whatever that selects other cells...
Range("B20").Select
Selection.Interior.ColorIndex = 3
'// reselect the cell that was initially the active cell
Range(strInitialCell).Select
End Sub
[/vba]
...or by setting a reference to the activecell (before whatever else we're doing)
[vba]
Sub SelOldCell_2()
Dim rngOldCell As Range
'// Set an object reference to the 'old' cell
Set rngOldCell = ActiveCell
Range("B20").Select
Selection.Interior.ColorIndex = 7
rngOldCell.Select
End Sub
[/vba]
...similar to what X10A showed. I would note that if you use Selection at the start, then if a range of cells were initially selected, the same range would get re-selected of course.
Although you did not ask, I would only mention that in most cases, selecting cells is not necessary, such like:
[vba]
Sub SkipSelecting()
Range("I1").Select
MsgBox ActiveCell.Address
'// ...but in most cases, you really do not need to be selectting other //
'// cells/sheets.... //
Range("B20").Interior.ColorIndex = 12
End Sub[/vba]
Hope that helps :-)
Mark
Thanks Mark, X10A, and xld. Your suggestions did the trick!