Adding event code to checkbox in PowerPoint VBA

Question

I am developing a PowerPoint 2010 deck that presents the user with a series of pages containing one statement, one checkbox (built from a label element to enable changing the size of the checkbox) and forward/back arrows on each page.

Since this will be used on numerous projects with varying numbers of pages I am building the “deck” dynamically using PowerPoint VBA to construct the pages dynamically from an Excel spreadsheet containing the list of individual statements.

I have been able to write the VBA code to open the Excel file, read the statements into an array in PowerPoint and construct the appropriate number of pages with all of the elements on the page. To this point everything works fine. Where I am having difficulty is in assigning the click action to the checkbox.

Here is the code that is called by the page building routine to insert the checkbox (obviously there is more code prior to this for accessing the Excel file, creating the pages and adding the “statement” text boxes...all of which works):

Sub AddSelectBox(Index As Integer, pptBuildingSlide As Slide)

'Add Checkbox
    With pptBuildingSlide.Shapes.AddOLEObject(Left:=342, Top:=294, Width:=42, Height:=42, ClassName:="Forms.Label.1")
        .Name = "label" & Index
        .OLEFormat.Object.Font.Name = "Wingdings 2"
        .OLEFormat.Object.Font.Charset = "2"
        .OLEFormat.Object.Caption = "£"
        .OLEFormat.Object.Font.Size = 40
    End With

'Add Checkbox Click Code
'(CODE FOR ADDING CLICK EVENT TO EACH BOX GOES HERE)

End Sub

The checkbox on each page has a discreet name keyed to the page number (e.g. Label1, Label2, etc.). I need to add the following code to each checkbox on each page to toggle the checkmark so later in the program I can see which were checked by reading the “caption” attributes. (The font is set to “Wingdings 2” to give a blank box and a checked box on click)

Private Sub Label1_Click()

   If Label1.Caption = "£" Then
       Label1.Caption = "R"
   Else
       Label1.Caption = "£"
   End If

End Sub

I have searched the web looking for any references to add event code dynamically and found a number of examples (e.g. Assign on-click VBA function to a dynamically created button on Excel Userform) but almost all are for Excel or Access. I should point out that coding is “not my day job” and I have managed to get this far reading “Mastering VBA for Office 2003” and web searching…so my ability to translate those examples to PowerPoint has come up short. Thanks for any help you can offer.

5/29 Additional information:
I came across the .CreateEventProc method as a way to write code into VBA. The example I found was written for Excel at this site. I've gotten this far with it (the message box code would be replaced with the click code but I was just using this for testing to avoid introducing other errors)...

Sub CreateEventProcedure()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    Const DQUOTE = """" ' one " character

    Set VBProj = ActivePresentation.VBProject
    Set VBComp = VBProj.VBComponents(Slides(1))
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        LineNum = .CreateEventProc("Click", "Label1")
        LineNum = LineNum + 1
        .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
    End With
End Sub

...but get a "Compile Error: Sub or Function not defined" at (slides(1)). Any help cleaning it up (if it is in fact an appropriate solution) would be appreciated.

I think you need to change ` ClassName:="Forms.Label.1"` to ` ClassName:="Forms.CheckBox.1"` when you are creating the shape. If you were using an ordinary shape you could attach the name of a macro to the `OnAction` property. You would add the macro to the deck at design time and set the `OnAction` string to something like `.OnAction="NameOfDeck!NameOfMacro"`. — ChipsLetten, May 26 '15 at 21:00
Thanks. I am using Label.1 instead of CheckBox.1 because I need the checkbox to be larger than a standard CheckBox object which is not resizable. I can get the box to toggle by entering the `Label1_Click( )` code manually to the Label on each page after they are built by the VBA but cannot figure out how to have VBA add the code programmatically on the fly. Might the `OnAction` method you describe be worth pursuing for this approach? — agoltz, May 27 '15 at 03:18

score 0 · Accepted Answer · answered May 27 '15 at 16:11

0

Do you have to use a label? (I understand the size thing but you can maybe add a shape which would be easier.)

Something based on: This can only work if you allow access to the VBE in Security (cannot be done in code)

    Sub makeBox()
Dim strCode As String
With ActivePresentation.Slides(1).Shapes.AddShape(msoShapeRectangle, 10, 10, 20, 20)
.Fill.Visible = False
.Line.Visible = False
With .TextFrame.TextRange.Font
.Name = "Wingdings 2"
.Size = 40
.Color.RGB = vbBlack
End With
.TextFrame.TextRange = "£"
With .ActionSettings(ppMouseClick)
.Action = ppActionRunMacro
.Run = "chex"
End With
End With
strCode = "Sub chex(oshp As Shape)" & vbCrLf & "If oshp.TextFrame.TextRange =" & Chr(34) & "£" & Chr(34) & "Then" _
& vbCrLf & "oshp.TextFrame.TextRange = " & Chr(34) & "R" & Chr(34) & vbCrLf _
& "Else" & vbCrLf & "oshp.TextFrame.TextRange =" & Chr(34) & "£" & Chr(34) & vbCrLf & "End If" & vbCrLf & "End Sub"
With ActivePresentation.VBProject.VBComponents.Add(vbext_ct_StdModule)
.CodeModule.AddFromString (strCode)
End With
End Sub

answered May 27 '15 at 16:11

John Wilson

26
1

Thanks John. Ran the code in a clean presentation as it did not seem to depend on any of my code. Got a "Compile error: Variable not defined on `.Add(vbext_ct_StdModule)`. Commented out the final "With..." section of code and the code runs, draws the box but no onClick action (probably commented out the magic.) – agoltz May 28 '15 at 14:07
A simple google search shows that you need to add a reference to Microsoft Visual Basic for Applications Extensibility 5.3. Taken from [http://www.cpearson.com/excel/vbe.aspx](http://www.cpearson.com/excel/vbe.aspx): The VBIDE is the object library that defines all the objects and values that make up VBProject and the Visual Basic Editor. To add this reference, open the VBA editor, open your VBProject in the editor, and go to the Tools menu. There, choose References . In the References dialog, scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 and check that item. – ChipsLetten May 28 '15 at 20:59
chipsLetten - Discovered the reference last night while researching .CreateEventProc. John's code now works. – agoltz May 29 '15 at 15:00
@John Wilson Thanks! I would vote your answer up but don't have enough points. Vote yourself up for me! If you get a second could you look at the additional information I've added to the OP. If you have any experience with .CreateEventProc it would be much appreciated. – agoltz May 29 '15 at 15:32

Adding event code to checkbox in PowerPoint VBA

1 Answers1