| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop (halt, break) my vba code please...
The following code will show a message box if any text boxes on a user form
are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
On Jun 29, 11:52 am, Jock wrote:
The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock If you want the code to "stop" you can simply add an Exit Sub statement where appropriate. Keep in mind that it partially depends on whether you want the information on the form to be in memory or not. Search the VBE Help for Unload and Hide for starters. Matt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
Jock,
Have iseen this code somewhere before!! Ty this:- Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes"): Exit Sub End If End If Next End Sub Mike "Jock" wrote: The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
Hi Mike,
yes, I think it's yours!!! Thanks for the 'tweak', off home now so I'll give it a go on Monday. Many thanks. Cheers. Jock "Mike H" wrote: Jock, Have iseen this code somewhere before!! Ty this:- Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes"): Exit Sub End If End If Next End Sub Mike "Jock" wrote: The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
Jock, when you return on Monday, you will find that using "exit sub" while in
the click event will only take you back to the main procedure. So what you need is a Goto command with a label set up to exit the main procedu 'In the click event Goto FINISH: 'In the main procedure FINISH: Exit Sub You can put the finish label just before the normal Exit Sub statement in the main procedure and it should work just fine. "Jock" wrote: Hi Mike, yes, I think it's yours!!! Thanks for the 'tweak', off home now so I'll give it a go on Monday. Many thanks. Cheers. Jock "Mike H" wrote: Jock, Have iseen this code somewhere before!! Ty this:- Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes"): Exit Sub End If End If Next End Sub Mike "Jock" wrote: The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
Correction:
You can put the finish label just before the normal End Sub statement in the main procedure and it should work just fine. "Jock" wrote: Hi Mike, yes, I think it's yours!!! Thanks for the 'tweak', off home now so I'll give it a go on Monday. Many thanks. Cheers. Jock "Mike H" wrote: Jock, Have iseen this code somewhere before!! Ty this:- Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes"): Exit Sub End If End If Next End Sub Mike "Jock" wrote: The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop (halt, break) my vba code please...
Sorted now. Thanks for all your input guys.
Much appreciated-- Jock "Jock" wrote: Hi Mike, yes, I think it's yours!!! Thanks for the 'tweak', off home now so I'll give it a go on Monday. Many thanks. Cheers. Jock "Mike H" wrote: Jock, Have iseen this code somewhere before!! Ty this:- Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes"): Exit Sub End If End If Next End Sub Mike "Jock" wrote: The following code will show a message box if any text boxes on a user form are empty: Private Sub CommandButton1_Click() Dim TB As Control For Each TB In UserForm1.Controls If TB.Name Like "TextBox*" Then If TB.Value = "" Then MsgBox ("Please fill all boxes") End If End If Next I would like the code to then stop once the "ok" button on the message box is hit. Currently, it continues once the button is clicked. I've lost some hair over this one..... -- tia Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OnTime code error "can't execute code in break mode" | Excel Programming | |||
| Halt all code while macro runs | Excel Programming | |||
| Code Halt - prevents reset of the AutomationSecurity level | Excel Programming | |||
| Code to halt a macro | Excel Programming | |||
Hybrid Mode
