User From MultiPage - Loop through 8 check boxes allowing True or False for each

[VicN]

Limited VBA experience. Trained in structured languages 30 years ago.

I have a userform with a 6 tab multipage control.

On any page, there are several check boxes.

How do I loop through all check boxes (cb_anything) on a given page, i.e.: Me.Multipage1.Pages(5), being able to check/uncheck each cb an unlimited amount of times, and, depending on what's checked, if those cb.values = TRUE, then [I99].Value = 1, [I103].Value = 1, etc. The values would be set ONLY When I move off the page, either to another page on Multipage1.Pages(5) or another control on the userform.

To say it another way, on the active page, let me check or uncheck anything I want; what's checked when I leave the page, those TRUE values trigger cell values to set to 1.

I think after I move off the page by another action, I will set the property of the just executed page to Enabled=FALSE. I have a master reset cmd button on the page that will allow start over.

I'm brand new to this forum, so forgive if this is a very "noob" and basic structure. Apologies in advance. Help appreciated.

 

[DanteAmor]

The values would be set ONLY When I move off the page, either to another page on Multipage1.Pages(5) or another control on the userform.

Try this.

Dim MulValue As Long 'At the beginning of all the code

Private Sub MultiPage1_Click(ByVal Index As Long)
  Dim chkB As CheckBox, ctrl As MSForms.Control
  If MulValue = 4 Then  'For the page 5
    For Each ctrl In Me.MultiPage1.Pages(4).Controls
      If TypeName(ctrl) = "CheckBox" Then
        Controls(ctrl.Name).Value = True
      End If
    Next
  End If
  MulValue = MultiPage1.Value
End Sub

Private Sub UserForm_Activate()
  MultiPage1.Value = 0  'Start on the first Page.
End Sub

 

[GWteB]

If you use the TAG property of each checkbox to enter the corresponding cell address, you can use the code below.

Option Explicit

Private Sub MultiPage1_Change()
    Call SubmitToSheet
End Sub

Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call SubmitToSheet
End Sub

Private Sub SubmitToSheet()
    Dim ctl As Control
    Dim i
    With Me.MultiPage1
        For Each ctl In .Pages(.Value).Controls
            If TypeName(ctl) = "CheckBox" Then
                If ctl.Value Then
                    Sheets("Sheet1").Range(ctl.Tag).Value = 1
                End If
            End If
        Next ctl
    End With
End Sub

 

[VicN]

Try this.

Dim MulValue As Long 'At the beginning of all the code

Private Sub MultiPage1_Click(ByVal Index As Long)
  Dim chkB As CheckBox, ctrl As MSForms.Control
  If MulValue = 4 Then  'For the page 5
    For Each ctrl In Me.MultiPage1.Pages(4).Controls
      If TypeName(ctrl) = "CheckBox" Then
        Controls(ctrl.Name).Value = True
      End If
    Next
  End If
  MulValue = MultiPage1.Value
End Sub

Private Sub UserForm_Activate()
  MultiPage1.Value = 0  'Start on the first Page.
End Sub

If you use the TAG property of each checkbox to enter the corresponding cell address, you can use the code below.

Option Explicit

Private Sub MultiPage1_Change()
    Call SubmitToSheet
End Sub

Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call SubmitToSheet
End Sub

Private Sub SubmitToSheet()
    Dim ctl As Control
    Dim i
    With Me.MultiPage1
        For Each ctl In .Pages(.Value).Controls
            If TypeName(ctl) = "CheckBox" Then
                If ctl.Value Then
                    Sheets("Sheet1").Range(ctl.Tag).Value = 1
                End If
            End If
        Next ctl
    End With
End Sub

Thank you very much. My initial explanation, I feel, is lacking:

I have 3 radio buttons with the same group name outside the multipage. Depending on which radio button is selected, each of the 5 checkboxes can be either T or F. If T, values from one worksheet within the workbook and copied to the main worksheet. Each radio button would necessitate a unique "pull" of from the worksheet. I have it outside a loop, but it's clunky. My coding skills are very rusty. Here is what I have that demonstrates what I'm trying to do:

Private Sub cb_CCP_Complete_Click()

' ADD ADDITIONAL YEARS OF CCP COMPLETE
' CCP_Complete is one of the 5 check boxes on page of MultiPage Form
        
    If ob_HS_B_Elite.Value = True Then 'option button for HSBE
    
        [I93].Value = 1
        [K93].Value = ThisWorkbook.Worksheets(3).[A29].Value
        [L93].Value = ThisWorkbook.Worksheets(3).[B29].Value
        [M93].Value = ThisWorkbook.Worksheets(3).[C29].Value
        
    ElseIf ob_HS_B.Value = True Then 'option button for HSB
    
        [I93].Value = 1
        [K93].Value = ThisWorkbook.Worksheets(3).[A33].Value
        [L93].Value = ThisWorkbook.Worksheets(3).[B33].Value
        [M93].Value = ThisWorkbook.Worksheets(3).[C33].Value
        
    ElseIf ob_HS307.Value = True Then 'option button for HS307
    
        [I93].Value = 1
        [K93].Value = ThisWorkbook.Worksheets(3).[A37].Value
        [L93].Value = ThisWorkbook.Worksheets(3).[B37].Value
        [M93].Value = ThisWorkbook.Worksheets(3).[C37].Value
    
    Else
    
        [I93].Value = ""
        [K93:M93].Value = ""
        
    End If
    
End Sub

 

[VicN]

If you use the TAG property of each checkbox to enter the corresponding cell address, you can use the code below.

Option Explicit

Private Sub MultiPage1_Change()
    Call SubmitToSheet
End Sub

Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call SubmitToSheet
End Sub

Private Sub SubmitToSheet()
    Dim ctl As Control
    Dim i
    With Me.MultiPage1
        For Each ctl In .Pages(.Value).Controls
            If TypeName(ctl) = "CheckBox" Then
                If ctl.Value Then
                    Sheets("Sheet1").Range(ctl.Tag).Value = 1
                End If
            End If
        Next ctl
    End With
End Sub

Thank you very much for the reply. I'm not sure this is what I'm looking for - my coding skills are very rusty and I think my explanation was obtuse, now that I re-read it. I've posted a reply to another member; perhaps that will make it more clear? Thank you again.

 

[DanteAmor]

Depending on which radio button is selected, each of the 5 checkboxes can be either T or F. If T, values from one worksheet within the workbook and copied to the main worksheet.

Sorry, but now I don't understand.
Could you explain what you need, but without code.
Use some images of what you have and what you want as a result.

 

[GWteB]

I am also not sure whether I understand everything correctly