Check boxes on a userform

[mummbles74]

I have created a template sheet that I will be using for my team to create quotation. I have generated the first part of the form that completes the pricing part.
I have a userform that I want to use to select different conditions to a quotation. I want the user to be able to tick/check the check boxes and for the text in the caption to be used to create a list that is then inserted into the sheet. I learn on the go all the time and it has been a while since I have used this level of code so I want to be able to work through 1 step at a time.

The first task i want to accomplish is creating the body of text (or number of filled rows) that is a result of the selected boxes. i am making the assumption that the captions might not be able to be used for this and I will have to generate the text on a separate sheet or in the code itself.

Thanks in anticipation.

 

[gtwo]

Upload an image, of a sample of your data e also the userform you have already created, so we can start it step by step.

 

[mummbles74]

i have kept the check boxes as the (name) and the same for the command buttons. There will be a lot more than 4 checkboxes but I assume the process will just repeat

Button 1 will be the OK button that allows what ever boxes are selected to have there data inserted in column A row 55 and the selection to be entered in the next available row so if only checkbox 1 and 4 are selected they would insert into row 55 & 56 etc.

Button 2 would be to clear the checkboxes

Button 3 will be to close the form with no action at all

I hope that all makes sense, if there is anything to clarify then please let me know

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim ctrl As MSForms.Control, i As Long
  i = 55
  For Each ctrl In Controls
    If TypeName(ctrl) = "CheckBox" Then
      If ctrl.Value Then
        Range("A" & i).Value = ctrl.Caption
        i = i + 1
      End If
    End If
  Next
End Sub

Private Sub CommandButton2_Click()
  Dim ctrl As MSForms.Control
  For Each ctrl In Controls
    If TypeName(ctrl) = "CheckBox" Then ctrl.Value = False
  Next
End Sub

Private Sub CommandButton3_Click()
  Unload Me
End Sub

 

[gtwo]

Here is the UserForm, and a sheet where the data of each checkbox is filled IF it is checked

And here is the code of this UserForm. There are better ways of doing this code, using a loop go go through each control, but the way I did maybe is easier for you to understand the code

'This is the OK button
Private Sub CommandButton1_Click()
   
    Dim next_row As Long    'This will be variable to store the number of the next empty
                            'row at column A to fill the data of each checkbox in that row
   
'__________________________________________________________________________________________
    If Me.CheckBox1 = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox1.Caption
    End If
'___The rest of the code, just repeats this part, changing Only the CheckBox name___________
   
    If Me.CheckBox2 = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox2.Caption
    End If
   
    If Me.CheckBox3 = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox3.Caption
    End If
   
    If Me.CheckBox4 = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox4.Caption
    End If
   
    'And so on...
   
End Sub

'This is the Clear button
Private Sub CommandButton2_Click()
    Me.CheckBox1 = False
    Me.CheckBox2 = False
    Me.CheckBox3 = False
    Me.CheckBox4 = False
End Sub

'This is the Close button. It only closes the userform
Private Sub CommandButton3_Click()
    Unload Me
End Sub

 

[BNR]

Hi,

The above code is missing the '.value' for each control object.
I think part the above code should read;

'This is the OK button
Private Sub CommandButton1_Click()
   
    Dim next_row As Long    'This will be variable to store the number of the next empty
                            'row at column A to fill the data of each checkbox in that row
   
'__________________________________________________________________________________________
    If Me.CheckBox1.value = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox1.Caption
    End If
'___The rest of the code, just repeats this part, changing Only the CheckBox name___________
   
    If Me.CheckBox2.value = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox2.Caption
    End If
   
    If Me.CheckBox3.value = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox3.Caption
    End If
   
    If Me.CheckBox4.value = True Then
        next_row = Sheets("Sheet1").Range("A500000").End(xlUp).Row + 1
        Sheets("Sheet1").Range("A" & next_row).Value = Me.CheckBox4.Caption
    End If
   
    'And so on...
   
End Sub

'This is the Clear button
Private Sub CommandButton2_Click()
    Me.CheckBox1.value = False
    Me.CheckBox2.value = False
    Me.CheckBox3.value = False
    Me.CheckBox4.value = False
End Sub

'This is the Close button. It only closes the userform
Private Sub CommandButton3_Click()
    Unload Me
End Sub

Also the next_row value could be calculated as follows;

next_row = Sheets("Sheet1").UsedRange.Rows.Count + 1

This code also assumes the required sheet is named "Sheet1".
This is a fair assumption, but should also be noted if the OP actually uses a different sheet.

regards,
BenR

 

[mummbles74]

All

Thank you all for the responses, i seem to have got the function I am looking for from DanteAmor's code. I will continue to add the additional conditions that I have and ensure the functionality continues.

Many thanks as always!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[mummbles74]

Is there any way to set i as the first blank row in column A after ROW 55?

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim ctrl As MSForms.Control, i As Long
  i = Range("A" & Rows.Count).End(xlUp).Row + 1
  If i < 55 Then i = 55
  For Each ctrl In Controls
    If TypeName(ctrl) = "CheckBox" Then
      If ctrl.Value Then
        Range("A" & i).Value = ctrl.Caption
        i = i + 1
      End If
    End If
  Next
End Sub