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

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
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?

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

        MsgBox .Name
    End With
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,987
Office Version
365
Platform
Windows
Use ForeColor, rather than BackColor
 

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,987
Office Version
365
Platform
Windows
The problem is you are using an ActiveX checkbox. Try deleting it & replacing it with a Form Control checkbox instead
 

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
192
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.

Code:
        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

Board Regular
Joined
Nov 1, 2011
Messages
192
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?

Code:
        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

Board Regular
Joined
Nov 1, 2011
Messages
192
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.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,987
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,081,538
Messages
5,359,397
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top