none
How do I assign a Macro to a checkbox dynamically using VBA RRS feed

  • Question

  • I have a spreadsheet that utilizes a userform for data entry. This user form will insert new rows with the data entered into the userform.

    As it inserts new rows, one of the columns for the newly inserted row will have a checkbox added.

    At the time the row/check box is added I need to assign that checkbox a macro.

    how can I do this using vba or is there another way to achieve this? Meaning once a checkbox is added for this particluar row/column combination at runtime can this checkbox have a default macro assigned to it?

    Keith


    Keith Aul

    Tuesday, May 5, 2015 7:43 PM

Answers

  • That code inserts a "Form Control" checkbox. Look at Developer, Insert

    Adapt the following examples for your needs

    Sub addCheckboxes()
    Dim i As Long, id As Long
    Dim cel As Range
    Dim cbx As CheckBox
    
        For i = 2 To 10 Step 2
            id = id + 1
            Set cel = ActiveSheet.Cells(i, 2)
            With cel
                Set cbx = ActiveSheet.CheckBoxes.Add(.Left, .Top, 90, 3)
    ' height will autosize larger to the font
            End With
            cbx.Name = "Checkbox_" & id
            cbx.Caption = "my Checkbox " & id
            
     ' with a linked can trap sheet change event or link to other formulas
            cbx.LinkedCell = cel.Offset(0, -1).Address
            cbx.OnAction = "'" & ThisWorkbook.Name & "'!CheckBOxHandler"
        Next
    
    End Sub
    
    Sub CheckBOxHandler()
    Dim sCaller As String
    Dim id As Long
    Dim cbx As CheckBox
    Dim shp As Shape
    
        sCaller = Application.Caller
        Set shp = ActiveSheet.Shapes(sCaller)
        Set cbx = ActiveSheet.CheckBoxes(sCaller)
    
        id = Val(Mid$(sCaller, Len("Checkbox_") + 1, 5))
    
        ' maybe something based on Select Case?
        Select Case id
        Case 1:    ' ??
            'etc
        End Select
    
        cbx.TopLeftCell.Offset(, 2).Interior.Color = IIf(cbx.Value = xlOn, vbGreen, vbRed)
        MsgBox cbx.Caption & vbCr & IIf(cbx.Value = xlOn, " ", " un-") & "ticked"
    End Sub
    

    Wednesday, May 6, 2015 8:57 AM
    Moderator
  • It looks like that the main code to fix my problem would be using the checkbox 'OnAction' method/property to assign the macro to. Below is what I did in my code:

    .OnAction = "CopyRangeToAnotherSheet"

    Now everytime I add a new row it adds a checkbox form control to the right column and assigns my macro to the checkbox

    Thank you for your help

    If you have any suggestions on VBA training then please don't hesitate to give me suggestions

    Keith


    Keith Aul

    • Marked as answer by L.HlModerator Monday, May 18, 2015 11:44 PM
    Sunday, May 10, 2015 1:59 AM

All replies

  • What sort of checkboxex, Form or ActiveX, and which for what reason
    Tuesday, May 5, 2015 9:30 PM
    Moderator
  • In my VBA code Im adding the checkbox through this code: ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select

    My question then would be 'Is this a Form or and ActiveX checkbox?'

    The purpose of the checkbox - once the checkbox is checked this will take the current row of data and copy it to another sheet in the workbook. After the copying takes place I will then delete the row in the current worksheet.

    Keith


    Keith Aul

    Tuesday, May 5, 2015 9:40 PM
  • That code inserts a "Form Control" checkbox. Look at Developer, Insert

    Adapt the following examples for your needs

    Sub addCheckboxes()
    Dim i As Long, id As Long
    Dim cel As Range
    Dim cbx As CheckBox
    
        For i = 2 To 10 Step 2
            id = id + 1
            Set cel = ActiveSheet.Cells(i, 2)
            With cel
                Set cbx = ActiveSheet.CheckBoxes.Add(.Left, .Top, 90, 3)
    ' height will autosize larger to the font
            End With
            cbx.Name = "Checkbox_" & id
            cbx.Caption = "my Checkbox " & id
            
     ' with a linked can trap sheet change event or link to other formulas
            cbx.LinkedCell = cel.Offset(0, -1).Address
            cbx.OnAction = "'" & ThisWorkbook.Name & "'!CheckBOxHandler"
        Next
    
    End Sub
    
    Sub CheckBOxHandler()
    Dim sCaller As String
    Dim id As Long
    Dim cbx As CheckBox
    Dim shp As Shape
    
        sCaller = Application.Caller
        Set shp = ActiveSheet.Shapes(sCaller)
        Set cbx = ActiveSheet.CheckBoxes(sCaller)
    
        id = Val(Mid$(sCaller, Len("Checkbox_") + 1, 5))
    
        ' maybe something based on Select Case?
        Select Case id
        Case 1:    ' ??
            'etc
        End Select
    
        cbx.TopLeftCell.Offset(, 2).Interior.Color = IIf(cbx.Value = xlOn, vbGreen, vbRed)
        MsgBox cbx.Caption & vbCr & IIf(cbx.Value = xlOn, " ", " un-") & "ticked"
    End Sub
    

    Wednesday, May 6, 2015 8:57 AM
    Moderator
  • I'm currently reviewing the code and trying to understand some of it. Once I get it working on my end I'll let you know. Thank you for the help.

    My question for the above is that since I'm fairly new to writing VBA code, I was wondering if you knew of a resource for me that can help me understand the syntax you wrote above. For instance, any books, video tutorials, VBA online training, etc.


    Keith Aul

    Thursday, May 7, 2015 10:12 PM
  • It looks like that the main code to fix my problem would be using the checkbox 'OnAction' method/property to assign the macro to. Below is what I did in my code:

    .OnAction = "CopyRangeToAnotherSheet"

    Now everytime I add a new row it adds a checkbox form control to the right column and assigns my macro to the checkbox

    Thank you for your help

    If you have any suggestions on VBA training then please don't hesitate to give me suggestions

    Keith


    Keith Aul

    • Marked as answer by L.HlModerator Monday, May 18, 2015 11:44 PM
    Sunday, May 10, 2015 1:59 AM
  • I don't have any recommendations but I imagine a quick search will point you to a wide range of VBA learning resources.

    Quick tip, head all modules Option Explicit and fully declare all variables, step through code (put breaks on suitable places) and look in particular at object variables in Locals (Alt-v, s).

    I take it your original question here is resolved now.

    Sunday, May 10, 2015 5:40 PM
    Moderator