UserForm MessageBox Options?

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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.)

Code:
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:

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

For Clear Button:
Code:
Private Sub CommandButton3_Click()
'Clear UserForm
Unload UserForm1
UserForm1.Show

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
For the message box question you need to return the users response.

Something like this.
Code:
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?
Code:
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

Active Member
Joined
Jan 31, 2006
Messages
438
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:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Using Selection could be the problem.

What is actually selected when that code is executed?
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Did you mean you tried this?
Code:
Sheets("Similar Incident Query Template").Range("C15").QueryTable.Refresh BackgroundQuery:=True
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Why do you have this?
Code:
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

Active Member
Joined
Jan 31, 2006
Messages
438
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,657
Messages
5,931,261
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top