单元格值和复选框更改时更改工作表 [英] 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:
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
Via VBA (e.g., this). With the sample code below, you would execute
InitCBs
, and that would associateCheckBox1Change
with the checkbox (it actually assigns actions for both checkboxes in the figure; action for checkbox 2 isCheckBox2Change
). You may also setInitCBs
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屋!