Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ref to Wkbk Open Event (2003)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ref to Wkbk Open Event (2003)

    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ref to Wkbk Open Event (2003)

    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

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ref to Wkbk Open Event (2003)

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

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ref to Wkbk Open Event (2003)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ref to Wkbk Open Event (2003)

    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.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ref to Wkbk Open Event (2003)

    Thankyou Hans.

    note to self: must bypass phobia of userforms...... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ref to Wkbk Open Event (2003)

    > must bypass phobia of userforms

    They don't bite!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •