Assign workbook to variable

0
votes

I want to set ActiveWorkbook to a variable When workbook opens simultaneously ActiveWorkbook assign to a variable and that variable i can use in whole VBA excel project. I tried to assigned in ThisWorkbook excel object on Workbook_open() function but it does not work.I provide that code below.

Private Sub Workbook_Open() On Error Resume Next Set WRBK = ActiveWorkbook #If Mac Then #Else 'unprotectVBProjProp UnlockVBA ' Sujith ID: 12482 AddReferences ' Sujith ID: 12482 ' protectVBProjProp #End If 'MsgBox "xla Workbook opened" Set eventInstance = New bwEvents End Sub

So how can i set activeworkbook to a variable??

1
excelvba

1 Answers

0
votes

I am not so sure what are all the commands in the middle, like #If Mac Then , and UnlockVBA.

If you want to set the ActiveWorkbook to object WRBK, you will need to define WRBK in a regulare module as Public, and then use something like the code below:

Code in ThisWorkbook Module

Private Sub Workbook_Open()

    Set WRBK = ActiveWorkbook

    TestWorkbookName ' call sub <--  this is just to test the the workbook was assigned correctly

End Sub

Code in Regular Module

Option Explicit

Public WRBK As Workbook

Sub TestWorkbookName()

MsgBox WRBK.Name

End Sub