VBA code help needed- if all other code is ok display

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
137
Hi,

I've got a lot of code on a VBA button currently but if all conditions are satisfied (true) the message box will appear blank, is there a way to say if all conditions are satisfied/true display a message box saying "no issues"

just FYI theres about 45/50 conditions in my form
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
RC i don't want the message to be displayed if the other conditions are not satisfied sorry forget to say that. So if all other parts of my code are true i wont it to say "no issues" otherwise i don't want it to display if one or more are false
 
Last edited:
Upvote 0
RC i don't want the message to be displayed if the other conditions are not satisfied sorry forget to say that. So if all other parts of my code are true i wont it to say "no issues" otherwise i don't want it to display if one or more are false

ill need to see your whole code or you can try and figure it out with the advice below.


What I would do is create a variant variable. set it to zero then each time there is a false increment it up.

then wrap the message box with an if statement that checks if that variable is zero.

Code:
dim varI as variant

vari=0

IF the moon is made of cheese then
*do your next line of code
else
vari=vari+1
end if

if vari=0 then
msgbox "No Issues"
end if
 
Last edited:
Upvote 0
Hi, some of the code is below. i have far to many lines to be able to to send it all and i cannot share the document due to the nature of it.

If IsEmpty(Range("L12").Value) = True Then
Dim yourFinalMessage As String
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 12 - Please enter the ID of the person completing this form"
End If

If IsEmpty(Range("L20").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 20 - Please enter the Number for this request"
End If

If IsEmpty(Range("L28").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 28 - Please enter the contact details for this request"
End If

Hope that can help you help me as the bit above doesn't really make much sense to me. also i didn't include the final end string line but the code above does work
 
Upvote 0
Hi, some of the code is below. i have far to many lines to be able to to send it all and i cannot share the document due to the nature of it.

If IsEmpty(Range("L12").Value) = True Then
Dim yourFinalMessage As String
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 12 - Please enter the ID of the person completing this form"
End If

If IsEmpty(Range("L20").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 20 - Please enter the Number for this request"
End If

If IsEmpty(Range("L28").Value) = True Then
yourFinalMessage = yourFinalMessage & vbCrLf & _
"Row 28 - Please enter the contact details for this request"
End If

Hope that can help you help me as the bit above doesn't really make much sense to me. also i didn't include the final end string line but the code above does work

Please use code tags when posting code. It makes it easier to read and saves space on the page.

code tags

open square bracket "[" the word "code" close square bracket "]"
so (without the spaces)

[ code ]

then

open square bracket "[" the word "/code" close square bracket "]"
so (without the spaces)

[ /code ]

paste code between those two lines (ends up looking like the following)

Code:
If IsEmpty(Range("L12").Value) = True Then
 Dim yourFinalMessage As String
 yourFinalMessage = yourFinalMessage & vbCrLf & _
 "Row 12 - Please enter the ID of the person completing this form"
end If

 If IsEmpty(Range("L20").Value) = True Then
 yourFinalMessage = yourFinalMessage & vbCrLf & _
 "Row 20 - Please enter the Number for this request"
 End If

 If IsEmpty(Range("L28").Value) = True Then
 yourFinalMessage = yourFinalMessage & vbCrLf & _
 "Row 28 - Please enter the contact details for this request"
 End If

Also only dim at the start of the procedure. Makes it easier to find them all and just looks cleaner.

since you are looking at each instances in the L column for blanks, it might be easier if you used a loop and/or a select case. This will shorten your code (make it easier to debug and read) and make it faster. I understand that you can not share the document, but perhaps some questions.

Your first IF statement check cell L12 and obviously is for the ID of the person completing the form. Is there a row designator that states ID. So how would someone unfamiliar with your spreadsheet know that row 12 is for Person's ID, row 20 is for the number, row 28 is for contact details.

Are all those designators in a specific column? the same column?

If so you can do a loop would take a the row number and then a select case that checks the row number and determines the msgbox.

so if all your data starts in row 2 of column L then you could do something like the following

Code:
Sub PGD15()
Dim lngROW As Long, lngCELL As Long
Dim rng As Range, cell As Range
Dim ws As Worksheet
Dim yourFinalMessage As String
Dim varI As Variant

Set ws = ActiveSheet
Set yourfinalmeassage = "my message"
With ws
lngROW = ws.Range("L" & ws.Rows.Count).End(xlUp).Row

Set rng = ws.Range("L2:L" & lngROW)
varI = 0
For Each cell In rng
    If isemty(cell.Value) = True Then
        lngCELL = cell.Row
        varI = varI + 1
        Select Case lngCELL
            Case 12
                yourFinalMessage = yourFinalMessage & vbCrLf & _
                    "Row 12 - Please enter the ID of the person completing " _
                    & "this form"
            Case 20
                yourFinalMessage = yourFinalMessage & vbCrLf & _
                    "Row 20 - Please enter the Number for this request"
            Case 28
                yourFinalMessage = yourFinalMessage & vbCrLf & _
                    "Row 28 - Please enter the contact details for this request"
        End Select
    End If
Next cell
    If varI = 0 Then
        MsgBox = "No Issues"
    Else
        MsgBox = yourFinalMessage
    End If
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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