Toggle checkbox values in vba

[melodramatic]

I'm running a macro that pulls up a file template, then asks you a series of questions to get the file set for who and what it needs to get the data.

The problem I'm having is in having it check the appropriate checkboxes. I've never done this before, and somewhere I'm screwing up.

If you answer the questions "No", then no problem - it bypasses and goes to the next question. So obviously my error is in the action line. I have opened the properties box and doublecheck that my checkboxes are named CheckBox1, CheckBox2, CheckBox3, CheckBox4 & CheckBox5. Their default setting in the template is false (unchecked).

Here's what I've got...

            If MsgBox("Was RAW DATA delivered to the Client?", vbYesNo) = vbYes Then
                CheckBox1.Value = True
            End If
            If MsgBox("Were PHOTOS delivered to the Client?", vbYesNo) = vbYes Then
                CheckBox2.Value = True
            End If
            If MsgBox("Were TEST LOGS delivered to the Client?", vbYesNo) = vbYes Then
                CheckBox3.Value = True
            End If
            If MsgBox("Was an EMAIL SUMMARY delivered to the Client?", vbYesNo) = vbYes Then
                CheckBox4.Value = True
            End If
            If MsgBox("Was any other type documentation delivered to the Client?", vbYesNo) = vbYes Then
                CheckBox5.Value = True
                OtherDesc = InputBox("Please list other documentation provided to Client:", "Other Documentation Provided")
                Range("G22").Select
                ActiveCell.FormulaR1C1 = OtherDesc
            End If

Help?

 

[Scott T]

Try change sheet1 to fit your data

Sheet1.CheckBoxes("Check Box 1").Value = True

 

[melodramatic]

Still getting an error. My sheet name is "Waive" - does that make a difference in the coding?

 

[RedBeard]

The msgbox needs a variable to be checked against, the above will always return false.

Try:

Dim answer As Integer

answer = MsgBox("Question", vbYesNo)


If answer = vbYes Then blahblahblah

 

[Scott T]

In the VBA editor your will see something like

VBAProject (workbookname)
microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Waive)
Sheet4(Sheet4)

even though Sheet3 is name Waive I use the below code to change the check box on that sheet.

Sheet3.CheckBoxes("Check Box 1").Value = True

Use the Sheet# not the name in the parentheses as listed in the VBA Project.

 

[melodramatic]

Awesome - it's working!!! Thank you so much, Scott

 

[Scott T]

Glad to help.