如何将属性分配给复选框 [英] how to assign the property to checkbox

问题描述

我有一个复选框和代码，如果我运行它显示错误。请修正此错误。

  Private Sub CheckBox1_Click（）
带表格（bom）
如果.CheckBox1.Value = True然后
 .Range（show_all_level）=是
 Else 
 .Range（show_all_level）=否
如果
结束
 End Sub 
  

错误类型：

解决方案

它应该处理与show_all_level名称范围的不同情况。

  Option Explicit 
 
 Private Sub CheckBox1_Click（）
 
 Dim Nm As Name 
 Dim NmExist As Boolean 
 Dim NameRng As Range 
 
'循环通过ThisWorkbook中的所有名称范围
对于每个Nm在ThisWorkbook .Names 
如果Nm.Parent.Name像bom那么'<  - 检查名称范围父（Sheet.Name）是否为bom
 MsgBox Nm.Name& 名称范围存在是表& Chr（34）& Nm.Parent.Name& Chr（34）
 NmExist = True'raise the flag>>名称存在于bom表
 Set NameRng = Nm.RefersToRange'将范围设置为名称范围
退出
 ElseIf Nm.Parent.CodeName像ThisWorkbook然后'<  -  - 检查名称范围是否为ThisWorkbook
 MsgBox Nm.Name& 名称范围作为WorkBook范围存在
 NmExist = True'提高标志>>名称存在于工作簿范围
 Set NameRng = Nm.RefersToRange'将范围设置为名称范围
退出
结束如果
下一个Nm 
 
'验证bom表（或Workbook范围）中是否存在show_all_level名称
如果不是NmExist然后
 MsgBox Chr（34）& show_all_level& Chr（34）& 名称范围，不存在于所需的表格，vbCritical 
退出Sub 
结束If 
 
带表单（bom）
如果.CheckBox1。 Value = True Then 
 NameRng.Value =Yes
 Else 
 NameRng.Value =No
 End If 
 End With 
 
 End Sub 
  

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:

解决方案

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

这篇关于如何将属性分配给复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！