CODEWITHSUNDEEP
excelvbacheckbox
user2453446
user2453446

Reputation: 303

how to link a checkbox on Excel to a VBA code

I have created a checkbox on my Excel Work sheet, using design mode I have leftclicked it and named it ChkV, and I wrote a VBA code but when I run it I get an message telling that the variable is not defined. 

    If ChkV.Value = True Then
        ' my code
    End If

Did I not label the check box correctly, what am I doing wrong ? How should I fix the mistake?

Upvotes: 0

Views: 18702

Answers (3)

Rafa Barragan
Rafa Barragan

Reputation: 610

Hi i was breaking my head over and over again for this, but searching i found that you need to refer by the CodeName's sheet or using OleObjects

  1. By Code Name is how you see in the VBA Project tree: 

    Hoja1.[CheckBoxName].Value  
'' In English or what ever you may call your sheet is:
Sheet1.[CheckBoxName].Value

  2. By OleObjects is: 

    Dim Wks as Worksheet  
Set Wks = Worksheets("[SheetName]")    '' in this case "Prueba"
Wks.OLEObjects("[CheckBoxName]").Object.Value

Note that my Excel is in Spanish thats why you see Hoja1, in English is Sheet1 and you can find something else here.

VBAProject tree

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

You have this error when you call your code outside Sheet module where your checkbox is located. To improve your code you need to add references to sheet where checkbox belongs to, like:

If Sheets("Sheet 1").ChkV.Value = True Then
    ' my code
End If

Upvotes: 1

Pawzik
Pawzik

Reputation: 78

Should it not be 

If  activesheet.Checkboxes("ChkV") = xlOn Then
'your code
End If

?

Upvotes: 3

Related Questions