Macro that will create a checkbox

benhop

New Member
Joined
Nov 18, 2008
Messages
35
I want to write a macro that will create a checkbox in
a cell that meets certain conditions. For example, if the cell or range of cells values fall between 1 and -1 then a macro would generate a checkbox in that cell or range of cells. Right now, I want this to happen when I activate a specified worksheet upon opening a workbook. Can anybody help? The beginning of my code looks like this:

Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Application.Range.Value <= 1 And   Application.Range.Value >= -1 Then
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi, :)

try - and change the necessary parameters:

Code:
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim rngCell As Range
    If Sh.CodeName = "Sheet1" Then ' adapt
        For Each rngCell In Sh.Range("A1:A5") ' adapt
            If Trim(rngCell.Value) <> "" Then
                If rngCell.Value <= 1 And rngCell.Value >= -1 Then
                    Sh.OLEObjects.Add ClassType:="Forms.CheckBox.1", _
                        Left:=rngCell.Left, _
                        Top:=rngCell.Top, _
                        Width:=rngCell.Width, _
                        Height:=rngCell.Height
                End If
            End If
        Next
    End If
End Sub
The code belonged in "ThisWorkbook".

Case_Germany
 
Upvote 0
This is just another way:

Sub myCheckbox()
Dim objCBox As Object, c As Object

For Each c In Selection
If (c.Value <= 1 And c.Value >= -1) Then
c.Select

Set objCBox = ActiveSheet.CheckBoxes.Add _
(Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
Width:=20, Height:=ActiveCell.Height)
End If
Next c
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top