Multiple ifs in a single Worksheet_Calculate()

furant08 · Jun 2nd 2021

Hello. Im a beginner in vba and trying to keep a button from being enabled based on 3 criteria and at the same time clear the contents of my range when a criterion is met.
Thank you.
This is my code:

Code

Private Sub Worksheet_Calculate()


On Error GoTo ErrorHandler
If [Y3] = 0 Then
SaveORec.Enabled = False
Else: SaveORec.Enabled = True  '<---------IT WORKS UP TO HERE


'Nothing works from here-->


If ActiveWorkbook.Worksheets("APD ORDER").Range("F3") = "" Or ActiveWorkbook.Worksheets("APD ORDER").Range("AB10") < 2.5 Then
SaveORec.Enabled = True


Else: SaveORec.Enabled = False


If [AG25] < 1.5 Then
ThisWorkbook.Worksheets("APD ORDERS").Range("$AB$25:$AG$25").Clearcontets


End If
End If
End If


ErrorHandler:
End Sub

Display More

**royUK** · Jun 2nd 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jun 2nd 2021

1. Don't use error handlers until you know the code is working. If you do you are suppressing messages that might help understand the problem.

2. Which sheet is the code in?

3. There's a typo - Clearcontets

Try this, if it doesn't work attach an example of the workbook.

Code

Private Sub Worksheet_Calculate()
    
    'On Error GoTo ErrorHandler
    
    Select Case True
        Case [Y3] <> 0, ActiveWorkbook.Worksheets("APD ORDER").Range("F3"), ActiveWorkbook.Worksheets("APD ORDER").Range("AB10") < 2.5
            SaveORec.Enabled = True
        Case Else: SaveORec.Enabled = False
    End Select
    
    If [AG25] < 1.5 Then ThisWorkbook.Worksheets("APD ORDERS").Range("$AB$25:$AG$25").ClearContents
    
ErrorHandler:
End Sub

Display More

furant08 · Jun 2nd 2021

Thank you so much Roy! Sorry I didn't know how to use the <> thingy..

The case part works pretty amazing!

The IF part on the other hand keeps having errors. Maybe because the range that i have there has dynamic dropdown validation?

i got a named range with this content and i use the name for my validation dropdown on the range $AB$25:$AG$25

Code

=IF('APD ORDER'!$AB$10>1.5,OFFSET('APD ORDER'!$CT$8,0,0,COUNTA('APD ORDER'!$CT$8:$CT$13)))

sorry i dont know if im supposed to <> this or not

**royUK** · Jun 2nd 2021

Attach an example workbook

Multiple ifs in a single Worksheet_Calculate()

Participate now!

Share

Tags