Multiple data input message boxes at Workbook_Open problem

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
184
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
  2. MacOS
Hi, we're having an issue with a workbook_open sub. We want to prompt the user for multiple inputs via message boxes, if the cell that will contain their input is blank at workbook_Open. So, we want the message boxes to continue until we've asked the user for each cell's data that we need, before the workbook is even displayed to them. As usual, we've tried to butcher codes together to reach our goal, but my lack of VBA command vocabulary is holding us back.

Upon workbook_Open, we're getting a compile error (Next without for). Please instruct us how to fix the issue.

Here's our attempt at the code:

Code: 
[COLOR=#454545][FONT=&quot]Private Sub workbook_open()[/FONT][/COLOR][COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]If ActiveSheet.Name = "Sheet 1" Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ptname1 = InputBox("What is the patient's first name?", "Renaming Sheets")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ptname2 = InputBox("What is the patient's last name?", "Renaming Sheets")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ActiveSheet.Name = ptname2 & ", " & ptname1[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheet1.Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Next[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]If WorksheetFunction.CountA( _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ActiveSheet.Range("E4")) < 1 Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]apptdt = InputBox("What is the date of the Dr Visit?", "Office Appointment Date")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("E4").Value = appdt[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheet1.Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Next[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]If WorksheetFunction.CountA( _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ActiveSheet.Range("H6")) < 1 Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("H6").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MRN = InputBox("What is the patient's medical record number?", "Medical Record Number")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range(“H6”).Value = MRN[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheet1.Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Next[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]If WorksheetFunction.CountA( _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ActiveSheet.Range("B7")) < 1 Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("B7").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]DOB = InputBox("What is the patient's date of birth?", "Patient Date Of Birth")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("B7").Value = DOB[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheet1.Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Next[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]If WorksheetFunction.CountA( _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]ActiveSheet.Range("E7")) < 1 Then[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("E7").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Ins = InputBox("What is the name of the patient's insurance plan?", "Primary Insurance")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Range("E7").Value = Ins[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Else[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheet1.Range("E4").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]End If[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]End Sub[/FONT][/COLOR]

Thank you
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Try removing all the Next lines I do not see anything in your code that requires them.
 
Upvote 0
No, that gave a block if without end if error.

I did take them out and added ElseIf statements. We opened the workbook and it successfully prompted us for the first and last name of the patient via the first few lines of code:
Code: 
If ActiveSheet.Name = "Sheet 1" Then
ptname1 = InputBox("What is the patient's first name?", "Renaming Sheets")
ptname2 = InputBox("What is the patient's last name?", "Renaming Sheets")
ActiveSheet.Name = ptname2 & ", " & ptname1

After entering that info into the message box, the code stopped and the excel sheet was displayed. Why did it not got to the next statement and display the next message box?

This is the next iteration of the code:

Code: 
Private Sub workbook_open()


    
If ActiveSheet.Name = "Sheet 1" Then
ptname1 = InputBox("What is the patient's first name?", "Renaming Sheets")
ptname2 = InputBox("What is the patient's last name?", "Renaming Sheets")
ActiveSheet.Name = ptname2 & ", " & ptname1
 
 
ElseIf fWorksheetFunction.CountA( _
ActiveSheet.Range("E4")) < 1 Then
Range("E4").Select
apptdt = InputBox("What is the date of the Dr Visit?", "Office Appointment Date")
Range("E4").Value = appdt




ElseIf WorksheetFunction.CountA( _
ActiveSheet.Range("H6")) < 1 Then
Range("H6").Select
MRN = InputBox("What is the patient's medical record number?", "Medical Record Number")
Range("E4").Value = MRN


ElseIf WorksheetFunction.CountA( _
ActiveSheet.Range("B7")) < 1 Then
Range("B7").Select
DOB = InputBox("What is the patient's date of birth?", "Patient Date Of Birth")
Range("B7").Value = DOB


ElseIf WorksheetFunction.CountA( _
ActiveSheet.Range("E7")) < 1 Then
Range("E7").Select
Ins = InputBox("What is the name of the patient's insurance plan?", "Primary Insurance")
Range("E7").Value = Ins


Else
sheet1.Range("E4").Select
MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."


End If
End Sub
 
Upvote 0
Look it at again where you have next should have end if.

Next is use in loops for example
Code: 
for x=2 to 99
code to do things here

next x

This says make the variable x = to 2 then run this code. when it gets to next x it goes back to the for stamen and makes x=3 and repeat until x hits the limit of 99
 
Last edited:
Upvote 0
Yup. Just a simple mistake...as always :)
Works as intended now, prompting user for data each step of the (required) way.

Thank you Scott!!

Code: 
Private Sub workbook_open()


If ActiveSheet.Name = "Sheet 1" Then
ptname1 = InputBox("What is the patient's first name?", "Renaming Sheets")
ptname2 = InputBox("What is the patient's last name?", "Renaming Sheets")
ActiveSheet.Name = ptname2 & ", " & ptname1
End If
 
If WorksheetFunction.CountA( _
ActiveSheet.Range("E4")) < 1 Then
Range("E4").Select
apptdt = InputBox("What is the date of the Dr Visit?", "Office Appointment Date")
Range("E4").Value = apptdt
End If


If WorksheetFunction.CountA( _
ActiveSheet.Range("H6")) < 1 Then
Range("H6").Select
MRN = InputBox("What is the patient's medical record number?", "Medical Record Number")
Range("H6").Value = MRN
End If


If WorksheetFunction.CountA( _
ActiveSheet.Range("B7")) < 1 Then
Range("B7").Select
DOB = InputBox("What is the patient's date of birth?", "Patient Date Of Birth")
Range("B7").Value = DOB
End If


If WorksheetFunction.CountA( _
ActiveSheet.Range("E7")) < 1 Then
Range("E7").Select
Ins = InputBox("What is the name of the patient's insurance plan?", "Primary Insurance")
Range("E7").Value = Ins


Else
sheet1.Range("E4").Select
MsgBox "Please enter all of the required information before clicking the yellow SaveAs button."


End If


End Sub
 
Upvote 0
Scott T said:
Look it at again where you have next should have end if.

Next is use in loops for example
Code: 
for x=2 to 99
code to do things here

next x

This says make the variable x = to 2 then run this code. when it gets to next x it goes back to the for stamen and makes x=3 and repeat until x hits the limit of 99
Click to expand...


I'm still new to VBA and scared of loops :rofl:
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
813
Agnarr
Agnarr
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S
A
  • Question Question
Autofilter code based on username
Replies
1
Views
368
aabrazil1
A
N
  • Solved
Excel Macro prompt box to save to another folder
Replies
3
Views
746
dmt32
D

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

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
Back
Top