I have four ActiveX Check Box controls and four ActiveX Spinner controls on Sheet1 of my workbook.
Currently, I have a nested IF Then Else checking the value of each checkbox and spinner control.
I need to add the ability to handle up to all four checkboxes and then set my msgBox properties depending on which checkboxes have been selected.
Additionally, I would like the ability to update the spinner controls while every possible combination of check box is selected.
Here is the code for the checkboxes:
Option Explicit
Public Sub TransformData(PolySaturday%, CaSaturday%, DhSaturday%, DoorsSaturday As Integer)
Dim Wk1Date As Date
Dim Wk2Date As Date
Dim Wk3Date As Date
Dim Wk4Date As Date
Wk1Date = Sheet1.Range("A2").Value
Wk2Date = Sheet1.Range("A15").Value
Wk3Date = Sheet1.Range("A26").Value
Wk4Date = Sheet1.Range("A37").Value
' Code to update Saturday capacity
With ActiveSheet
If ActiveSheet.CheckBox1.Value Then
MsgBox "Saturday Capacity for " & Wk1Date & " Showing"
Sheet1.Range("C4").Value = PolySaturday
Sheet1.Range("D4").Value = DhSaturday
Sheet1.Range("F4").Value = CaSaturday
Sheet1.Range("E4").Value = DoorsSaturday
Sheet1.Range("C16").Value = PolySaturday
Sheet1.Range("D16").Value = DhSaturday
Sheet1.Range("F16").Value = CaSaturday
Sheet1.Range("E16").Value = DoorsSaturday
ElseIf ActiveSheet.CheckBox2.Value = True Then
MsgBox "Saturday Capacity for " & Wk2Date & " Showing"
Sheet1.Range("C16").Value = PolySaturday
Sheet1.Range("D16").Value = DhSaturday
Sheet1.Range("F16").Value = CaSaturday
Sheet1.Range("E16").Value = DoorsSaturday
ElseIf ActiveSheet.CheckBox3.Value = True Then
MsgBox "Saturday Capacity for " & Wk3Date & " Showing"
Sheet1.Range("C27").Value = PolySaturday
Sheet1.Range("D27").Value = DhSaturday
Sheet1.Range("F27").Value = CaSaturday
Sheet1.Range("E27").Value = DoorsSaturday
ElseIf ActiveSheet.CheckBox4.Value = True Then
MsgBox "Saturday Capacity for " & Wk4Date & " Showing"
Sheet1.Range("C38").Value = PolySaturday
Sheet1.Range("D38").Value = DhSaturday
Sheet1.Range("F38").Value = CaSaturday
Sheet1.Range("E38").Value = DoorsSaturday
Else
MsgBox "Regular Work Week Showing"
Sheet1.Range("C4").Value = Sheet4.Range("C2").Value
Sheet1.Range("D4").Value = Sheet4.Range("C4").Value
Sheet1.Range("F4").Value = Sheet4.Range("C3").Value
Sheet1.Range("E4").Value = Sheet4.Range("C5").Value
Sheet1.Range("C16").Value = Sheet4.Range("C2").Value
Sheet1.Range("D16").Value = Sheet4.Range("C4").Value
Sheet1.Range("F16").Value = Sheet4.Range("C3").Value
Sheet1.Range("E16").Value = Sheet4.Range("C5").Value
Sheet1.Range("C27").Value = Sheet4.Range("C2").Value
Sheet1.Range("D27").Value = Sheet4.Range("C4").Value
Sheet1.Range("F27").Value = Sheet4.Range("C3").Value
Sheet1.Range("E27").Value = Sheet4.Range("C5").Value
Sheet1.Range("C38").Value = Sheet4.Range("C2").Value
Sheet1.Range("D38").Value = Sheet4.Range("C4").Value
Sheet1.Range("F38").Value = Sheet4.Range("C3").Value
Sheet1.Range("E38").Value = Sheet4.Range("C5").Value
End If
End With
End Sub
And the code for the Spinners:
Public Sub UseHolidayData()
With ActiveSheet
If Sheet1.SpinButton1.Enabled And Sheet1.CheckBox1.Value = True Then
Sheet1.Range("C4").Value = Sheet4.Range("F2").Value
Sheet1.Range("D4").Value = Sheet4.Range("F4").Value
Sheet1.Range("E4").Value = Sheet4.Range("F5").Value
Sheet1.Range("F4").Value = Sheet4.Range("F3").Value
ElseIf Sheet1.SpinButton2.Enabled And Sheet1.CheckBox2.Value = True Then
Sheet1.Range("C16").Value = Sheet4.Range("F8").Value
Sheet1.Range("D16").Value = Sheet4.Range("F10").Value
Sheet1.Range("E16").Value = Sheet4.Range("F11").Value
Sheet1.Range("F16").Value = Sheet4.Range("F9").Value
ElseIf Sheet1.SpinButton3.Enabled And Sheet1.CheckBox3.Value = True Then
Sheet1.Range("C27").Value = Sheet4.Range("F14").Value
Sheet1.Range("D27").Value = Sheet4.Range("F16").Value
Sheet1.Range("E27").Value = Sheet4.Range("F17").Value
Sheet1.Range("F27").Value = Sheet4.Range("F15").Value
ElseIf Sheet1.SpinButton4.Enabled And Sheet1.CheckBox4.Value = True Then
Sheet1.Range("C38").Value = Sheet4.Range("F20").Value
Sheet1.Range("D38").Value = Sheet4.Range("F22").Value
Sheet1.Range("E38").Value = Sheet4.Range("F23").Value
Sheet1.Range("F38").Value = Sheet4.Range("F21").Value
Else
Sheet1.Range("C4").Value = Sheet4.Range("E2").Value
Sheet1.Range("D4").Value = Sheet4.Range("E4").Value
Sheet1.Range("E4").Value = Sheet4.Range("E5").Value
Sheet1.Range("F4").Value = Sheet4.Range("E3").Value
Sheet1.Range("C16").Value = Sheet4.Range("E8").Value
Sheet1.Range("D16").Value = Sheet4.Range("E10").Value
Sheet1.Range("E16").Value = Sheet4.Range("E11").Value
Sheet1.Range("F16").Value = Sheet4.Range("E9").Value
Sheet1.Range("C27").Value = Sheet4.Range("E14").Value
Sheet1.Range("D27").Value = Sheet4.Range("E16").Value
Sheet1.Range("E27").Value = Sheet4.Range("E17").Value
Sheet1.Range("F27").Value = Sheet4.Range("E15").Value
Sheet1.Range("C38").Value = Sheet4.Range("E20").Value
Sheet1.Range("D38").Value = Sheet4.Range("E22").Value
Sheet1.Range("E38").Value = Sheet4.Range("E23").Value
Sheet1.Range("F38").Value = Sheet4.Range("E21").Value
End If
End With
End Sub
I feel this could be done using a for each loop.
Not sure if you are trying to deal with combinations of checkbox selections but here is an example similar to a MVC framework. Essentially the code is separated into logical components.
Public Sub TransformData(PolySaturday%, CaSaturday%, DhSaturday%, DoorsSaturday As Integer)
' Code to update Saturday capacity
Dim wkDate(4) As Date, rng(4) As Range, ar0, ar
' data
With Sheet1
wkDate(1) = .Range("A2").Value
wkDate(2) = .Range("A15").Value
wkDate(3) = .Range("A26").Value
wkDate(4) = .Range("A37").Value
Set rng(1) = .Range("C4:F4")
Set rng(2) = .Range("C16:F16")
Set rng(3) = .Range("C27:F27")
Set rng(4) = .Range("C38:F38")
ar0 = Array(.Range("C2"), .Range("C4"), .Range("C5"), .Range("C3"))
End With
ar = Array(PolySaturday, DhSaturday, DoorsSaturday, CaSaturday)
' controls
Dim msg As String, i As Integer, chkBox(4) As Boolean
chkBox(1) = ActiveSheet.CheckBox1.Value
chkBox(2) = ActiveSheet.CheckBox2.Value
chkBox(3) = ActiveSheet.CheckBox3.Value
chkBox(4) = ActiveSheet.CheckBox4.Value
' model
For i = 1 To 4
If chkBox(i) Then
msg = msg & wkDate(i) & ", "
rng(i).Value2 = ar
Else
rng(i).Value2 = ar0
End If
Next
' view
If msg = "" Then
msg = "Regular Work Week Showing"
Else
msg = "Saturday Capacity for " & msg & " Showing"
End If
MsgBox msg
End Sub