Use userform checkbox to select columns

[Jimmypop]

Good day again

I have a Userform with 12 Checkboxes on it. I am struggling to get it right with this project. I have done this on a previous project where if value of checkbox is true then it will select a row.

So my issue now, and I don't know what I am doing wrong, is that on Userform when I select Checkbox 1 then it will select the column that I want... However when I select Checkbox 1 and let's say Checkbox 7 then it will only select the column range linked to Checkbox 7 and not Checkbox 1 and 7... I need the end user to select whatever checkbox/s they want and then it needs to select those columns together with "A1:A39". So basically if they select Checkbox1 then it needs to select "A1:A39" together with "B1:B39"... If they select Checkbox1 and Checkbox7 then it needs to select "A1:A39" together with "B1:B39" and "H1:H39" etc...

Here is the code I am using...

Private Sub GenerateData_Click()

Range("A1:A39").Select

If Me.Checkbox1.Value = True Then
  Range("B1:B39").Select
End If

If Me.Checkbox2.Value = True Then
  Range("C1:C39").Select
End If

If Me.Checkbox3.Value = True Then
  Range("D1:D39").Select
End If

If Me.Checkbox4.Value = True Then
  Range("E1:E39").Select
End If

If Me.Checkbox5.Value = True Then
  Range("F1:F39").Select
End If

If Me.Checkbox6.Value = True Then
  Range("G1:G39").Select
End If

If Me.Checkbox7.Value = True Then
  Range("H1:H39").Select
End If

If Me.Checkbox8.Value = True Then
  Range("I1:I39").Select
End If

If Me.Checkbox9.Value = True Then
  Range("J1:J39").Select
End If

If Me.Checkbox10.Value = True Then
  Range("K1:K39").Select
End If

If Me.Checkbox11.Value = True Then
  Range("L1:L39").Select
End If

If Me.Checkbox12.Value = True Then
  Range("M1:M39").Select
End If

End Sub

 

[Fluff]

How about

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
   
   Set Rng = Range("A1:A39")

   For i = 1 To 12
      If Me.Controls("CheckBox" & i) Then
         Set Rng = Union(Rng, Cells(1, i + 1).Resize(39))
      End If
   Next i
   Rng.Select
End Sub

 

[Jimmypop]

How about

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
  
   Set Rng = Range("A1:A39")

   For i = 1 To 12
      If Me.Controls("CheckBox" & i) Then
         Set Rng = Union(Rng, Cells(1, i + 1).Resize(39))
      End If
   Next i
   Rng.Select
End Sub

Hi Fluff

Getting attached error on this line...

    If Me.Controls("CheckBox" & i) Then

Perhaps I should mention that I am using custom names for my Checkboxes which are in order Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar

Reason I used Checkbox in above code was to make it easier to understand... Apologies for any inconvenience caused.

 

[Fluff]

Reason I used Checkbox in above code was to make it easier to understand

NEVER do that, it does not make anything "simpler" or "easier", it usually just the opposite.
Which check select which column?

 

[Fluff]

Assuming that Apr selects col B, May col C etc try

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
   Dim Ary As Variant
   
   Ary = Array("Apr", "May", "Jun")
   Set Rng = Range("A1:A39")

   For i = 1 To UBound(Ary)
      If Me.Controls(Ary(i)) Then
         Set Rng = Union(Rng, Cells(1, i + 2).Resize(39))
      End If
   Next i
   Rng.Select
End Sub

Just add the rest of the checkbox names to the array in blue.

 

[Jimmypop]

Assuming that Apr selects col B, May col C etc try

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
   Dim Ary As Variant
  
   Ary = Array("Apr", "May", "Jun")
   Set Rng = Range("A1:A39")

   For i = 1 To UBound(Ary)
      If Me.Controls(Ary(i)) Then
         Set Rng = Union(Rng, Cells(1, i + 2).Resize(39))
      End If
   Next i
   Rng.Select
End Sub

Just add the rest of the checkbox names to the array in blue.

Hi Fluff

Apologies for only getting back now. When your message came through I was already at work. Code works perfectly for everything else except the checkbox named Apr which selects col B. The rest of them all select as they supposed to.

 

[Jimmypop]

UPDATE...

H Fluff

I investigated a bit and got it working... I changed

For i = 1 To UBound(Ary)

to

For i = 0 To UBound(Ary)

Thanks so much for help and in future I will refrain from making things "simpler" ? ? ?

 

[Fluff]

You're welcome & thanks for the feedback

 

[Jimmypop]

Hi Fluff

How would I go about changing code so that when checkbox is selected it selects a row... I tried changing code below:

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
   
   Set Rng = Range("A1:A39")

   For i = 1 To 12
      If Me.Controls("CheckBox" & i) Then
         Set Rng = Union(Rng, Cells(1, i + 1).Resize(39))
      End If
   Next i
   Rng.Select
End Sub

To

Private Sub GenerateData_Click()
   Dim Rng As Range
   Dim i As Long
   
   Set Rng = Range("A1:M1")

   For i = 1 To 38
      If Me.Controls("CheckBox" & i) Then
         Set Rng = Union(Rng, Cells(i + 1, 1).Resize(38))
      End If
   Next i
   Rng.Select
End Sub

So basically the range to select all the time will run from A1:M1 (13 Columns), then the row to select will be dependent on which checkbox is selected. There are 38 checkboxes in total. None have been renamed, so they start at Checkbox1 to Checkbox 38.

Checkbox1 to select Row2
Checkbox2 to select Row3
Checkbox3 to select Row4
Checkbox4 to select Row5 etc.

It does select the Rng row correctly just not the rows linked to checkboxes...

Thanks in advance.

 

[Jimmypop]

UPDATE...

Played around with code and came up with below which is working...

Private Sub GenerateData_Click()

 Dim Rng As Range
   Dim i As Long
   Dim Row As Range
   Set Rng = Range("A1:M1")

   For i = 1 To 38
      If Me.Controls("CheckBox" & i) Then
         Set Rng = Union(Rng, Cells(i + 1, 1).Resize(1, 13)) 'Changes made here
      End If
   Next i
   Rng.Select
End Sub