Using the MsgBox

inactiveUser214710 · Apr 24, 2018

hi

i have in excel vba an userform with 4 textboxes to filled up.

if i write a wrong data, appear a message to fill the the correct data.

how to do a vba code, for the correct data stay, and didn't disappear after clicked ok, in the observation message (vbOKOnly) and write the correct data?

i ask for your help. thank you

Yongle · Apr 24, 2018

You have not told us very much

Try this code in the UserForm module -adapt to your needs

example
TextBox1 must contain a valid DATE,
When user exits TextBox1 VBA validates TextBox1 (does it contains a valid date?)

Code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If Not IsDate(.Text) Then
            MsgBox "Please enter a valid date", , "Validation error"
            Cancel = True
            .Text = ""      'clears bad value
            .SetFocus
        End If
    End With
End Sub

To update values in worksheet

Code:

Private Sub CommandButton1_Click()
Range("A1").Value = Me.TextBox1.Text
...
End Sub

inactiveUser214710 · Apr 25, 2018

Hi Yongle
Thank your for quicly answer. indeed, I did not clear on my question, but your information was very useful for other situation. thank you.
But, the situation is this:
Actually I have a userform for domestic expens that have between others textboxes and comboboxes to be filled up and a commandbox to regist in worksheet table.
The situation is, if i click in commandbox to regist data, and eventually i forgot to fill up just one textbox, it gives me the msgbox (ex:Please finish and complit)
The goal is, if I click on commandbox to register the data, and if eventually I had forgotten to fill in some text box, the system would give me the msgbox (ex: Please finish the padding) and after clicking ok, I do not lose the data that was previously made in the respective boxes, and only let me complete what is missing.
Thanks again

Yongle · Apr 25, 2018

There are several ways to approach this

My preferred way:
- test every box when the user exits the box -as in my example above
- if data is valid then SetFocus on the next box (see code below) - user cannot "forget" a box
- if the data is not valid the user is not permitted to leave the box - as in example code above

Another way - test validity of data using command button VBA
- check the value of each box in turn
- if value is not acceptable then message box and set focus to "bad" box and Exit Sub
- EXIT the sub BEFORE values are updated and cleared
- see example code below - TextBox1 must have a valid date, TextBox2 cannot be empty
- adapt to your needs

Code:

Private Sub CommandButton1_Click()
        
If Not IsDate(TextBox1.Text) Then
    [COLOR=#ff0000]MsgBox[/COLOR] "Date missing TB1"
    TextBox1.[COLOR=#ff0000]SetFocus[/COLOR]
    [COLOR=#ff0000]Exit Sub[/COLOR]
End If

If TextBox2.Text = "" Then
    [COLOR=#ff0000]MsgBox[/COLOR] "Please finish the padding"
    TextBox2.[COLOR=#ff0000]SetFocus[/COLOR]
    [COLOR=#ff0000]Exit Sub[/COLOR]
End If
      
    'update values in workbook
    'clear values from userform
    'unload form etc

End Sub

inactiveUser214710 · Apr 25, 2018

hi
sorry but it did not work or i did not properly the changes.
If i forget padding, for exemple txtV1, it'll give me the msg to complet, but the others regists must not disappear.
However i send a small example:

Private Sub Cmdsubmit_Click()

Dim rng As Range
Dim lrPD As Long

'If cboB1.Value = True And txtV1.Value = True And txtObs.Value = True Then '' not resolve

Set rng = Worksheets("sheet1").Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

rng.Offset(0, 0) = cboB1.Value
rng.Offset(0, 1) = txtV1.Value
rng.Offset(0, 2) = txtObs.Value
'End If

cboB1.Value = Clear
txtV1.Value = Clear
txtObs.Value = Clear

ActiveWorkbook.save

If Not IsDate(txtV1.Value) Then
MsgBox "Date missing TB1"
txtV1.SetFocus
Exit Sub
End If

If cboB1.Value = "" Then
MsgBox "Please finish the padding"
cboB1.SetFocus
Exit Sub
End If

End Sub
Private Sub cmdout_Click()

Unload Me

End Sub
Private Sub txtV1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Only Numbers in textbox 0r simples signs
Select Case KeyAscii
Case Asc("0") To Asc("999")
Case Asc(".")
If InStr(1, txtV1.Value, ".") > 0 Then
KeyAscii = 0
End If

Case Asc(",")
If InStr(1, txtV1.Value, ",") > 0 Then
KeyAscii = 0
End If

Case Else
KeyAscii = MsgBox("SORRY! Introduce just Price numbers")

End Select
End Sub

Private Sub UserForm_Initialize()

cboB1.List = Array("AAAA", "BBBB")

End Sub

'''modulo
'Sub sumit()
'Load UserForm1
'UserForm1.Show
'End Sub

Yongle · Apr 25, 2018

Correct Order
1. FIRST Test values in textboxes\comboboxes
2. NEXT transfer values to worksheet
3, NEXT Clear the values from textboxes\comboboxes

Your code 2 - 3 - 1
Rewrite your code in the correct order in Private Sub Cmdsubmit_Click

To clear values try:

Code:

cboB1.ListIndex= -1
txtV1.Text = ""
txtObs.Text = ""

Do you have a date in textbox txtV1 ?

Using the MsgBox

inactiveUser214710

Board Regular

Excel Facts

Yongle

Well-known Member

inactiveUser214710

Board Regular

Yongle

Well-known Member

inactiveUser214710

Board Regular

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Using the MsgBox

inactiveUser214710

Board Regular

Excel Facts

Yongle

Well-known Member

inactiveUser214710

Board Regular

Yongle

Well-known Member

inactiveUser214710

Board Regular

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock