In Excel 2010, VBA, when the checkbox is unchecked, the code should not run. However it is running when the checkbox is unchecked. What am I missing?
EDIT: The unchecked checkbox results in "-4146" in the Immediate window. So I changed the IF
condition to <> 1
. Isn't an unchecked checkbox supposed to be equal to zero?
EDIT 2: I forgot to mention, this is a FORM CONTROL checkbox.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'delete range on the active row from column I to column M and shift up
'I=9, M = 13
Dim delAnswer As VbMsgBoxResult
Const WS_Range As String = "I:M"
'Checks to see if user wants macro turned on
If ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 0 Then Exit Sub
On Error GoTo ws_exit
Application.EnableEvents = False
'Check to see if user is in applicable range for macro to run
If Not Intersect(ActiveCell, Me.Range(WS_Range)) Is Nothing Then
'Ask user to confirm running macro
With Target
aR = ActiveCell.Row
delAnswer = MsgBox("Delete I" & aR & ":M" & aR & "?", vbYesNo)
'Macro - deletes range of cells on the active row
If delAnswer = vbYes Then
Range("I" & ActiveCell.Row).Resize(, 5).Delete Shift:=xlUp
Else: GoTo ws_exit
End If
End With
End If
ws_exit:
Application.EnableEvents = True
'Debug.Print Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
'Debug.Print "ar is " & aR
End Sub
Aucun commentaire:
Enregistrer un commentaire