Results 1 to 3 of 3

Macro that will create a checkbox

This is a discussion on Macro that will create a checkbox within the Excel Questions forums, part of the Question Forums category; I want to write a macro that will create a checkbox in a cell that meets certain conditions. For example, ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    35

    Default Macro that will create a checkbox

    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

  2. #2
    Board Regular Case_Germany's Avatar
    Join Date
    May 2008
    Location
    49 6'0.43"N and 950'35.70"E
    Posts
    407

    Default Re: Macro that will create a checkbox

    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
    Case_Germany - Blog

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Macro that will create a checkbox

    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
    JSW: Try and try again: "The way of the Coder!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com