mardi 22 décembre 2015

Unchecked checkbox value does not result in zero value

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