Change Worksheet When a Cell Value and a Checkbox

Question

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 

Answer 1

You've defined sh as a String. Since there is no sheet named "1", for example, your code will generate a "Subscript out of Range" runtime error. Try changing sh to a Long.

Dim sh As Long

Answer 2

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