Changing the value of a textbox (MS Excel 2003)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote