Macro to create dialog box for user instruction

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
I have a workbook with 23 pages, each running a macro to calculate the final "answer".
I would like to make the title page comman button create a dialog box, listing the 23 pages/macros with a check-box for each (default is "checked") asking the user which macros / calculations to execute. Any ideas how to do this?
I am trying to avoid making this a sheet in my workbook.
Thanks in advance.
Karl
 

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.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
An InPutBox or MsgBox cannot have CheckBoxs in them. So, you will need to build a UserForm that is called or opened on the sheet to do what you want!
 

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
Thanks Joe.
I've never built a UserForm before. Is there another way to accomplish this, or is the UserForm the best, most efficient, and relatively simplest way to go about?
Thanks for the quick response.
Karl
 

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
THANK YOU!
This board has saved me the last couple of weeks!
Karl
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
A nother option is checkboxs in the Assistant, but I think 23 checkboxs is more than the assistant can handle.

Don't let this code scare you most of it is to build a new application in a blank workbook for you. It writes itself.

It will show you how to use the assistant to make choices.

Copy all the code below to a Standard module, like: Module1 [Alt+F11, Toolbar: Insert - Module] and run "Builder" you only run this once! Then press the button on the sheet that it builds.

Note if you get an error message that the Sub cannot be found,
Right-Click the Button on the sheet and Select: Asign Macro and select "Utility" it should work then!


Sub Builder()
'Note: Only run this on a blank workbook.
'The blank workbook should have three blank sheets,
'named "Sheet1, Sheet2 and Sheet3."
'Only run "Builder" once, to create the application.


Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Utility Log"
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 22
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "To use, press the button below!"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Note: Row 3 on the (Data)Sheet must have valid start values!"
Range("D1").Select

ActiveSheet.Buttons.Add(81, 60.75, 175.5, 33.75).Select
Selection.OnAction = "Sheet1.Utilities"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 9
End With

Selection.Characters.Text = "Record Utility Readings"
Worksheets("Sheet1").Select
Range("D1").Select

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete

Worksheets("Sheet1").Select
Sheets("Sheet1").Name = "Menu"
Range("A1:G1").Select
Worksheets("Sheet2").Select
Sheets("Sheet2").Name = "Data"
Application.ScreenUpdating = True
Application.DisplayAlerts = True

With Worksheets("Data")
.Range("B1").Value = "1st Utility"
.Range("B2").Value = "Reading"
.Range("B1:B2").Font.Bold = True
.Range("B1:B2").HorizontalAlignment = xlCenter
.Range("B3").Value = "999999"
.Range("C1").Value = "Usage"
.Range("C2").Value = "This"
.Range("C3").Value = "Period"
.Range("C1:C3").Font.Bold = True
.Range("C1:C3").HorizontalAlignment = xlCenter
.Range("E1").Value = "2nd Utility"
.Range("E2").Value = "Reading"
.Range("E1:E2").Font.Bold = True
.Range("E1:E2").HorizontalAlignment = xlCenter
.Range("E3").Value = "999999"
.Range("F1").Value = "Usage"
.Range("F2").Value = "This"
.Range("F3").Value = "Period"
.Range("F1:F3").Font.Bold = True
.Range("F1:F3").HorizontalAlignment = xlCenter
.Range("H1").Value = "3rd Utility"
.Range("H2").Value = "Reading"
.Range("H1:H2").Font.Bold = True
.Range("H1:H2").HorizontalAlignment = xlCenter
.Range("H3").Value = "999999"
.Range("I1").Value = "Usage"
.Range("I2").Value = "This"
.Range("I3").Value = "Period"
.Range("I1:I3").Font.Bold = True
.Range("I1:I3").HorizontalAlignment = xlCenter
.Range("B3").Font.ColorIndex = 11
.Range("E3").Font.ColorIndex = 11
.Range("H3").Font.ColorIndex = 11
.Range("A1").Select
End With

Sheets("Menu").Select
Range("D1").Select
End Sub

Sub Utilities()
Dim clickTest As Variant
Dim my1st As Variant
Dim my2nd As Variant
Dim my3rd As Variant
Dim my1stD As Variant
Dim my2ndD As Variant
Dim my3rdD As Variant
Dim my1stO As Variant
Dim my2ndO As Variant
Dim my3rdO As Variant

'Open checkbox menu.
With Assistant.NewBalloon
.Heading = "Start utility reading;"
.Text = "Click the box which is your choice:"

'Build three check boxes.
For i = 1 To 3
.CheckBoxes(1).Text = ("1st Utility.")
.CheckBoxes(2).Text = ("2nd Utility.")
.CheckBoxes(3).Text = ("3rd Utility.")
Next
.Button = msoButtonSetOkCancel
.Show
clickTest = 0
On Error GoTo myEnd

'Test for which box is checked.
If .CheckBoxes(1).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate

'Get new reading.
my1st = Application.InputBox(prompt:="What is the new reading for the 1st Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

'Test for errors.
If my1st = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop

'Do data updating and math.
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("B65536").End(xlUp).Offset(1, 0).Value = my1st
my1stO = Worksheets("Data").Range("B65536").End(xlUp).Offset(-1, 0).Value
my1stD = my1st - my1stO
Worksheets("Data").Range("C65536").End(xlUp).Offset(1, 0).Value = my1stD
Application.ScreenUpdating = True
End If

'Display results.
If my1st <> 0 And my1stO <> 0 Then
MsgBox prompt:="The current reading is: " & my1st & Chr(13) & _
"The last reading was: " & my1stO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my1stD, _
Title:="Current Usage!"
End If

'The next two check boxes are designed as above!
If .CheckBoxes(2).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my2nd = Application.InputBox(prompt:="What is the new reading for the 2nd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

If my2nd = 0 Then GoTo myStop

If Cancel = True Then GoTo myStop

Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("E65536").End(xlUp).Offset(1, 0).Value = my2nd
my2ndO = Worksheets("Data").Range("E65536").End(xlUp).Offset(-1, 0).Value
my2ndD = my2nd - my2ndO
Worksheets("Data").Range("F65536").End(xlUp).Offset(1, 0).Value = my2ndD
Application.ScreenUpdating = True
End If

If my2nd <> 0 And my2ndO <> 0 Then
MsgBox prompt:="The current reading is: " & my2nd & Chr(13) & _
"The last reading was: " & my2ndO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my2ndD, _
Title:="Current Usage!"
End If

If .CheckBoxes(3).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my3rd = Application.InputBox(prompt:="What is the new reading for the 3rd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

If my3rd = 0 Then GoTo myStop

If Cancel = True Then GoTo myStop

Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("H65536").End(xlUp).Offset(1, 0).Value = my3rd
my3rdO = Worksheets("Data").Range("H65536").End(xlUp).Offset(-1, 0).Value
my3rdD = my3rd - my3rdO
Worksheets("Data").Range("I65536").End(xlUp).Offset(1, 0).Value = my3rdD
Application.ScreenUpdating = True
End If

If my3rd <> 0 And my3rdO <> 0 Then
MsgBox prompt:="The current reading is: " & my3rd & Chr(13) & _
"The last reading was: " & my3rdO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my3rdD, _
Title:="Current Usage!"
End If

'Test for errors and other events.
If .CheckBoxes(i).Checked = False And clickTest <> 1 Then GoTo Emp
End With
End

myStop:
MsgBox prompt:="Operator ended update, no action taken!", Title:="UpDate Stopped!"
End

Emp:
MsgBox prompt:="You did not check an Utility box.", Title:="Input Data Error!"
End

myEnd:
End Sub
 

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
One more question

So I have my userform with all 26 (!) checkboxes. Now, some of the checkboxes (and corresponding macros) need not be run if there are certain values on the main title page (i.e., if one cell reads software, I will not need to run the macro "Supply Chain", which is also a text box.
To make this tool as succinct and accurate as possible, I'd like to "black out" or "shadow" the "Supply Chain" option if the title page reads "software".
I know the coading will be simple, but I cannot find the keyword/action.
One more piece of help on this?
Karl
 

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
One more question

So I have my userform with all 26 (!) checkboxes. Now, some of the checkboxes (and corresponding macros) need not be run if there are certain values on the main title page (i.e., if one cell reads software, I will not need to run the macro "Supply Chain", which is also a text box).
To make this tool as succinct and accurate as possible, I'd like to "black out" or "shadow" the "Supply Chain" option if the title page reads "software".
I know the coading will be simple, but I cannot find the keyword/action.
One more piece of help on this?
Karl
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You do it like this:


Private Sub CheckBox1_Click()
'UserForm code module code only!

CheckBox2.Enabled = False
End Sub

Private Sub UserForm_Initialize()
'UserForm code module code only!

CheckBox1.Value = False
CheckBox2.Value = False
End Sub


This opens the form with 2 empty CheckBoxs if the user selects #1 then #2 becomes grayed-out and cannot be used. You can condition this with cell values or other things as the trigger.
 

karl_burns

New Member
Joined
Jul 10, 2007
Messages
19
Follow-up

Joe,
I'm a beginning VBA-er, so this is a bit confusing.
I am trying to execute a macro on my title page, which brings up my UserForm to review the CheckBox options, and Disables the corresponding CheckBoxes based on certain text values on mt title page.
Can I (and where would I put) the code to perform this. Is that what you are trying to help me with?
Karl
 

Forum statistics

Threads
1,181,659
Messages
5,931,273
Members
436,786
Latest member
Deniel

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