checkbox problem...

buzz71023

Active Member
Joined
May 29, 2011
Messages
281
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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.
 

buzz71023

Active Member
Joined
May 29, 2011
Messages
281
Sorry, yes I would like to add the caption of the checked checkbox to certain cells.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

buzz71023

Active Member
Joined
May 29, 2011
Messages
281

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Just change A2 to B7 in the code.
 

buzz71023

Active Member
Joined
May 29, 2011
Messages
281

ADVERTISEMENT

Works perfectly, all I have to do is get it to count down on columns instead of across on rows
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

buzz71023

Active Member
Joined
May 29, 2011
Messages
281
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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