Multiple "If" statements in a macro

Dpowell

New Member
Joined
Aug 12, 2003
Messages
35
I have the following code which is intended to check the contents of 4 cells and if any of them is blank it displays an error message which is different depending on which cell is empty.

If all 4 cells are completed then the macro prints the page.

I have never combined more than one if statement and don't know how to, guessing there is some sor t of OR command.

Thanks in advance
David


If ActiveSheet.Range("B8") = "" Then
MsgBox ("Please enter your name")
If ActiveSheet.Range("B9") = "" Then
MsgBox ("Please enter the contract you work on, or enter n/a if central function")
If ActiveSheet.Range("I8") = "" Then
MsgBox ("Please enter your permanent base site")
If ActiveSheet.Range("B8") = "" Then
MsgBox ("Please enter your accounting unit code e.g. 31413260 which is available from your site accountant")
End
Else:
Application.ScreenUpdating = False
Sheets("Claim Form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=2
Application.ScreenUpdating = True
End If
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Re: Multiple "If" statements in a macro, please he

Hi David,

How about:
Code:
Sub Test()
    Dim bEmpty As Boolean
    
    With Sheet1
        bEmpty = .Range("A1").Value = "" Or .Range("A2").Value = "" Or _
            .Range("A3").Value = "" Or .Range("A4").Value = ""
        If bEmpty = True Then
            MsgBox "You have not completed all necessary fields!"
        Else
            MsgBox "OK, proceeding to next bit."
        End If
    End With
    
End Sub
HTH
 
Upvote 0
Re: Multiple "If" statements in a macro, please he

Thanks Richie

Thats halfway there and I've learnt some new code, but how do I get it to display a different error message for each cell that isn't empty?

e.g.
if A1 not empty "Error message 1"
if A2 not empty "Error message 2"
if A3 not empty "Error message 3"
if A4 not empty "Error message 4"

Thanks
David
 
Upvote 0
Re: Multiple "If" statements in a macro, please he

David,

Try this:

Code:
Sub macro1()
If ActiveSheet.Range("B8") = "" Then
    MsgBox ("Please enter your name")
Else
    If ActiveSheet.Range("B9") = "" Then
    MsgBox ("Please enter the contract you work on, or enter n/a if central function")
    Else
        If ActiveSheet.Range("I8") = "" Then
        MsgBox ("Please enter your permanent base site")
        Else
            If ActiveSheet.Range("B8") = "" Then
            MsgBox ("Please enter your accounting unit code e.g. 31413260 which is available from your site accountant")
            Else
            Application.ScreenUpdating = False
            Sheets("Claim Form").Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=2
            Application.ScreenUpdating = True
            End If
        End If
    End If
End If
End Sub

I did notice that you have the same range ("B8") listed twice.
 
Upvote 0
Re: Multiple "If" statements in a macro, please he

Thanks

That works perfectly.

and it was as simple as adding an "Else" after each if.

Thanks again
David
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top