5 Ways To Activate Workbook In Excel

Summary:

In this tutorial, we will mainly discuss different ways to activate workbook in Excel. Apart from that, you will also get to know what the need to activate the workbook in Excel and things to check before activating Excel workbook.

What’s The Need To Activate Workbook In Excel?

Suppose you have 5 worksheets, and you need to do some tasks in sheets 3. So as to perform any task on that specific sheet you need to activate it.

Well, there are several ways through which you can activate the workbook in Excel.

Things To Check Before Activating A Workbook

If you are trying to activate such a workbook that is not opened, then VBA will throw the error.  To fix this issue, just check the workbook name first after then activates it.

Sub vba_activate_workbook()

Dim wb As Workbook

For Each wb In Workbooks

If wb.Name = “Book3.xlsx” Then

wb.Activate

MsgBox “Workbook found and activated”

Exit Sub

End If

Next wb

MsgBox “Not found”

End Sub

Well by using the above-given code, you can easily specify the workbook name. After that, it will check for that specific workbook in all the opened workbooks. Once the is workbook fetched, it will activate it.

Notes

• When you are using the name of the workbook make sure to use the correct file extension
• If you want to activate a workbook that is not yet saved, then you need to use only the name of that workbook without suffixing the file extension.

How To Activate Workbook In Excel?

Trick  1#  Workbook.Activate method

You can use this method to activate a worksheet or workbook only if you are pretty sure about the workbook name. Suppose, the name of your excel workbook which you need to activate is “Book2” and it has a sheet with the name “Sheet1”.

Then by using the following code you can select the required workbook and sheet and the n activate it using the Excel VBA.

Syntax

expression.Activate

Here expression is a variable that represents the Excel Workbook object.

Remarks

This activates workbook method won’t execute any Auto_Activate or Auto_Deactivate macros which is attached to Excel workbook. In order to run these macros, you need to use the RunAutoMacros method.

Code To Use:

Sub Activate_Workbook()

    ‘Activate Workbook

    Workbooks(“Book2.xls”).Activate

    ‘Activate Worksheet

    Workbooks(“Book2.xls”).Sheets(“Sheet1”).Activate

End Sub

How To Use:

1. At first, open your Excel workbook.
2. After that press the Alt+F11 button from your keyboard. This will open the VBA Editor.
3. Now go to hit the Insert tab from the menu bar and insert new module.
4. Copy the above code and then paste it into this opened code window.
5. Press the F5 button from your keyboard to see the output.
6. After that just save this file as a macro-enabled workbook.

Trick  2# Activate Workbook or Worksheet Using Object

Another method to activate the workbook in Excel is by using the object. For this, you need to create an object for the worksheet and workbook first. After then assign the workbook to any object and choose the workbook having the VBA to execute the following procedures using the Excel macros.

Code:

 Sub Activate_Workbook_Using_Object()

 ‘Variable Declaration

Dim WrkBk As Workbook

Dim WrkSht As Worksheet

‘Create Object for Workbook

Set WrkBk = Workbooks.Add

 ‘Create Object for Worksheet

Set WrkSht = WrkBk.Sheets(“Sheet1”)

‘Activate Sheet

WrkSht.Activate

End Sub

Explanation:

• In the above, I have declared 2 objects for the worksheet and workbook.
• Added a new workbook and assign it to the workbook object.
• Also, set the Worksheet to the worksheet object.
• Activated the Excel worksheet by referencing the worksheet object.

Instructions:

1. At first, open your Excel workbook.
2. After that press the Alt+F11 button from your keyboard. This will open the VBA Editor.
3. Now go to hit the Insert tab from the menu bar and insert new module.
4. Copy the above code and then paste it into this opened code window.
5. Press the F5 button from your keyboard to see the output.
6. After that just save this file as a macro-enabled workbook.

Trick  3#  Activate A Workbook By Using The Number

If you have opened multiple workbooks then all those Excel workbooks are components of the workbook collection. Each workbook in this collection is having a number using which you can easily reference it in the activate method. Here is the code that you can use.

Sub vba_activate_workbook()

Workbooks(2).Activate

End Sub

If in case you are trying to activate such workbook using the number which doesn’t exist, then VBA code will throw Subscript out of Range: Run-time error ‘9’.

Trick  4#  Activate workbook using ThisWorkbook property

Another method of activating the Excel workbook is by using ThisWorkbook property.

Suppose you have opened 5 workbooks simultaneously but you are working only on the “Book1.xlsm”. Thus, when you execute the below-mentioned code, it will activate only the “Book1.xlsm”.

 Sub vba_activate_workbook()·

 ThisWorkbook.Activate·

End Sub

Trick  5#  VBA to Select Workbook or Worksheet in Excel

You have multiple worksheets in your excel workbook then you can make a selection for the sheet you need by using VBA.

You can also choose the required workbook in case several workbooks are opened.

Well, it’s a good trick to set the required worksheet or workbook to the object and refer to it whenever you need it. This works great in accessing the workbook or worksheet without activating or selecting it.

Code:

Sub Activate_Workbook_Using_Object()

    ‘Variable Declaration

    Dim wbAs Workbook

    Dim wsAs Worksheet

    ‘Select any Workbook and assign to an Object

    Set wb= Workbooks(“Book1.xlsm”)

    ‘Create Object for Worksheet

    Set ws= wb.Sheets(“SheetName”)

  ‘VBA to Select or Activate the required Workbook

    wb.Activate

    ‘VBA to Select or Activate the required Sheet

    ws.Activate

End Sub

Difference Between Active Workbook and ThisWorkbook In Excel VBA

Many VBA coders use the text ThisWorkbook and Active Workbook very frequently in their coding.

Being a learner or reader, it’s very important to know the basics between these two. Here some of the differences outlined so just check it out.

Active Workbook: 

• The active workbook is not compulsorily the workbook in which you are writing the code at that time. If several workbooks are opened but only the workbook which is appearing currently on your screen is counted as Active Workbook.
• Use active in the VBA coding results in several errors and confusion. As we don’t know which workbook is active until and unless we mention the name of the workbook just before the word Active Workbook.

ThisWorkbook:

• Whereas ThisWorkbook is necessarily that workbook where you are writing the code.
• ThisWorkbook never goes wrong because this doesn’t matter which workbook is active.  It takes workbook reference in the code section.

Final Verdict:

You can opt for any of the above-listed methods to activate Excel workbook. According to me, using the activate method in VBA to choose the required worksheet or workbook through VBA coding and perform your required tasks is the easiest one.

If you know any other ways to activate workbook in Excel then do share it with us in our comment section.