Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
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 "jeff" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
On Sep 20, 11:48*am, "Jim Rech" wrote:
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 "jeff" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
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. On 09/20/2010 09:51, jeff wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
On Sep 20, 2:32*pm, Dave Peterson wrote:
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. On 09/20/2010 09:51, jeff wrote: 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. -- Dave Peterson- Hide quoted text - - Show quoted text - 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code restricting TextBox entry
On Sep 21, 2:04*pm, Dave Peterson wrote:
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 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restricting entry | Excel Worksheet Functions | |||
Restricting Duplicate Entry | Excel Worksheet Functions | |||
Restricting entry in B1 on the basis of entry in A1 | Excel Worksheet Functions | |||
Restricting entry in B1 on the basis of entry in A1 | Excel Worksheet Functions | |||
Text Box - Restricting Entry | Excel Programming |