链接单元格选项按钮更改时的操作 [英] Action when linked cell of option button changes

问题描述

我的选项按钮链接的单元格有问题。每当链接的单元格的值更改时，我想加载一个宏。
我尝试了两种不同的方法，当按钮更改值时，它们都不会工作。如果我在选定的单元格中输入一个值，我的宏将加载。

I have a problem with my option buttons linked cells. I'd like to load a macro whenever the value of the linked cell changes. I tried two different methods, and none of them won't work when the button changes the value. If I type in a selected cell a value, my macros do load.

这是我的两种技巧：

Private Cel_CONGESg As Byte
Private Sub Worksheet_Calculate()
Dim Ws As Worksheet

Set Ws = ThisWorkbook.Sheets("Externe")

If Ws.Range("$I$12").Value <> Cel_CONGESg Then
    MsgBox "heheheheeheheheheee"
End If

Cel_CONGESg = Ws.Range("$I$12").Value

End Sub

第二种方法

Private Sub Worksheet_Change(ByVal target As Range)
Dim CongesG
Dim Ws As Worksheet

Set CongesG = Ws.Range("$I$12")

If Not Application.Intersect(CongesG, Range(target.Address)) _
       Is Nothing Then
    If Ws.Range("$I$12").Value = 2 Or Ws.Range("$I$12").Value = 0 Then
        Ws.Range("$I$13").EntireRow.Hidden = True
            With Range("H12:L12").Borders(xlEdgeBottom)
                .LineStyle = xlDot
                .Color = RGB(51, 63, 79)
                .Weight = xlThin
            End With
    ElseIf Ws.Range("$I$12").Value = 1 Then
        Ws.Range("$I$13").EntireRow.Hidden = False
            With Range("H12:L12").Borders(xlEdgeBottom)
                .LineStyle = xlNone
            End With
    End If 'I12 Congés
End If 'Application intersect CongesG
End Sub

你能帮我看看有什么问题吗？

Could you please help me understand what's the problem?

提前谢谢。有一个美好的一天。

Thank you in advance. Have a great day.

Jean

推荐答案

您没有使用 Worksheet_Change 事件的完整功能， code>目标，已经定义为范围。

You are not using the full capabilities of the Worksheet_Change event, and Target, which is already defined as Range.

尝试以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CongesG As Range

Set CongesG = Range("$I$12")

If Not Application.Intersect(CongesG, Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Value
        Case 2, 0
            Target.Offset(1).EntireRow.Hidden = True
            With Range("H12:L12").Borders(xlEdgeBottom)
                .LineStyle = xlDot
                .Color = RGB(51, 63, 79)
                .Weight = xlThin
            End With
        Case 1
            Target.Offset(1).EntireRow.Hidden = False
            With Range("H12:L12").Borders(xlEdgeBottom)
                .LineStyle = xlNone
            End With
    End Select
End If 'Application intersect CongesG
Application.EnableEvents = True

End Sub

这篇关于链接单元格选项按钮更改时的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！