VBA TextBox DirectCast Compile Error

[saisis]

I am running Excel 2010 and am trying to run this macro so that when a certain string is typed into any of five textboxes, a label's caption will change. For example:

Dim tbNum as Integer
Dim tb as TextBox = DirectCast(Me.Contols(“Textbox” & tbNum.ToString), TextBox)

Private Sub TextBox1_Change()
	txtChange
End Sub

Private Sub TextBox2_Change()
	txtChange
End Sub

Private Sub TextBox3_Change()
	txtChange
End Sub

Private Sub TextBox4_Change()
	txtChange
End Sub

Private Sub TextBox5_Change()
	txtChange
End Sub

Sub txtChange ()
	If me.tb.text  = “123” then
		Label1.Caption = “Hello”
	End If
	If me.tb.text = “456” then
		Label1.Caption = “Good-Bye”
	End If
End Sub

Whenever I finish typing

Dim tb as TextBox = DirectCast(Me.Contols(“Textbox” & tbNum.ToString), TextBox)

I get an error message saying: “Compile Error: Expected: end of statement” and I can’t run without same error message showing and the “=” is highlighted in string. What is it I need to do to get “tb” to be recognized as TextBox object?

 

[Norie]

Are you sure that's VBA code?

It looks more like VB to me.

 

[saisis]

It's possible. I searched for similar inquiries online as thought the above coding would be the most useful but have been trying to run it unsuccessfully.
Eventually, the text in the textbox will go into excel cells once the form closes, but I'm having a hard time just building this first step.
How should it be converted to better suit VBA?

 

[Norie]

You could try something like this.

Private Sub TextBox1_Change()
	txtChange TextBox1
End Sub

Private Sub TextBox2_Change()
	txtChange TextBox2
End Sub

Private Sub TextBox3_Change()
	txtChange TextBox3
End Sub

Private Sub TextBox4_Change()
	txtChange TextBox4
End Sub

Private Sub TextBox5_Change()
	txtChange TextBox5
End Sub

Sub txtChange (tb As MSForms.TextBox)
	If tb.text  = “123” then
		Me.Label1.Caption = “Hello”
	End If
	If tb.text = “456” then
		Me.Label1.Caption = “Good-Bye”
	End If
End Sub

 

[saisis]

You could try something like this.

Private Sub TextBox1_Change()
	txtChange TextBox1
End Sub

Private Sub TextBox2_Change()
	txtChange TextBox2
End Sub

Private Sub TextBox3_Change()
	txtChange TextBox3
End Sub

Private Sub TextBox4_Change()
	txtChange TextBox4
End Sub

Private Sub TextBox5_Change()
	txtChange TextBox5
End Sub

Sub txtChange (tb As MSForms.TextBox)
	If tb.text  = “123” then
		Me.Label1.Caption = “Hello”
	End If
	If tb.text = “456” then
		Me.Label1.Caption = “Good-Bye”
	End If
End Sub

Thank you, it helped a little.
I added

Else: Me.Label1.Caption = "Text Will Change Here"

for each IF statement in txtChange sub because as soon I would start typing in a textbox, I'd get an error saying there was no else statement.
However, the label caption won't change to say "hello" or "good-bye" once the corresponding values are entered in a textbox.

 

[Norie]

Do you mean the code I posted doesn't work?

If worked fine for me, the only problem were the strange quote marks - “.

Once I'd replaced those with standard quotes everything worked fine.

 

[saisis]

Do you mean the code I posted doesn't work?

If worked fine for me, the only problem were the strange quote marks - “.

Once I'd replaced those with standard quotes everything worked fine.

Thank you for noting that in your post! It does work once the strange “ are replaced with ". Thank you! I never would have caught that otherwise