Help with code restricting TextBox entry

[jeff]

1st off, I can't believe all this can't be handled within the TextBox
Properties selections. I've looked, but can't see anything on this.

This is something I’ve had help on, but I’m trying to tweak it to get
just what I want.
What I’m trying to do is to require the user to enter a number in
TextBox6, and the user cannot leave it blank.
Here’s what I have so far to help in this:

‘This takes care to not allow an alpha to be entered in TextBox6. This
works fine.
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 9, 27, 45, 46, 48 To 57
Case Else
Beep
KeyAscii = 0
End Select
End Sub

This part below is what I’m having a problem with. This makes sure
TextBox6 is not left blank. It works fine, except that I would like to
let the user Cancel the whole UserForm routine if he wants. Maybe he
doesn’t have a number to enter for TextBox6 at the time, and would
rather start over after getting the required data.
For right now, this code goes in a continuous loop that requires the
user to enter a number in this box before it will allow the user to do
ANYTHING. Even the Cancel button (see below) will not work until a
number is entered in TextBox6. The only way to get out of the loop is
to enter any number in the box, THEN the Cancel button will work.
‘HOWEVER, if I take out the line Cancel=True, or change it to False,
then the message comes up when TextBox6 is left blank, but, ‘after
hitting OK for the MsgBox reminder, the cursor goes to the next box as
if this whole restriction never applied.
‘In short, I need the requirement that the user enter a number in
TextBox6, but the user must be able to click the Cancel button, too.


Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
‘Keeps user from leaving TextBox6 blank.
With Worksheets("Official list")
If TextBox6.Text = "" Then
MsgBox "This box must have a numeric value. .“
TextBox6.Text = Clear
Cancel = True
End If
End With
End Sub


This is the Cancel button referenced above.
Private Sub CommandButton2_Click()
'CANCEL button.
Unload UserForm4
Worksheets("Menu").Activate
End Sub


NOTE: I would rather handle all this with code specific to
instructions for TextBox6
I’m going to set up similar restrictions (but not the same) for other
textboxes in this userform.
So, I would rather the coding for the OK and Cancel buttons be left
out of all this, if that makes any sense.

I hope this is clear enough for someone to help me. I appreciate it.
Thanks
j.o.

 

[Jim Rech]

Try something like this:

Dim CancelMode as Boolean

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not CancelMode then
With Worksheets("Official list")
If TextBox6.Text = "" Then
MsgBox "This box must have a numeric value. .“
TextBox6.Text = Clear
Cancel = True
End If
End With
End If
End Sub

Private Sub CommandButton2_Click()
CancelMode = True
Unload UserForm4
End Sub



1st off, I can't believe all this can't be handled within the TextBox
Properties selections. I've looked, but can't see anything on this.

This is something I’ve had help on, but I’m trying to tweak it to get
just what I want.
What I’m trying to do is to require the user to enter a number in
TextBox6, and the user cannot leave it blank.
Here’s what I have so far to help in this:

‘This takes care to not allow an alpha to be entered in TextBox6. This
works fine.
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 9, 27, 45, 46, 48 To 57
Case Else
Beep
KeyAscii = 0
End Select
End Sub

This part below is what I’m having a problem with. This makes sure
TextBox6 is not left blank. It works fine, except that I would like to
let the user Cancel the whole UserForm routine if he wants. Maybe he
doesn’t have a number to enter for TextBox6 at the time, and would
rather start over after getting the required data.
For right now, this code goes in a continuous loop that requires the
user to enter a number in this box before it will allow the user to do
ANYTHING. Even the Cancel button (see below) will not work until a
number is entered in TextBox6. The only way to get out of the loop is
to enter any number in the box, THEN the Cancel button will work.
‘HOWEVER, if I take out the line Cancel=True, or change it to False,
then the message comes up when TextBox6 is left blank, but, ‘after
hitting OK for the MsgBox reminder, the cursor goes to the next box as
if this whole restriction never applied.
‘In short, I need the requirement that the user enter a number in
TextBox6, but the user must be able to click the Cancel button, too.


Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
‘Keeps user from leaving TextBox6 blank.
With Worksheets("Official list")
If TextBox6.Text = "" Then
MsgBox "This box must have a numeric value. .“
TextBox6.Text = Clear
Cancel = True
End If
End With
End Sub


This is the Cancel button referenced above.
Private Sub CommandButton2_Click()
'CANCEL button.
Unload UserForm4
Worksheets("Menu").Activate
End Sub


NOTE: I would rather handle all this with code specific to
instructions for TextBox6
I’m going to set up similar restrictions (but not the same) for other
textboxes in this userform.
So, I would rather the coding for the OK and Cancel buttons be left
out of all this, if that makes any sense.

I hope this is clear enough for someone to help me. I appreciate it.
Thanks
j.o.

 

[jeff]

Try something like this:

Dim CancelMode as Boolean

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 If Not CancelMode then
   With Worksheets("Official list")
    If TextBox6.Text = "" Then
      MsgBox "This box must have a numeric value. .“
      TextBox6.Text = Clear
      Cancel = True
    End If
  End With
 End If
End Sub

Private Sub CommandButton2_Click()
  CancelMode = True
  Unload UserForm4
End Sub


1st off, I can't believe all this can't be handled within the TextBox
Properties selections. I've looked, but can't see anything on this.

This is something I’ve had help on, but I’m trying to tweak it to get
just what I want.
What I’m trying to do is to require the user to enter a number in
TextBox6, and the user cannot leave it blank.
Here’s what I have so far to help in this:

‘This takes care to not allow an alpha to be entered in TextBox6. This
works fine.
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 Select Case KeyAscii
  Case 8, 9, 27, 45, 46, 48 To 57
    Case Else
        Beep
        KeyAscii = 0
End Select
End Sub

This part below is what I’m having a problem with. This makes sure
TextBox6 is not left blank. It works fine, except that I would like to
let the user Cancel the whole UserForm routine if he wants. Maybe he
doesn’t have a number to enter for TextBox6 at the time, and would
rather start over after getting the required data.
For right now, this code goes in a continuous loop that requires the
user to enter a number in this box before it will allow the user to do
ANYTHING. Even the Cancel button (see below) will not work until a
number is entered in TextBox6. The only way to get out of the loop is
to enter any number in the box, THEN the Cancel button will work.
‘HOWEVER, if I take out the line   Cancel=True, or change it to False,
then the message comes up when TextBox6 is left blank, but, ‘after
hitting OK for the MsgBox reminder, the cursor goes to the next box as
if this whole restriction never applied.
‘In short, I need the requirement that the  user enter a number in
TextBox6, but the user must be able to click the Cancel button, too.

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
‘Keeps user from leaving TextBox6 blank.
With Worksheets("Official list")
 If TextBox6.Text = "" Then
    MsgBox "This box must have a numeric value. .“
    TextBox6.Text = Clear
    Cancel = True
End If
End With
End Sub

This is the Cancel button referenced above.
Private Sub CommandButton2_Click()
'CANCEL button.
Unload UserForm4
Worksheets("Menu").Activate
End Sub

NOTE: I would rather handle all this with code specific to
instructions for TextBox6
I’m going to set up similar restrictions (but not the same) for other
textboxes in this userform.
So, I would rather the coding for the  OK and Cancel buttons be left
out of all this, if that makes any sense.

I hope this is clear enough for someone to help me. I appreciate it.
Thanks
j.o.

I appreciate your suggestion. However, it reacted the same.
I put that line.....
Dim CancelMode As Boolean
as the 1st line in the sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Then I tested it.

Then I moved it above to the Declarations. In each case, there was no
difference.....I had to put a number in the TextBox6 before I could
Cancel the userform.

If you, or someone else has a different idea, I would be glad to try
it out.
Thanks again,
j.o.

 

[Dave Peterson]

I would remove all the msgboxes and replace them with a label. Then even if the
label changes, the user won't be able to see that change if they hit the cancel
button.

You can also change the cancel commandbutton's .takefocusonclick property to false.

I'd do it in the userform_initialize routine.

with me.commandbutton2
.caption = "Cancel"
.takefocusonclick = false
end with

That should fix the problem if the user hits the cancel button -- but the msgbox
will still appear if they hit the X in the top right corner. I'd use a label, too.

 

[jeff]

I would remove all the msgboxes and replace them with a label.  Then even if the
label changes, the user won't be able to see that change if they hit the cancel
button.

You can also change the cancel commandbutton's .takefocusonclick propertyto false.

I'd do it in the userform_initialize routine.

with me.commandbutton2
   .caption = "Cancel"
   .takefocusonclick = false
end with

That should fix the problem if the user hits the cancel button -- but themsgbox
will still appear if they hit the X in the top right corner.  I'd use alabel, too.

Thanks Dave. I'm sure your way is the most efficient. However, I'm not
sure what a Label is that you're suggesting. I've never done them. I
looked it up, and I still don't get what they are/do. I tried to put
one in, and all I got was some of the characters on the userform
changed, like there was something behind the textboxes.
I'll keep looking at how the labels work. Meanwhile, I'll have to
settle on the work-arounds.
Thanks for your help.
j.o.

 

[Dave Peterson]

A label is another control on the userform toolbar.

It's like a textbox that the user can't change.

if someerrorcondition = true then
me.label1.caption = "Error message here"
else
me.label1.caption = ""
end if




On 09/21/2010 13:50, jeff wrote:

 

[jeff]

A label is another control on the userform toolbar.

It's like a textbox that the user can't change.

if someerrorcondition = true then
    me.label1.caption = "Error message here"
else
    me.label1.caption = ""
end if

On 09/21/2010 13:50, jeff wrote:
<<snipped>>

Ok. I know about the label one might put above the textbox. I use
those to label the textboxes. I didn't know this was what you were
refering to. The concept of creating one based on conditional
statements.... this is what I'm not familiar with. I think I'll get my
John Walkenbach book out, and learn about this before I move ahead.
Thanks for your time and patience.
j.o.