Help with code

[JMay]

When I click my cb1 (below) my cursor jumps inside tb1 - FINE OK

Private Sub CommandButton1_Click()
Worksheets("Sheet1").TextBox1.Activate
End Sub

But If I type Mary -- nothing happens << but should get message


Private Sub TextBox1_Change()
If Not Left(Worksheets("Sheet1").TextBox1.Text, 1) = "A" Or
Left(Worksheets("Sheet1").TextBox1.Text, 1) = "B" Then
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
End If
TextBox1.Text = ""
End Sub

 

[JMay]

**CORRECTION**
As I type the first character into the textbox the message pops up,
when I click OK, it pops up again. Grrrrrrrr

I'm missing understanding something BIG here.

Help !!

 

[John Bundy]

It works fine for me. Where are you posting the code? The only thing i would
do differently is put the if statement on two lines to make it easier to trap
for other issues. Below i fix it so a is acceptable not just A and also it
doesn't create the error everytime you blank it out.

Private Sub CommandButton1_Click()
Worksheets("Sheet1").TextBox1.Activate

End Sub
Private Sub TextBox1_Change()
If Len(Worksheets("Sheet1").TextBox1.Text) And
UCase(Left(Worksheets("Sheet1").TextBox1.Text, 1)) <> "A" Then
If Len(Worksheets("Sheet1").TextBox1.Text) And
UCase(Left(Worksheets("Sheet1").TextBox1.Text, 1)) <> "B" Then
TextBox1.Text = ""
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
End If
End If
End Sub

 

[John Bundy]

i dont see my answer here so i will post again. You need to make a few
changes that i think will help. It stops the double msgbox issue and lets you
use caps or no caps

Private Sub CommandButton1_Click()
Worksheets("Sheet1").TextBox1.Activate

End Sub
Private Sub TextBox1_Change()
If Len(Worksheets("Sheet1").TextBox1.Text) And
UCase(Left(Worksheets("Sheet1").TextBox1.Text, 1)) <> "A" Then
If Len(Worksheets("Sheet1").TextBox1.Text) And
UCase(Left(Worksheets("Sheet1").TextBox1.Text, 1)) <> "B" Then
TextBox1.Text = ""
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
End If
End If
End Sub

 

[Mike H]

Hi,

It works partly for me but becaue you are clearing the textbox the change
event will fire twice. Once for incorrect input and once when you clear it.
I'd do it like this

Private Sub TextBox1_Change()
If Left(Worksheets("Sheet1").TextBox1.Text, 1) <> "A" And _
Left(Worksheets("Sheet1").TextBox1.Text, 1) <> "B" _
And TextBox1.Text <> "" Then
TextBox1.Text = ""
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
End If
End Sub

Mike

 

[JMay]

John I copied your code in exactly, but still have the same problem.

What is condition of the Len() statement(s)? I don't see anything..

Thanks!

 

[John Bundy]

If this stuff isn't working i am thinking you have the code in the
ThisWorkbook module, the buttonclick has to be in the sheet module that you
have the button on. Turn on design mode, double click the button and see if
it takes you to the code. The len i accidentally left off =1 you just want to
provide the error if there is 1 letter, not when its blank.

 

[JLGWhiz]

I think Mike is on to it. I got pretty good results with the code below. It
only fires twice if something other that A or B is entered. Note that the Or
was changed to And.

Private Sub TextBox1_Change()
If Not Left(UCase(Worksheets("Sheet1").TextBox1.Text), 1) = "A" And Not
Left(UCase(Worksheets("Sheet1").TextBox1.Text), 1) = "B" Then
TextBox1.Text = ""
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
End If
End Sub

 

[Rick Rothstein]

I have not looked into the functionality problem yet, so this message does
not address that aspect of the thread. All I am doing here is offering a
simplification for the If-Then statements being used so far. Instead
statements like this...

If Not Left(UCase(Worksheets("Sheet1").TextBox1.Text), 1) = "A" And Not
Left(UCase(Worksheets("Sheet1").TextBox1.Text), 1) = "B" Then

use this much simpler equivalent statement....

If Worksheets("Sheet1").TextBox1.Text Like "[!ABab]*" Then

The Like operator can be a very powerful tool at times and this is one of
those times.

 

[Dave Peterson]

You've used application.enableevents in worksheet code to stop a procedure (say
the _change event) that changes something from calling itself, right?

Well, you can do the same thing with these kinds of controls--but you have to
take care of them yourself:

Option Explicit
Dim BlkProc As Boolean
Private Sub CommandButton1_Click()
Me.TextBox1.Activate
End Sub
Private Sub TextBox1_Change()
If BlkProc = True Then Exit Sub

If Left(Me.TextBox1.Text, 1) = "A" _
Or Left(Me.TextBox1.Text, 1) = "B" Then
'ok
Else
MsgBox "Text Input must begin with either an ""A"" or ""B"", Try Again"
BlkProc = True
TextBox1.Text = ""
BlkProc = False
End If
End Sub

ps. Your If statement seemed weird to me, so I changed it to what I thought you
wanted <bg>. And your code doesn't accept lower case a or b.

 

[AndrewCerritos]

Hi, I use KeyUp event to check if the first character entered is "A" or "B".
I noticed that need to check and ignore keycode=8, otherwise when type
backspace, it will display the message again.


Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 8 Then Exit Sub
If Left(TextBox1.Text, 1) = "A" Or Left(TextBox1.Text, 1) = "B" Then
'
' OK
'
Else
MsgBox "Text Input must begin with either an ""A"" or ""B"". Try
again."
End If

End Sub

--AC