Tall order a lot of code, this is how I do it. See code notes. JSW
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
