VBA Excel - What methods/attributes can I use with ActiveSheet

vba excel worksheet
JSM picture JSM · Sep 11, 2014 · Viewed 14k times · Source

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?

Answer

Siddharth Rout picture Siddharth Rout · Sep 11, 2014

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

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More