checkbox problem...

buzz71023

Active Member
Joined
May 29, 2011
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
i have a userform with 10 checkboxes that define packaging. i have a worksheet the with a lot of packaging data on in. At any time I could have as many as three of these boxes check (most of the time just one is check). When the boxes are checked is there a way that i can get those results to display in my worksheet in cells B2, C2, and D2.

For example on my userform i might have checked, Packaging 1, Packaging 2, and Packaging 8 and when I click next or add cmd button I want it to add the results to that range of cells (b2-d2).

If this is not feasible or someone has a better idea, I'll take suggestions.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
It's not clear if you want to populate cells with the caption of the selected checkboxes, or, because you said "add the results" maybe you want to look up numbers in some column on your sheet associated with the checked package(s). Try explaining more about what you are after and if math is involved, how your sheet is laid out so someone can have a better idea of what you are doing.
 
Upvote 0
Sorry, yes I would like to add the caption of the checked checkbox to certain cells.
 
Upvote 0
Do you want to put the captions of the checked buttons across row 2 starting in column B?

This might get you started, it assumes the checkboxes are name CheckBox1, Checkbox2 etc - change that if needed.
Code:
Private Sub CommandButton1_Click()
Dim I As Long
Dim rng As Range
 
                      ' put captions in next empty row
                      'Set rng = Range("B" & Rows.Count).End(xlUp).Offset(1)
    
    ' put captions in row 2
    Set rng = Range("B2")
    
    For I = 1 To 10
    
        If Me.Controls("CheckBox" & I).Value Then
            rng.Value = Me.Controls("CheckBox" & I).Caption
            Set rng = rng.Offset(, 1)
        End If
    Next I
End Sub
 
Upvote 0
Yes, I actually just had a request and I had to change the order of something and not everything is exactly the same except the now the cells the captions of the checkboxes will go into will be C7,8 and 9.

Thank you for helping me by the way
 
Upvote 0
Just change A2 to B7 in the code.
 
Upvote 0
Works perfectly, all I have to do is get it to count down on columns instead of across on rows
 
Upvote 0
Sorry misread my phone - it's got a tiny screen.

Anyway it should have been B2 you changed but you would also need to change the offset for rows.

Here's the whole code with alterations:
Rich (BB code):
Private Sub CommandButton1_Click()
Dim I As Long
Dim rng As Range
 
                      ' put captions in next empty row
                      'Set rng = Range("B" & Rows.Count).End(xlUp).Offset(1)
 
    ' put captions in row 2
   Set rng = Range("C7")
 
    For I = 1 To 10
 
        If Me.Controls("CheckBox" & I).Value Then
            rng.Value = Me.Controls("CheckBox" & I).Caption
           Set rng = rng.Offset(1)
        End If
    Next I
End Sub
 
Upvote 0
You dont have to apoligize for anything because you are taking time out of your day to help me. So thank you. Everything worked perfectly by the way after I fixed that and then added in the other parts that I had wrote. It worked flawlessly. So thank you again
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top