Solved: Procedure "Sheet_Activate_1()" Does What?

[Cyberdude]

I was looking through the contents of the VBAX Code Library (neat stuff!), and I came across two procedures which I didn't understand the purpose of. The first is:
Sub Sheet_Activate_1()
Sheets(1).Activate
[A1].Activate
End Sub
What does this Sub do for you? It appears to activate (select?) the first worksheet in the workbook, then (if I understand the shorthand) select cell "A1". Why would one need a procedure to do that?? Is Activate in this context equivalent to Select? :dunno

[Jacob Hilderbrand]

This macro will activate the first sheet in the workbook then activate A1. In this case Activate is the same as Select.

I am not sure what the point of the macro is. Perhaps it is used at the end of a macro to select the first sheet and A1 so that will be what is selected when the macro is completed.

[Ken Puls]

Alternately, if you were to use it in a workbook open (and/or) other event, you could ensure that you were always returning the user to a specified place. I submitted something similar to the KB (by request) for exactly that purpose.

The only reason that I would write a separate procedure to do this would be to call it from multiple places, in the following format:
Call Sheet_Activate_1

The Call command isn't exactly required, but I prefer to put it in for self documenting purposes.

It's pretty much a "utility code" type of procedure, IMHO.

[Zack Barresse]

.. The Call command isn't exactly required ..
I think the word Ken was searching for was it's "implied". ;) As are things like Activesheet and ActiveWorkbook, etc.

Cyberdude, there is a difference between Activate and Select. The difference being you can only Activate one Object at a time. You cannot Activate two Ranges at one time. You also cannot Select any hidden sheet or Select more than one Range at a time, the terms coined for that is Union and Intersect, both having their own traits and purposes. Colo has a nice write up on his site about Activating and Selecting sheets and the differences. The link is here: http://puremis.net/excel/code/066.shtml

HTH :)

[Maurits]

I recently came upon that piece of code as well, it helps me get back to certain sheets of which the user changed the name. Sheets ("sheets1") won't work if the user changed the name to "data", for instance. While this code would.

Ofcourse i'm not sure how it would react if users changed the *location* of sheets, but that doesn't happen nearly as much as renaming.

[johnske]

I recently came upon that piece of code as well, it helps me get back to certain sheets of which the user changed the name. Sheets ("sheets1") won't work if the user changed the name to "data", for instance. While this code would.

Ofcourse i'm not sure how it would react if users changed the *location* of sheets, but that doesn't happen nearly as much as renaming.


Hi Maurits,

It works like this Sub Firstsheetactivate()
'activates 1st sheet positioned on the left in w/book
'(ignores given and code-names)
Sheets(1).Activate
End Sub

Sub sheet1activate1()
'activates sheet with given name "Sheet1"
'(ignores position & code-name, fails if sheet is re-named)
Sheets("Sheet1").Activate
End Sub



Sub sheet1activate2()
'activates sheet with the code-name sheet1
'ignores position and any given name (re-naming)
Sheet1.Activate
End Sub



Regards,
John

[Maurits]

How incredibly cool. Awesome john, thanks!

[Ken Puls]

Hey John,

It occurs to me that you should write an article to explain that. It's a very useful thing to know, and could be very helpful to many new VBA'ers. Well explained!

:thumb

[Cyberdude]

I agree! Very enlightening. Thanks again to you all. :friends:

[johnske]

Hey John,

It occurs to me that you should write an article to explain that. It's a very useful thing to know, and could be very helpful to many new VBA'ers. Well explained!

:thumb


:doh: Sigh.... No rest for the wicked is there. :devil: Will think about it...