UserForms Loop Code

**pocklove** · 12-05-2011, 10:51 AM

Hi all,

I've got a Userform which has several CheckBoxes which users can select. When the users click OK on the Userform a macro runs and does the same copy and paste function on the selected sheets. Effectively, the checkboxes relate to a different tab in the sheet, so if the users select checkbox 1 and 3 then the macro runs on Sheet1 and Sheet3.

Now, I have a code which checks each Checkbox in turn and if it is true it selects the appropriate tab and runs the Copy and Paste function.

What I need help with is I want it to run this as a loop to perform the same function(mainly becasue in the code provided below I only have 3 sheets but in reality I have many)

Private Sub CommandButton1_Click()

'----------------Division 1----------------

    If CheckBox1.Value = True Then
        Sheets("Division 1").Select
        
        'Run the copy and paste macro that I need
                
    End If

Sheets("Summary").Select

'----------------Division 2----------------

    If CheckBox2.Value = True Then
        Sheets("Division 2").Select
        
        'Run the copy and paste macro that I need
                
    End If

Sheets("Summary").Select

'----------------Division 3----------------

    If CheckBox3.Value = True Then
        Sheets("Division 3").Select
        
        'Run the copy and paste macro that I need
                
    End If

Sheets("Summary").Select


End Sub

I'm not very good with loops but I want it to work along the lines of

For each checkbox in the userform
If it is checked then go to the corresponding tab and perform the macro
Then move on to the next checkbox
Repeat until all checkboxes have been checked

I hope this is clear and thanks in advance for your help

Cheers,

Pocklove

**Andy Pope** · 12-05-2011, 11:05 AM

Something like this...

Private Sub CommandButton1_Click()
    dim lngIndex as long

    for lngIndex = 1 to 3
        If me.controls("CheckBox" & lngIndex).Value = True Then
            Sheets("Division " & lngIndex).Select
        
            'Run the copy and paste macro that I need
                
        End If
    next
Sheets("Summary").Select

**pocklove** · 12-06-2011, 04:16 AM

Thanks Andy,

My apologies though, in my spreasheet the tabs aren't called Division 1, Division 2 etc (Sorry for misleading you), they relate to different divisions of the organisation, such as HR, Finance, Income etc

Would it be possible to tweak this to take those names into consideration?

Many thanks,

Pocklove

**Andy Pope** · 12-06-2011, 04:33 AM

A complete guess as I have no idea what sheets are called and how they relate to the controls.

Private Sub CommandButton1_Click()
    dim lngIndex as long
    dim vntName as variant

    vntName = Array("","HR","Income","Finance")
    for lngIndex = 1 to 3
        If me.controls("CheckBox" & lngIndex).Value = True Then
            Sheets(vntName( lngIndex) ).Select
        
            'Run the copy and paste macro that I need
                
        End If
    next
Sheets("Summary").Select