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

VicN

New Member
Joined
Jul 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
If you use the TAG property of each checkbox to enter the corresponding cell address, you can use the code below.
VBA Code:
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

New Member
Joined
Jul 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try this.

VBA Code:
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.
VBA Code:
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:

VBA Code:
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

New Member
Joined
Jul 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you use the TAG property of each checkbox to enter the corresponding cell address, you can use the code below.
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I am also not sure whether I understand everything correctly :unsure:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,448
Messages
5,596,210
Members
414,046
Latest member
mbeutler1203

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