4

I have looked at the MSDN page, and also found this question helpful, but I would like to know exactly how ActiveSheet behaves. Is it like a Worksheet object? It sound like it just returns or references a Worksheet object. Do normal Worksheet methods and properties work with it?

I have used it in code I copied from other sources, but I would like to understand what is happening under the hood.

Thanks

Bonus question: If I have a control on sheet2, then set active sheet to sheet1 in a userform, can I then set it back to sheet2 when closing the userform? In essence, can I change the sheet below my form to display/manipulate data while the form is active?

Community
  • 1
  • 1
JSM
  • 215
  • 1
  • 5
  • 20

1 Answers1

6

Is it like a Worksheet object?

Yes ActiveSheet is "Like" a worksheet object but they are not the same. ActiveSheet can be a "Worksheet", "Chart Sheet", "MS Excel 4.0 Macro Sheet" or "MS Excel 5.0 Dialog Sheet"

And hence one should always avoid using Activesheet while working with worksheets. You may not be working with the sheet you think you are.

Do normal Worksheet methods and properties work with it?

If the ActiveSheet is a Worksheet then yes. For example, the below will work for a worksheet but not for "MS Excel 5.0 Dialog Sheet"

Debug.Print ActiveSheet.Range("A1").Address

Regarding your bonus question, yes you can set any Worksheet to active sheet, i.e. bring it to the front, provided that the Worksheet is not hidden. Else you will have to unhide it first and then activate it.

To make a worksheet active, you may use this

ThisWorkbook.Sheets("Sheet2").Activate

In a nutshell, avoid using ActiveSheet. Work with objects instead. INTERESTING READ

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks. I'll keep your recommendation in mind. It is always nice to see what some of the best practices are. – JSM Sep 12 '14 at 16:39