how to assign the property to checkbox

Question

hi i have a checkbox and code if i run it shows error. please rectify this error.

Private Sub CheckBox1_Click()
    With Sheets("bom")
        If .CheckBox1.Value = True Then
            .Range("show_all_level") = "Yes"
        Else
            .Range("show_all_level") = "No"
        End If
    End With
End Sub

type of error:

You probably don't have a defined range called "show_all_level" . Check in your Formula tab>Name Manager and see if you have a defined range called "show_all_level" ? — pokemon_Man
– pokemon_Man, Commented Apr 11, 2017 at 19:03
Is the defined named range show_all_level on the same worksheet as the checkbox? i.e. Worksheets("bom") — user4039065
– user4039065, Commented Apr 11, 2017 at 19:21

Shai Rado · Accepted Answer · 2017-04-12 07:57:21Z

1

Try the code below, it should handle different scenarios you might have with your "show_all_level" Name Range.

Option Explicit

Private Sub CheckBox1_Click()

Dim Nm          As Name
Dim NmExist     As Boolean
Dim NameRng     As Range

' loop through all Name Ranges in ThisWorkbook
For Each Nm In ThisWorkbook.Names
    If Nm.Parent.Name Like "bom" Then '<-- check if name range parent (Sheet.Name) is "bom"
        MsgBox Nm.Name & " Name Range exists is sheet " & Chr(34) & Nm.Parent.Name & Chr(34)
        NmExist = True ' raise the flag >> Name exist in "bom" sheet
        Set NameRng = Nm.RefersToRange ' set the Range to the Name Range
        Exit For
    ElseIf Nm.Parent.CodeName Like "ThisWorkbook" Then '<-- check if name scope is "ThisWorkbook"
        MsgBox Nm.Name & " Name Range exists as WorkBook scope"
        NmExist = True ' raise the flag >> Name exist in Workbook scope
        Set NameRng = Nm.RefersToRange ' set the Range to the Name Range
        Exit For
    End If
Next Nm

' verify that "show_all_level" name exist in "bom" sheet (or Workbook scope)
If Not NmExist Then
    MsgBox Chr(34) & "show_all_level" & Chr(34) & "Name Range, doesn't exist in the desired sheet", vbCritical
    Exit Sub
End If

With Sheets("bom")
    If .CheckBox1.Value = True Then
        NameRng.Value = "Yes"
    Else
        NameRng.Value = "No"
    End If
End With

End Sub

answered Apr 12, 2017 at 7:57

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

0m3r Over a year ago

@DINESHKUMARPALANISAMY see meta.stackexchange.com/a/5235/289619

Shai Rado Over a year ago

@0m3r thanks for this link ;) I have needed it in the past so many times ;)

Collectives™ on Stack Overflow

how to assign the property to checkbox

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related