How do I get an ActiveX checkbox to have a solid black border?

[Ayugma]

I want my ActiveX checkbox to have a solid black border. Is there an easy way to do this with VBA? Since I don't see it as a value within Properties. Thank you!

 

[Logit]

.
The following includes a property to adjust the border width. Perhaps you can work with the entire macro ... trim it down to suit.

Sub FormControl_CheckBox_Shape_Properties_1()
'Add a Form Control Check Box with the CheckBoxes.Add Method, set properties for the Form control (CheckBox) object - Refer Image 3a


Dim shpChBx As Shape, ws As Worksheet, rng As Range, objChBx As OLEObject

Set ws = Sheets("Sheet1")

ws.Activate
'------------------------

'a quick way of adding multiple checkboxes (Form Control) in a worksheet & then deleting:

'Dim cell As Range, rng As Range

'Set rng = Range("J5:J10")

'Set ws = Sheets("Sheet4")

'ws.Activate

'For Each cell In rng

'With ws.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)

'.Characters.Text = cell.Address

'End With

'Next

'ws.CheckBoxes.Delete

'------------------------

Set rng = ws.Range("B2")

'delete ActiveX Control CheckBox objects

For Each objChBx In ActiveSheet.OLEObjects

'TypeName Function returns the data-type about a variable - TypeName(varname)

If TypeName(objChBx.Object) = "CheckBox" Then objChBx.Delete

Next objChBx


'delete Form Control CheckBox objects

For Each shpChBx In ActiveSheet.Shapes

If shpChBx.Type = msoFormControl Then

If shpChBx.FormControlType = xlCheckBox Then shpChBx.Delete

End If

Next shpChBx


'Range.ColumnWidth Property returns or sets the width of all columns in the specified range, where one unit of column width is equal to the width of one character in the Normal style (width of the character zero is used for proportional fonts) - to return (does not set) the width of the range in points, use the Range.Width property

rng.ColumnWidth = 10

'Range.RowHeight Property returns or sets the height of the first row in the range, in points - to set or return the total height of a range of cells in points, use the Height property.

rng.RowHeight = 15


'add a check box Form control - position & size the Check Box with the cell assigned to the rng variable

ws.CheckBoxes.Add(Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Select

With Selection

'set a String value as object's name

.Name = "Check Box 1"

'Placement property determines how the object is attached to its underlying cells viz. xlFreeFloating (Object is free floating - does not move or size with cells), xlMove (Object is moved with the cells), xlMoveAndSize (Object is moved and sized with the cells)

.Placement = xlFreeFloating

'use the Locked Property of the Shapes object to lock it - if not Locked (False), the object can be modified when the sheet is protected

.Locked = True

.Display3DShading = False

'True (for Enabled Property) indicates that the control can be accessed through a vba code & respond to user-generated events (ie. the user can interact with the control by using mouse, keystrokes, accelerators, or hotkeys) - enable the object

.Enabled = True

'check box will be printed when the document is printed

.PrintObject = True

.LinkedCell = rng.Address

'wrt to the cell which is under the upper-left corner of the checkbox - enter text in the same row to the right of the checkbox

.TopLeftCell.Offset(0, -1) = "Select Country"

'Value property of a CheckBox indicates whether it is selected or not.

.Value = xlOn

'.Value = xlOff

'specify caption text

.Characters.Text = "USA"

'alternatively:

'.Caption = "USA"

'use the Shape.OnAction Property to assign a macro (ie. sets the macro name) to the shape - clicking on the shape will execute the ChkBoxClick macro

.OnAction = "ChkBoxClick"


'a ShapeRange object represents a subset of shapes on a document - it can contain a single shape, or a few shapes (viz. first, third & fifth shape out of a total of 8 shapes in a sheet), or it could represent all shapes in a document

With .ShapeRange

'shape will retain its original proportions when it is resized

.LockAspectRatio = msoTrue

'set the alternative text to a shape description when Shape object is saved to a Web page - alternative text may be displayed in the web browser in lieu of the shape's image, or over the shape's image when the mouse pointer hovers over the image (if browser supported).

.AlternativeText = "Click to Select"

'set check box fill / interior

With .Fill

.Visible = msoTrue

.Solid

'.ForeColor.SchemeColor = 13

.ForeColor.RGB = RGB(255, 255, 0)

.Transparency = 0

End With

'set border for check box

With .Line

.Weight = 3                     '<<------ this will change border width

.DashStyle = msoLineSolid

.Style = msoLineSingle

.Transparency = 0

.Visible = msoTrue

'.ForeColor.SchemeColor = 10

.ForeColor.RGB = RGB(255, 0, 0)

End With

End With

End With


'--------------------------

'copy check box and create new check box with same properties

'set range size, for cell below the existing check box

rng.Offset(1, 0).ColumnWidth = rng.ColumnWidth

rng.Offset(1, 0).RowHeight = rng.Height


'copy check box to clipboard

ws.Shapes("Check Box 1").Copy

'paste a copy of the checkbox in the same sheet

ws.Paste

'fit new check box in the cell below the existing check box

Selection.Left = rng.Offset(1, 0).Left

Selection.Top = rng.Offset(1, 0).Top

Selection.Name = "Check Box 2"

Selection.LinkedCell = rng.Offset(1, 0).Address

Selection.Caption = "UK"

'use the Shape.OnAction Property to assign a macro (ie. sets the macro name) to the shape - clicking on the shape will execute the ChkBoxClick macro

Selection.OnAction = "ChkBoxClick"

 

'--------------------------

'merge & format cells to the left of both check boxes

Union(rng.Offset(0, -1), rng.Offset(1, -1)).Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.ReadingOrder = xlContext

.MergeCells = True

.WrapText = True

.Interior.Color = RGB(0, 255, 0)

.Borders.LineStyle = xlContinuous

.Borders.ColorIndex = 5

.Borders.Weight = xlMedium

End With


End Sub

 

[Ayugma]

I have no idea what any of that does unfortunately.

 

[Logit]

I removed some of the extraneous lines of code from the macro. The macro now creates specifically what you are seeking - ActiveX checkbox with bold borders.

Paste the entire macro into a Regular Module. Create a Command Button on Sheet 1 and connect it to the macro. Click the button and observe.

If you read the macro comments (in green color) it describes specifically what each section of the code does. It is very understandable. If you like, once the checkboxes
are created, you can move them to any location on the sheet you like ... or you can edit the code so the checkbox are created in the cell you need.

Create a new workbook, paste the macro as described above and play with the code. You won't break anything. If the code stops working and you don't understand
what you've done .... create the workbook again (or copy a backup version) and start again. Although the macro may look overwhelming at this point - after messing
around with it for awhile it will lose it's mystique and won't be so frightening.

Option Explicit

Sub FormControl_CheckBox_Shape_Properties_1()
'Add a Form Control Check Box with the CheckBoxes.Add Method, set properties for the Form control (CheckBox) object - Refer Image 3a


Dim shpChBx As Shape, ws As Worksheet, rng As Range, objChBx As OLEObject

Set ws = Sheets("Sheet1")

ws.Activate


'------------------------

Set rng = ws.Range("B2")

'delete ActiveX Control CheckBox objects

For Each objChBx In ActiveSheet.OLEObjects

'TypeName Function returns the data-type about a variable - TypeName(varname)

If TypeName(objChBx.Object) = "CheckBox" Then objChBx.Delete

Next objChBx


'delete Form Control CheckBox objects

For Each shpChBx In ActiveSheet.Shapes

If shpChBx.Type = msoFormControl Then

If shpChBx.FormControlType = xlCheckBox Then shpChBx.Delete

End If

Next shpChBx


'Range.ColumnWidth Property returns or sets the width of all columns in the specified range, where one unit of column width is equal to the width of one character in the Normal style (width of the 'character zero is used for proportional fonts) - to return (does not set) the width of the range in points, use the Range.Width property

rng.ColumnWidth = 10

'Range.RowHeight Property returns or sets the height of the first row in the range, in points - to set or return the total height of a range of cells in points, use the Height property.

rng.RowHeight = 15


'add a check box Form control - position & size the Check Box with the cell assigned to the rng variable

ws.CheckBoxes.Add(Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Select

With Selection

'set a String value as object's name

.Name = "Check Box 1"

'Placement property determines how the object is attached to its underlying cells viz. xlFreeFloating (Object is free floating - does not move or size with cells), xlMove (Object is moved with the cells), 'xlMoveAndSize (Object is moved and sized with the cells)

.Placement = xlFreeFloating

'use the Locked Property of the Shapes object to lock it - if not Locked (False), the object can be modified when the sheet is protected

.Locked = True

.Display3DShading = False

'True (for Enabled Property) indicates that the control can be accessed through a vba code & respond to user-generated events (ie. the user can interact with the control by using mouse, keystrokes, 'accelerators, or hotkeys) - enable the object

.Enabled = True

'check box will be printed when the document is printed

.PrintObject = True

.LinkedCell = rng.Address


'Value property of a CheckBox indicates whether it is selected or not.

.Value = xlOn

'.Value = xlOff

'specify caption text

.Characters.Text = "USA"


'use the Shape.OnAction Property to assign a macro (ie. sets the macro name) to the shape - clicking on the shape will execute the ChkBoxClick macro

.OnAction = "ChkBoxClick"


'a ShapeRange object represents a subset of shapes on a document - it can contain a single shape, or a few shapes (viz. first, third & fifth shape out of a total of 8 shapes in a sheet), or it could represent 'all shapes in a document

With .ShapeRange

'shape will retain its original proportions when it is resized

.LockAspectRatio = msoTrue


'set check box fill / interior

With .Fill

.Visible = msoTrue

.Solid

'.ForeColor.SchemeColor = 13

.ForeColor.RGB = RGB(255, 255, 255)

.Transparency = 0

End With

'set border for check box

With .Line

.Weight = 3                     '<<------ this will change border width

.DashStyle = msoLineSolid

.Style = msoLineSingle

.Transparency = 0

.Visible = msoTrue

'.ForeColor.SchemeColor = 10

.ForeColor.RGB = RGB(0, 0, 0)

End With

End With

End With


'--------------------------

'copy check box and create new check box with same properties

'set range size, for cell below the existing check box

rng.Offset(1, 0).ColumnWidth = rng.ColumnWidth

rng.Offset(1, 0).RowHeight = rng.Height


'copy check box to clipboard

ws.Shapes("Check Box 1").Copy

'paste a copy of the checkbox in the same sheet

ws.Paste


'fit new check box in the cell below the existing check box

Selection.Left = rng.Offset(1, 0).Left

Selection.Top = rng.Offset(1, 0).Top

Selection.Name = "Check Box 2"

Selection.LinkedCell = rng.Offset(1, 0).Address

Selection.Caption = "UK"

'use the Shape.OnAction Property to assign a macro (ie. sets the macro name) to the shape - clicking on the shape will execute the ChkBoxClick macro

Selection.OnAction = "ChkBoxClick"

End Sub

 

[Fluff]

@Logit
Your code actually creates form control check boxes, not ActiveX ones.

@Ayugma
As far as I'm aware you cannot create borders on an ActiveX checkbox, although you can with Form Control checkboxes.

 

[Logit]

.
Here is a more concise macro that creates and ActiveX checkbox with bold borders on Sheet 1 :

Option Explicit

Dim Pub As Long
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet

Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.CheckBox.1")

    With OleObj.ShapeRange.Line
      .Weight = 3
      .DashStyle = msoLineSolid
      .Style = msoLineSingle
      .Visible = msoTrue
      .ForeColor.SchemeColor = 8
      .BackColor.RGB = RGB(255, 255, 255)
    End With

End Sub

 

[Ayugma]

.
Here is a more concise macro that creates and ActiveX checkbox with bold borders on Sheet 1 :

Option Explicit

Dim Pub As Long
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet

Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.CheckBox.1")

    With OleObj.ShapeRange.Line
      .Weight = 3
      .DashStyle = msoLineSolid
      .Style = msoLineSingle
      .Visible = msoTrue
      .ForeColor.SchemeColor = 8
      .BackColor.RGB = RGB(255, 255, 255)
    End With

End Sub

This works, thank you!

 

[Logit]

You are welcome.

 

[DominicLC]

.
Here is a more concise macro that creates and ActiveX checkbox with bold borders on Sheet 1 :

Option Explicit

Dim Pub As Long
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet

Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.CheckBox.1")

    With OleObj.ShapeRange.Line
      .Weight = 3
      .DashStyle = msoLineSolid
      .Style = msoLineSingle
      .Visible = msoTrue
      .ForeColor.SchemeColor = 8
      .BackColor.RGB = RGB(255, 255, 255)
    End With

End Sub

Hello, sorry to disturb you, I know this thread is old, but I really want to know how to make this work? I pasted the code into VBA, but nothing happens to my checkboxes. Thanks!