Ref to Wkbk Open Event (2003)

Topic

New Reply

I have set a workbook open event to capture 2 names ‘MyName’ & ‘BossName’ using:

Private Sub Workbook_Open()
MyName = InputBox(“Enter Your Name”)
BossName = InputBox(“Enter Your Line Managers Name”)
End Sub

I then want to reference the names in severeral worksheet ranges using =MyName & = BossName but am getting #NAME?

I know that I am being DULL here but please aleviate my pain!

Reply | Quote

Replies

How are MyName and BossName defined? Are they declared as global VBA variables in a code module, or are they defined names specified in Insert | Name | Define?
You can’t refer directly to VBA variables in a worksheet formula, and although you can assign the value of defined names using code, you cannot do treat them as if they’re variables.

Reply | Quote

If the names are created with Insert – name -define, you can use:

Private Sub Workbook_Open()
range(“MyName”) = InputBox(“Enter Your Name”)
range(“BossName”) = InputBox(“Enter Your Line Managers Name”)
End Sub

Steve

Reply | Quote

Thanks both, I failed to define the names. [stooooopid] Got it sorted now though!

Reply | Quote

How difficult would it be to strengthen this? I would like to remove the cancel option, and on clicking ok, build in error handler so that 2 names (first and surmame) must be input before proceeding? Also could this be done in one dialogue box instead of 2? I’m not sure if this can be achieved?

Reply | Quote

You can create a userform in the Visual Basic Editor with 2 text boxes and a command button with caption OK. The On Click event procedure for the command button would check whether the user has entered data, and if so, store the values and close the userform.
The Workbook_Open event procedure would simply show the userform.

Reply | Quote

Thankyou Hans.

note to self: must bypass phobia of userforms……

Reply | Quote

> must bypass phobia of userforms

They don’t bite!

Reply | Quote