TextBox Event Error

[Stephen Newman]

Could somebody explain why this throws an error please?

Private Sub TextBox1_Change()
i = 1
MsgBox TextBox & i & .Value
End Sub

I get this error at .Value:

Compile error:
Invalid or unqualified reference.

If posible, could the correct syntax for writing this be provided?

 

[Bob Phillips]

Because you are trying to use a control like a variable

Private Sub TextBox1_Change()
i = 1
MsgBox Me.Controls("TextBox" & i).Value
End Sub

 

[Nigel]

You have not fully referenced the textbox, but I think you are also trying
to create a reference to a textbox as well?

Assuming this is a UserForm control

Private Sub TextBox1_Change()
Dim i As Integer
i = 1
MsgBox Me.Controls("TextBox" & i).Value
End Sub

 

[Rumplestiltskin]

On Mon, 14 Jul 2008 11:43:06 +0100, "Nigel"

I ended up doing something different because I needed to access the
value of several text boxes simultaneously.

Sub GetTextBoxValues()
Dim i
i=Cells(Columns(1).Find("Total").Row,Rows(1).Find("AName").Column).Value
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox And ctrl.Visible = True Then i = i -
ctrl.Value
If i < 0 Then ctrl.Value = Null
Next ctrl
Label1.Caption = i
End Sub

However, now it throws an Error 13: Type mismatch.

I've read here that textboxes contain strings, not numberic values,
and I'm assuming that's where the error lies. How can I Dim the values
to avoid this error?

 

[Bob Phillips]

It works okay if you input numbers, held as strings or not, so you must be
inputting text. You should validate the input before running that code.

 

[Stephen Newman]

On Tue, 15 Jul 2008 08:54:31 +0100, "Bob Phillips"

I figured out the error occurs when the For Each loop tries to add
data from a text box that, as yet, has none, therefore firing the
mismatch error. The code I am attempting to write adds controls
dynamically, depending on the value of adjacent cells below several
headings. I only posted the offending code snippet.

I added:

On Error Resume Next

immediately below the For Each and it runs well now, doing exactly
what I anticipated. I'm assuming this ingores the error when there's
no data in a TextBox, and completes the routine when there is. I
really try to avoid using On Error when I don't understand why the
error occurs. I always fear it isn't doing what I intended it to do.

I did have input validation in place to prevent non numerical
characters, and added a decimal point provision to a TextBox class
routine, thanks to an ancient Rob Bovey post which detailed a class
that is implemented when the TextBox KeyPress event is triggered. I
just modified it a little for KeyDown so the backspace could be
trapped. I fussed with it for hours.

Most, in fact all the information I used, came from this group.

Thanks for your input.

 

[Bob Phillips]

I would remove the On Error and when adding textbox values use

Val(TextBox1.Text) etc., that should handle empty textboxes

 

[notprovided]

On Wed, 16 Jul 2008 10:51:09 +0100, "Bob Phillips"

Thanks for the tip Bob. I had actually never used that function
before, so it took me some time to figure out how to make it work.

I've shed the On Error statement now, and it works well. I guess when
you mentioned validation before I assumed you meant validating the
textBox input, and not validating the text.

Thanks again.