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

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

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

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

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

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

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