UserForm MessageBox Options?

[aka_krakur]

I have the following VB macro that runs after user presses OK on a userform. I don't think it's written correctly. I would like a prompt that gives two options (either yes to return a null value if they've left a field blank, or return user to a reset userform1 if they select no.)

Private Sub CommandButton1_Click()

If Me.TextBox1.Value = "" Then
MsgBox ("You Must Enter a Part Number (eg. 1007821-12)")
Unload UserForm1
Calculate
UserForm1.Show
End If
If Me.TextBox2.Value = "" Then
MsgBox ("You Must Enter a Lot Number (eg. 6020631)")
Unload UserForm1
Calculate
UserForm1.Show
End If

If Me.TextBox3.Value = "" Then
MsgBox ("You Must Enter a Current Incident # (eg. 93885)")
Unload UserForm1
Calculate
UserForm1.Show
End If

'Unload UserForm
Sheets("Similar Incident Query Template").Select
Unload UserForm1
' Refresh data
 Application.ScreenUpdating = False
' Add UserForm data to proper fields
Sheets("Similar Incident Query Template").Range("B9").Value = Me.TextBox1.Value
Calculate
Sheets("Similar Incident Query Template").Range("B10").Value = Me.TextBox2.Value
Calculate
Sheets("Similar Incident Query Template").Range("B11").Value = Me.TextBox3.Value
Calculate
Sheets("Similar Incident Query Template").Range("C15").Select
Calculate
Selection.QueryTable.Refresh BackgroundQuery:=True
Range("H15").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],""Duplicate"",""Unique"")"
Calculate
Range("G15").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],-RC[3],-RC[4])"
Calculate
last1 = Range("C65536").End(xlUp).Row
    Range("H15").Select
    Selection.AutoFill Destination:=Range("H15:H" & last1)
    Range("G15").Select
    Selection.AutoFill Destination:=Range("G15:G" & last1)
Calculate
End Sub

1. For some reason I don't think I have the code the way I truly want it on the "MsgBox"... and regardless,
2. I am actually wanting to either create another userform or something similar to a msgbox that prompts the user "Do you really want to leave this field blank"...if they choose Yes, then it returns a true null value; but if they choose No, then it brings them back to the "reset" form.
3. Other problem I have with the way the code is currently, is if they did leave a field blank and it brings them back to the userform, if I select Cancel, it still pops up with one of those Msgboxes.

the code for the Cancel button is quite simple:

Private Sub CommandButton4_Click()
'Cancel using form
Sheets("Similar Incident Query Template").Select
Unload UserForm1
End Sub

For Clear Button:

Private Sub CommandButton3_Click()
'Clear UserForm
Unload UserForm1
UserForm1.Show

End Sub

 

[Norie]

For the message box question you need to return the users response.

Something like this.

rsp = MsgBox ("Do you really want to leave Part Number blank?, vbYesNo)

Select Case rsp
       Case vbYes
            ' code if user wants to leave blank
       Case vbNo
            ' code to return user to form
End Select

By the way why are you unloading the form then doing this sort of thing?

Sheets("Similar Incident Query Template").Range("B9").Value = Me.TextBox1.Value

I'm not 100% sure but I would have thought that you wouldn't be able to access the controls on the form once it's unloaded.

 

[aka_krakur]

I am also having an issue with a line in the macro (even if everything else goes okay.
I have another command button on the Excel Spreadsheet that will allow the user to start a new "query" which if the click this command button, it just brings up this UserForm1 again.
However, no matter what scenario, I haven't been successful as, when Ok is clicked, it gives me a debug error.
When I click debug, it takes me to this line:

Selection.QueryTable.Refresh BackgroundQuery:=True

Which I know has to do with the query itself; but when I go step by step using the Debug Step Into, and <F8> all the way through, I'm successful;
I am just unsuccessful when I use the UserForm1 more than once.

Any ideas what I might be doing wrong?

 

[Norie]

Using Selection could be the problem.

What is actually selected when that code is executed?

 

[aka_krakur]

I've tried that various ways...currently in the code above in my 1st post it is selecting C15 which it doesn't have a problem the 1st go around...it's anytime I have to run the query again.

Also, do you know of any other way to reset a userform other than unload and show?

 

[Norie]

Did you mean you tried this?

Sheets("Similar Incident Query Template").Range("C15").QueryTable.Refresh BackgroundQuery:=True

 

[aka_krakur]

I'm getting closer...It seems to me that it has to do with my If Statements...
Because anytime It prompts for the "Are you Sure", then if they select No, then it brings them back to the UserForm1; however, if they input something the second time around is where it has its problem.
Here's what the code looks like revised with the rsp help you gave me earlier:

Private Sub CommandButton1_Click()

If Me.TextBox1.Value = "" Then
rsp = MsgBox("Do you really want to leave the Part Number blank?", vbYesNo)

Select Case rsp
       Case vbYes
            ' code if user wants to leave blank
            Range("B9").Value = Me.TextBox1.Value
       Case vbNo
            ' code to return user to form
            Unload UserForm1
            UserForm1.Show
End Select
Else
If Me.TextBox2.Value = "" Then
rsp2 = MsgBox("Do you really want to leave the Lot Number blank?", vbYesNo)

Select Case rsp2
       Case vbYes
            ' code if user wants to leave blank
            Range("B10").Value = Me.TextBox2.Value
       Case vbNo
            ' code to return user to form
             Unload UserForm1
            UserForm1.Show
End Select
Else
If Me.TextBox3.Value = "" Then
rsp3 = MsgBox("Do you really want to leave Current Incident # blank?", vbYesNo)

Select Case rsp3
       Case vbYes
            ' code if user wants to leave blank
            Range("B11").Value = Me.TextBox3.Value
       Case vbNo
            ' code to return user to form
             Unload UserForm1
            UserForm1.Show
End Select
End If
    End If
        End If

Not sure if this is right.

 

[Norie]

Why do you have this?

Select Case rsp 
       Case vbYes 
            ' code if user wants to leave blank 
            Range("B9").Value = Me.TextBox1.Value 
       Case vbNo 
            ' code to return user to form 
            Unload UserForm1 
            UserForm1.Show 
End Select

Note the comments, I think you've got your logic the wrong way round.

Also why are you unloading and showing the form?

By doing so you'll probably lose any data the user has entered.

 

[aka_krakur]

The unloading and showing of the form is the only way I know to bring the user back to the form.
I guess this is where I am having my problem.

Also, I'm really unsure as to what code to put in...so here's the scenario:
1. If User DOES want to leave it blank, then I need the value of "" to be returned into Cell B9
2. If User does NOT want to leave it blank (in other words, accidentally left it blank), then I need it to bring the user back to the userform1 so they can input what they left blank...that's where I have no idea.

 

[Norie]

There is no reason to do this unloading etc.

If the userform is showing and the user clicks the command button and a message box appears then the userform should still be showing when they chose an option from the message box.