使vba代码适用于所有框 [英] Make vba code work for all boxes

问题描述

你好，所以我想做的是使这个代码适用于所有复选框的1-50我想要的代码只影响被点击的框。

  Private Sub CheckBox1_Click（）
如果MsgBox（你要锁定这个框吗，vbYesNoWarning）= vbYes然后
 ActiveSheet.CheckBox2.Enabled = False 
 Else 
 End If 
 End Sub 
  

解决方案

简单的方法是编写一个类模块，将将一个代码例程应用于复选框

假设你想在 ActiveSheet 上的所有ActiveX复选框上运行它，然后大量借用Bob Phillip的 VBAX的代码

1. 插入一个名为的类模块 clsActiveXEvents / p>
   
   

   Option Explicit

     Publ ic WithEvents mCheckboxes As MSForms.CheckBox 
    
    Private Sub mCheckboxes_Click（）
    mCheckboxes.Enabled =（MsgBox（你要锁定这个框吗，vbYesNo，Warning）= vbNo ）
    End Sub 
     




2. 在普通模块使用此代码

     Dim mcolEvents As Collection 
    
    Sub Test（）
    Dim cCBEvents As clsActiveXEvents 
    Dim shp As Shape 
    
   设置mcolEvents =新集合
    
   对于每个shp在ActiveSheet.Shapes 
   如果shp.Type = msoOLEControlObject Then 
   如果TypeName（shp.OLEFormat.Object.Object）=CheckBox然后
   设置cCBEvents =新的clsActiveXEvents 
   设置cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object 
    mcolEvents.Add cCBEvents 
    End If 
    End If 
    Next 
    End Sub 
     

Hello so what i want to do is make this code work for all Check Box's 1-50 I want the code to only effect the box that is clicked.

Private Sub CheckBox1_Click()
If MsgBox("Do you want to lock this box?", vbYesNo, "Warning") = vbYes Then
  ActiveSheet.CheckBox2.Enabled = False
Else
End If
End Sub

解决方案

The easy way is to write a class module that will apply one code routine to a collection of Checkboxes

Assuming yu want to run this on all ActiveX checkboxes on the ActiveSheet, then borrowing heavily from Bob Phillip's code from VBAX

1. Insert a Class Module named clsActiveXEvents

   Option Explicit

   Public WithEvents mCheckboxes As MSForms.CheckBox
   
   Private Sub mCheckboxes_Click()
     mCheckboxes.Enabled = (MsgBox("Do you want to lock this box?", vbYesNo, "Warning") = vbNo)
   End Sub
   

2. In a normal module use this code

   Dim mcolEvents As Collection
   
   Sub Test()
   Dim cCBEvents As clsActiveXEvents
   Dim shp As Shape
   
   Set mcolEvents = New Collection
   
   For Each shp In ActiveSheet.Shapes
       If shp.Type = msoOLEControlObject Then
           If TypeName(shp.OLEFormat.Object.Object) = "CheckBox" Then
              Set cCBEvents = New clsActiveXEvents
              Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object
           mcolEvents.Add cCBEvents
       End If
   End If
   Next
   End Sub
   

这篇关于使vba代码适用于所有框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！