Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
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 -- __________________________________ HTH Bob "Stephen Newman" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
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 -- Regards, Nigel "Stephen Newman" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
On Mon, 14 Jul 2008 11:43:06 +0100, "Nigel"
wrote: 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? 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
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. -- __________________________________ HTH Bob "Rumplestiltskin" wrote in message ... On Mon, 14 Jul 2008 11:43:06 +0100, "Nigel" wrote: 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? 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
On Tue, 15 Jul 2008 08:54:31 +0100, "Bob Phillips"
wrote: 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. 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
I would remove the On Error and when adding textbox values use
Val(TextBox1.Text) etc., that should handle empty textboxes -- __________________________________ HTH Bob "Stephen Newman" wrote in message ... On Tue, 15 Jul 2008 08:54:31 +0100, "Bob Phillips" wrote: 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. 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Event Error
On Wed, 16 Jul 2008 10:51:09 +0100, "Bob Phillips"
wrote: 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. I would remove the On Error and when adding textbox values use Val(TextBox1.Text) etc., that should handle empty textboxes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exit event/textbox/frame | Excel Programming | |||
Textbox.change event not firing! | Excel Programming | |||
How To Get An Event To Run When I Exit A TextBox | Excel Programming | |||
help with textbox change event | Excel Programming | |||
Textbox change event | Excel Programming |