Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - 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 
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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...

    enter image description here

    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
    

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...