Auto-Create Checkboxes w/ VBA

Jaclyn · Jan 13th 2006

Hi,

Apologies if this doesn't make sense...I don't entirely know what I'm talking about.

I have written some code which, when run, creates a worksheet. It's pretty much a table. When the sheet is created, I would like two checkboxes to also be created beside the table. These will be used to expose/hide certain parts of the table (but I'll worry about that part later). I've tried different code I've found but nothing works and I think it's because it is for adding a checkbox to a form and not directly into Excel. I've been trying to use something along these lines:

Set myLabel = frmMyForm.Controls.Add("Forms.Label.1", "lblPrompt")

But I don't exactly know what this does!

Also, would I embed this code along with my existing code or would it be a separate sub.

Thanks for any help!

Dave Hawley · Jan 13th 2006

Re: Auto-Create Checkboxes w/ VBA

Hi Jaclyn

You could perhaps create a Template Worksheet and have the Checkboxes already in place.

However, the code below will add an ActiveX Checkbox (from the Control Toolbox Toolbar) to cell A1

Code

ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=Range("A1").Left, _
        Top:=Range("A1").Top, Width:=108, Height:=19.5

The code below here will add a Checkbox (from the Forms toolbar) to A10

Code

ActiveSheet.CheckBoxes.Add Range("A10").Left, Range("A10").Top, 72, 72

Jaclyn · Jan 13th 2006

Re: Auto-Create Checkboxes w/ VBA

Thanks for that. I used the first lot of code. Although, I can't figure out how to change the caption name, font, things like that. I thought you would just add it on to the kend of that line.

Cheers

Dave Hawley · Jan 13th 2006

Re: Auto-Create Checkboxes w/ VBA

The code below will change those attributes

Code

Dim oleObj As OLEObject


    Set oleObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=Range("A1").Left, _
    Top:=Range("A1").Top, Width:=108, Height:=19.5)
    
    With oleObj.Object
            .Caption = "Click Me"
            .Font.Size = 10
            .Font.Italic = True
            .Font.Bold = True
    End With

Display More

Auto-Create Checkboxes w/ VBA

Participate now!

Share

Users Viewing This Thread