VBA: I cannot find the "correct" Backcolor setting for a worksheet checkbox

[ez08mba]

Through intellisense, I cannot find the backcolor fill setting for a checkbox shape within an Excel spreadsheet. I've used the "With" statement to make sure I'm selecting the one-and-only checkbox on the spreadsheet as well as the entire workbook. I'm not sure why this doesn't work?

    With wsTemplate.Shapes(1)
        .Fill.BackColor.RGB = RGB(170, 170, 170)

        MsgBox .Name
    End With

 

[Fluff]

Use ForeColor, rather than BackColor

 

[ez08mba]

Use ForeColor, rather than BackColor

Hi Fluff!

That didn't work but I have tried that before. I'm sure you know, but this is a checkbox added from the controls under the Developer tab and not on a form. It shows as =EMBED(Form.Checkbox.1) when clicked on. I can't even get to the control properties manually. This is the first I've tried to interact with a control not located on a form, so I have no clue here.

Thanks!

 

[Fluff]

The problem is you are using an ActiveX checkbox. Try deleting it & replacing it with a Form Control checkbox instead

 

[ez08mba]

The problem is you are using an ActiveX checkbox. Try deleting it & replacing it with a Form Control checkbox instead

I did change it to a form control. I hadn't realized, but do remember, selecting the ActiveX. So I replaced it with the "Form control" checkbox as you suggested. It still won't change the color. I can select the control, pull the name, resize it but I cannot change the color. I don't receive an error, it simply won't change the color. There has to be something stupid that I'm missing.

        With wsTemplate.Shapes(1)
            .Select
            Cells(4, 3) = .Name
            .Height = 20
            .Width = 200
            .Fill.BackColor.RGB = RGB(0, 0, 255)
            .Fill.ForeColor.RGB = RGB(0, 0, 255)
        End Wit

 

[ez08mba]

I did more searching and discovered this and it worked. However, ".CheckBoxes" didn't come up as a method option with my wsTemplate reference. I took it as hoping it would work and it did. This still doesn't explain to me though why the other way doesn't work when I can do all the same things except change the color?

        With wsTemplate.CheckBoxes(1)
            .Select
            Cells(4, 3) = .Name
            .Height = 20
            .Width = 200
            .Interior.ColorIndex = 45
'            .Fill.BackColor.RGB = RGB(0, 0, 255)
'            .Fill.ForeColor.RGB = RGB(0, 0, 255)
        End With

 

[ez08mba]

One more thing that is interesting... If I record a macro expanding the shape of the CheckBox and change the color, the macro "does not" record changing the background color. There must be a glitch somewhere.

    ActiveSheet.Shapes("Check Box 1").ScaleWidth 1.587628866, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Check Box 1").ScaleHeight 2.0322580645, msoFalse, _
        msoScaleFromTopLeft

 

[Fluff]

Glad you got it sorted.
Not sure why the code from post#5 doesn't work for you if it gives the right name & changes the size, as it works for me.