On Checkbox click insert item into another ws

[kylee]

Hi y'all,
I've got a workbook with a few worksheets in it. One of those sheets is full of checkboxes that are linked to a cell 2 spots over (if that matters). I'm trying to accomplish ...

Master has the checkboxes. A,B,C=checkbox, item, linked cell. List is the sheet I want to be auto'ed. When I click a checkbox on Master tab (column A), I want the word on that row column B to be inserted into List tab column B.

I've figured out how to put in the checkboxes (for 150 checkboxes, there's GOT to be a faster way!!!) via the form toolbar, but heck if I can figure out how to make the other sheet auto populate.

I look forward to your replies. Thank you --- as always.

~Ky

 

[BrandonWLH]

Dim myLeft, myInitLeft As Double
    Dim myTop, myInitTop As Double
    Dim myHeight As Double
    Dim myWidth, hOff, vOff, topLeft, topTop As Double
    Dim iCust, vshift As Integer
 
    vshift = totalProducts
    If vshift > 3 Then vshift = 4
 
    myHeight = 28.5
    myWidth = 115
 
    Range("PrescriberProducts").RowHeight = myHeight
    Range("PrescriberProducts").RowHeight = Range("PrescriberProducts").RowHeight + myHeight * (vshift - 1)
 
    For iCust = 1 To 5
        topTop = Range("PrescriberProducts").Top
        topLeft = Range("PrescriberProducts").Offset(0, iCust - 1).Left
 
        For i = 1 To totalProducts
 
            vOff = ((i - 1) Mod 4) * myHeight
            hOff = Int((i - 1) / 4) * (myWidth + 5)
 
            myTop = topTop + vOff
            myLeft = topLeft + hOff
 
 
            Worksheets("4.3 Prescriber Profile").CheckBoxes.Add( _
                Left:=myLeft, Top:=myTop, Width:=myWidth, Height:=myHeight).Select
                    With Selection
                        .Name = "ProdCheckBox" & iCust & i
                        .Caption = strProducts(i)
                        .Display3DShading = True
                    End With
 
        Next i
 
    Next iCust

Here is a code chunk I wrote which autopopulates 5 columns of cells with a 2 column set of check boxes. iCust controls the 5 columns of cells. Total Products is the total # of checkboxes to make. vOff and hOff control the placement of the check boxes into 2 columns. The number 4 is used to determine when the boxes count goes past 4 to make the 2nd column populate for a maximum of 8 checkboxes for my application, but its not a set number. Just increase the totalProducts var to get more boxes. Just play with the #4 in this spot

vOff = ((i - 1) Mod 4) * myHeight
hOff = Int((i - 1) / 4) * (myWidth + 5)

to adjust the check box placement.

Vshift controls the height of the cell where the boxes are to be place, not necesarry for you, but was for my application.

 

[kylee]

Brandon,
Thank you for your help. Unfortunately, I have barely even begun to start learning VBA - the code you gave me is way over my head. I was able to tweak a few things, but I ain't gettin' nowhere with it. lol

Shoot, I can't even figure out where, exactly, to put the code! I think I'm gonna read a few more chapters from www.ozgrid.com/Excel/free-training/ExcelVBA1 I'm only up to lesson 3 so far.

Again, thank you for your help, it's not your fault I can't get it to work. Have a good one, back to the books ...

~Ky