当单元格值和复选框更改时更改工作表 [英] Change Worksheet When a Cell Value and a Checkbox Change
问题描述
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屋!