Hi again

I have set up a user form for users to enter smaple data. The form has
a text box for entering the sample number and I am using various code
to update the data as the sample number is changed using the
textbox.change event. All works well if I stick to stepping one sample
at a time, however if the user wants to enter a sample number and view
the data I run into problems! If the user wants to view say sample 54
and there is no 5 then the code falls over (no erros just reports that
sample 5 does not exist) From what I can gather the change event works
on each key stroke.

I cant find a textbox event to delay or allow multiple key stokes
before acting. Is there anohter way to achive what im tryin to do??

using Excel 2003

Thanks in Advance

Ian

Ian,

If you used a combobox instead you could load all the different sample
numbers into it when you open the form. Then they can only pick valid
sample numbers and you could update your data on the Combobox_Change event.
A rough example is below and assumes you have a Userform with "ComboBox1" on
it.

Note that you can add items from a worksheet range or array or other ways
much more efficiently. The Additem code below is just for demonstration:

Private Sub UserForm_Initialize()

With Me.ComboBox1
.AddItem 3
.AddItem 4
.AddItem 5
.AddItem 54
.AddItem 554
.ListIndex = 0 'fills box with first item in list
.MatchEntry = fmMatchEntryFirstLetter 'skips to next item with this
first letter
.MatchRequired = True 'so they can't enter a value not in list
End With

End Sub

Private Sub ComboBox1_Change()

MsgBox "cbox changed"

End Sub

"Ian Mangelsdorf" wrote in message
om...
Hi again

I have set up a user form for users to enter smaple data. The form has
a text box for entering the sample number and I am using various code
to update the data as the sample number is changed using the
textbox.change event. All works well if I stick to stepping one sample
at a time, however if the user wants to enter a sample number and view
the data I run into problems! If the user wants to view say sample 54
and there is no 5 then the code falls over (no erros just reports that
sample 5 does not exist) From what I can gather the change event works
on each key stroke.

I cant find a textbox event to delay or allow multiple key stokes
before acting. Is there anohter way to achive what im tryin to do??

using Excel 2003

Thanks in Advance

Ian

A number of ways.

You could try the AfterUpdate or Exit events, although this assumes another
control to move to.

Another way is to use the Keydown event, and trap for enter (KeyCode of 13)
and run your code when that is pressed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ian Mangelsdorf" wrote in message
om...
Hi again

I have set up a user form for users to enter smaple data. The form has
a text box for entering the sample number and I am using various code
to update the data as the sample number is changed using the
textbox.change event. All works well if I stick to stepping one sample
at a time, however if the user wants to enter a sample number and view
the data I run into problems! If the user wants to view say sample 54
and there is no 5 then the code falls over (no erros just reports that
sample 5 does not exist) From what I can gather the change event works
on each key stroke.

I cant find a textbox event to delay or allow multiple key stokes
before acting. Is there anohter way to achive what im tryin to do??

using Excel 2003

Thanks in Advance

Ian