VBA Excel - What methods / attributes can I use with ActiveSheet
I've looked at the MSDN page and also found this question helpful, but I'd like to know exactly how ActiveSheet behaves. Does this look like a Worksheet object? It seems to just return or reference a Worksheet object. Does the normal Worksheet methods and properties work with it?
I've used it in code I've copied from other sources, but I would like to understand what's going on under the hood.
thank
Bonus question: if I have a control on sheet2 then set the active sheet to sheet1 in the userform, can I set it back to sheet2 when the userform is closed? Essentially, can I change the sheet below my form to display / manipulate data while the form is active?
source to share
Does this look like a Worksheet object?
Yes ActiveSheet
- they are "like" a worksheet object, but they are not the same. ActiveSheet
can be " Worksheet ", " Dialogue sheet ", " Macro sheet MS Excel 4.0 " or " MS Excel 5.0 Dialogue sheet "
And therefore, use should always be avoided ActiveSheet
when working with worksheets. You may not be working with a sheet that you think.
Do the normal Worksheet methods and properties work with it?
If ActiveSheet
is Worksheet
, then yes. For example, below will work for worksheet, but not for " MS Excel 5.0 Dialogue Sheet "
Debug.Print ActiveSheet.Range("A1").Address
As for your bonus question, yes, you can set any Worksheet
to the active sheet, i.e. bring it to the foreground, provided it is Worksheet
not hidden. Otherwise, you will have to display it first and then activate it.
To make the worksheet active you can use this
ThisWorkbook.Sheets("Sheet2").Activate
In short, avoid using ActiveSheet
. Work with objects instead. INTERESTING TO READ
source to share