VBA Loop

[raccoon588]

i have the following code, it loops through the check boxes within the user form. Id like to be able to have the message change depending on which check box is true. how could i adjust this loop to have a specific message per check box that is true and then keep going continuing the loop every time hitting a different true check box and giving a different message depending on what comes up true.

Private Sub CmdSubmit_Click()
Dim C As MSForms.Control
    For Each C In Me.Controls '<--| loop through userform controls
        If TypeName(C) = "CheckBox" Then
        If C.Value = True Then
        
            MsgBox C.Name 
            End If
        End If
        
    Next C


End Sub

 

[Fluff]

If your checkboxes are named checkbox1, checkbox2 etc try

Dim C As MSForms.Control
Dim msg As String
   For Each C In Me.Controls '<--| loop through userform controls
      If TypeName(C) = "CheckBox" Then
         If C.Value = True Then
            MsgBox Choose(CLng(Right(C.name, 1)), "box1", "box2", "box3")
         End If
      End If
   Next C

 

[raccoon588]

my check boxes are named ChkFD1-ChkFD7, ChkFH1-ChkFH7, and ChkSH1-ChkSH7. do i need to adjust for this?

 

[Fluff]

What messages do you want displayed?

 

[raccoon588]

for example if the check box is true on ChkFD1 it will show Full Day & lblDayOne if ChkFD2 is true then Full Day & lblDayTwo etc.

 

[Fluff]

How about

Dim C As MSForms.Control
Dim msg As String
   For Each C In Me.Controls '<--| loop through userform controls
      If TypeName(C) = "CheckBox" Then
         If C.Value = True Then
            Select Case Mid(C.name, 4, 2)
               Case "FD"
                  MsgBox "Full Day " & Choose(CLng(Right(C.name, 1)), lblDayOne, lbldaytwo, lbldaythree)
               Case "HD"
                  MsgBox "Half Day " & Choose(CLng(Right(C.name, 1)), lblDayOne, lbldaytwo, lbldaythree)
               Case "SH"
                  MsgBox "Whatever " & Choose(CLng(Right(C.name, 1)), lblDayOne, lbldaytwo, lbldaythree)
            End Select
         End If
      End If
   Next C

 

[raccoon588]

It is looping through the Full Days but does not move to the other two cases.

I was just able to adjust where it was getting hung up. thank you. it works

 

[Fluff]

Glad to help & thanks for the feedback

 

[raccoon588]

is there also a way to take the data from the message and populate it into the excel sheet behind the user form? in an ongoing list?

 

[Fluff]

Something like

               Case "FD"
                  msg = "Full Day " & Choose(CLng(Right(C.name, 1)), lblDayOne, lbldaytwo, lbldaythree)
                  mesgbox msg
                  Range("A" & Rows.count).End(xlUp).Offset(1).Value = msg