单元格值和复选框更改时更改工作表 [英] Change Worksheet When a Cell Value and a Checkbox Change

问题描述

我有一本包含多个图纸的工作簿.我有一个菜单页(工作表)，其中有多个用户选择(输入新订单，更新订单等).每个选择旁边都有一个复选框，并且取决于选中哪个复选框，单元格 F4:F21 从 0 更改为 1 ，单元格 B1 更改为我要去的工作表的名称.我在主菜单工作表中有以下VBA，但是当我单击一个复选框时，什么也没有发生.有什么想法吗?

CODE

  Private Sub Worksheet_Activate()ClearMenuForm结束子 

---

  Private子Worksheet_Change(按目标的ByVal目标)暗淡如弦如果不相交(目标，范围("F4:F21"))则不成立sh = Cells(1，"B").Value张数(sh).选择万一结束子 

解决方案

单击复选框不会激活事件 Worksheet_Change (请参阅).在下面的示例代码中，您将执行 InitCBs ，并将 CheckBox1Change 与复选框相关联(它实际上为图中的两个复选框分配了动作；复选框2的动作是 CheckBox2Change ).您也可以将 InitCBs 设置为在打开文件时执行.

  Sub CheckBox1Change()MsgBox复选框1b已更改"结束子子InitCBs()昏暗的cb作为复选框对于ActiveSheet.CheckBoxes中的每个cb与cb昏暗的动作作为字符串'action ="CheckboxChange"动作= Replace(cb.Name，"，")&改变".OnAction =动作结束于下一个cb结束子 

I have a Workbook with multiple Sheets. I have a menu page (Worksheet) with multiple user choices (Enter a new order, update an order, etc.) Each choice has a check box beside it and depending on which check box is checked, cells F4:F21 change from 0 to 1 and, cell B1 changes to the name of the Worksheet where I want to go. I have the following VBA in the Main Menu worksheet but when I click a check box, nothing happens. Any ideas why?

CODE

Private Sub Worksheet_Activate()
 ClearMenuForm
End Sub

---

Private Sub Worksheet_Change (ByVal Target As Range)
 Dim sh As String
 If Not Intersect(Target, Range("F4:F21")) Is Nothing Then
 sh = Cells(1, "B").Value
 Sheets(sh).Select
 End If
End Sub 

解决方案

Clicking a check box does not activate the event Worksheet_Change (see this). That is why nothing happens.

Try changing one of the cells instead to see the effect.

What I think you want to do is assign an action to your Checkbox(es). You can do this in two ways:

1. Right clicking on the checkbox, and Assign Macro...

   You have to create the associated macro, which will likely contain parts of the code that you already wrote, and/or calls to subs you have. You may bring the VBE (Alt+F11), insert a module in your VBA project, and write your Sub, e.g.,

   Sub CheckBox1_Click()
       MsgBox "Checkbox 1a has changed"
   End Sub
   

2. Via VBA (e.g., this). With the sample code below, you would execute InitCBs, and that would associate CheckBox1Change with the checkbox (it actually assigns actions for both checkboxes in the figure; action for checkbox 2 is CheckBox2Change). You may also set InitCBs to be executed when opening the file.

   Sub CheckBox1Change()
       MsgBox "Checkbox 1b has changed"
   End Sub
   
   Sub InitCBs()
       Dim cb As CheckBox
       For Each cb In ActiveSheet.CheckBoxes
         With cb
           Dim action As String
           'action = "CheckboxChange"
           action = Replace(cb.Name, " ", "") & "Change"
           .OnAction = action
         End With
       Next cb
   End Sub
   

这篇关于单元格值和复选框更改时更改工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！