Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing the value of a textbox (MS Excel 2003)

    Hi All!

    Usually programming in Access, Excel continues to throw me for a loop. I have a userform that fills in the value of a textbox from a worksheet as it initializes. Then I also have code in the textbox's BeforeUpdate event, so that if a user puts in a new value, a calculation is made (actually a database call and several calculations) and new information shows up in the rest of the form.

    The problem is that it seems that on initialize, the form is also executing the BeforeUpdate code, so it seems to take a bunch of time for the form to open. I suppose it's not a huge problem, but I would prefer that the form open immediately with the existing worksheet values and wait to execute BeforeUpdate until the user actually changes the information in the textbox.

    My problem is that I can't figure out how to trigger Cancel on the BeforeUpdate, nor can I find anything that shows "OldValue" or "NewValue" of the textbox in Excel (so that at least I could test whether the data came from the worksheet on initialize or from user intervention).

    Anyone have any ideas?

    TIA!

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

    Re: Changing the value of a textbox (MS Excel 2003)

    You could use a module-level boolean variable to keep track of whether the form is being initialized:

    Private blnInit As Boolean

    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If blnInit Then
    blnInit = False
    Exit Sub
    End If
    ' Other code goes here
    MsgBox "BeforeUpdate"
    End Sub

    Private Sub UserForm_Initialize()
    blnInit = True
    ' Fill text box
    Me.TextBox1 = "Woody's Lounge"
    End Sub

    The Initialize event of the userform sets blnInit to True.
    The Before Update event of the text box checks whether blnInit is true. If so, it sets it to False (for the remainder of the session) and exits immediately.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the value of a textbox (MS Excel 2003)

    Hi Hans! That's exactly what I was trying to do! (except poorly)

    Thank you!!!

    PS. I love that you're advertising Woody's Lounge in code these days

Posting Permissions

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