VBA TextBox DirectCast Compile Error

saisis

New Member
Joined
Oct 21, 2014
Messages
9
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:
Code:
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
Code:
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?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Are you sure that's VBA code?

It looks more like VB to me.
 

saisis

New Member
Joined
Oct 21, 2014
Messages
9
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?
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
You could try something like this.
Code:
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

New Member
Joined
Oct 21, 2014
Messages
9

ADVERTISEMENT

You could try something like this.
Code:
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
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 21, 2014
Messages
9
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top