MACRO message box error

[SelinaR]

Morning

I use the below Macro for an annual leave tracker.... if it sees 0 it gives a pop up message, however it seems to have an issue that it sometimes give an error when the minimum number is 5 or 3 and on other rows if it's 0 (correctly)

I have tried tweaking it but have had no success - any suggestions?


Sub ANL()
Dim range
Dim nResult As Long
range = ActiveCell.Row
If (Cells(range, 3) <= 0) Then
nResult = MsgBox( _
Prompt:="Leave Unavailable. Do you still want to continue?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
Else
Selection.FormulaR1C1 = "ANL"
Selection.Font.ColorIndex = 0
Selection.Font.Name = "Arial Rounded MT Bold"
End If
Else
Selection.FormulaR1C1 = "ANL"
Selection.Font.ColorIndex = 0
Selection.Font.Name = "Arial Rounded MT Bold"
End If
End Sub


THANKS

 

[dmt32]

hi,
see if this change to your code helps:

Sub ANL()
    Dim Result As Long


    If Cells(ActiveCell.Row, 3).Value <= 0 Then
        Result = MsgBox("Leave Unavailable. Do you still want to continue?", 36, "Leave Unavailable")
        If Result = vbNo Then Exit Sub
    End If


    With ActiveCell
        .FormulaR1C1 = "ANL"
        .Font.ColorIndex = 0
        .Font.Name = "Arial Rounded MT Bold"
    End With


End Sub

Dave

 

[SelinaR]

Hiya

that worked - thanks however - the spreadsheet has now been reformatted and coloumns removed...

how ill I know how to identify which cell the "if" formula is working off of to gain my YES/NO prompt:

currently shows:
If (Cells(range, 12) <= 0) Then...

Is 12 referring to a column? My data it needs to read to total prompt from is coloumn or is if from

or is it reading from Selection.FormulaR1C1 = "ANL"

thanks

 

[Michael M]

Yes, this is Column 12
Cells(range, 12)

This, should actually be..

Selection.Value = "ANL"