CODEWITHSUNDEEP
vbaexcel
CaptainABC
CaptainABC

Reputation: 1239

Get Workbook Name Public Function

I have the below code where I attempt to capture the work book name when the Workbook is first open.

Public curWorkbook As Workbook

Function GetName()

Set curWorkbook = ActiveWorkbook

End Function

I need to add: curWorkbook.activate on a user form button that is used to add data, to ensure that the data is being added to the correct workbook in case the user has multiple files open at the same time.

When I run this code I get the RT 91: "Object variable or With block variable not set" error. No idea why?

Also, I would appreciate if someone can advise if it will better to have this code in the "ThisWorkbook" module or inside my "UserForm1" module.

Upvotes: 0

Views: 303

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

If you want to reference the workbook which was active when the form was opened, then you can use

Public curWorkbook As Workbook

at the top of your form module, and in the form's Activate event set the value to the ActiveWorkbook.

Once you have a reference to curWorkbook you can reference that workbook directly - you don't need to go through the name. Eg:

curWorkbook.Sheets(1)

is the same as

Workbooks(curWorkbook.Name).Sheets(1)

Upvotes: 2

Related Questions