Stop a MsgBox when If Statement is true

A

arl5334

New Member
Joined
Nov 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking to stop a MsgBox from appearing more than one time, once the if statement is true. Currently I have If 3 cells are filled in, a message box appears with instructions on what to do next - however, if the user clicks on other cells within the spreadsheet the message box keeps appearing with every cell the user clicks on, because the 3 cells in the If statement are still true.
How would I get the MsgBox to only appear that one time when all 3 specific cells are filled in and not appear when the user filled in those 3 cells and is now clicking around and filling in other cells?

Here is the code that I have. I originally had it under Private Sub Worksheet_SelectionChange(ByVal Target As Range) but then moved it to its own sub.

Sub tMsgBox()
Dim Answer As VbMsgBoxResult

If (Range("A9") = "Crime Victims Bank Account") And (Range("G18") = "Yes") And IsEmpty(Range("J18").Value) = False Then
Sheets("CVCP Pick-Up Form").Visible = True

Answer = MsgBox("You have indicated that you have checks being held for pick-up. Please select the tab, CVCP Pick-Up Form, at the bottom to complete and print the CVCP Check Pick-Up Form." & VBA.Constants.vbNewLine & _
VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
"Would you like to proceed directly to the CVCP Check Pick-Up Form?", vbYesNo, "Check(s) Held for Pick-Up")
End If

If Answer = vbYes Then
MsgBox "Yay!" 'This will be changed to take the user to a other tab/sheet automatically that is now visible for them to complete the form.
Else
Exit Sub 'This would be to keep them on the current sheet/form
End If

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi welcome to forum

one idea maybe would be to test for visibility of the worksheet "CVCP Pick-Up Form" & only display the msgbox if it is hidden. If user selects vbYes only then make it visible to user.

some like following

VBA Code: 
Sub tMsgBox()
    Dim Answer      As VbMsgBoxResult
    
    If (Range("A9") = "Crime Victims Bank Account") And _
       (Range("G18") = "Yes") And _
       IsEmpty(Range("J18").Value) = False Then
    
    With Sheets("CVCP Pick-Up Form")
        
        If Not .Visible Then
            
            Answer = MsgBox("You have indicated that you have checks being held For pick-up." & Chr(10) & _
                     "Please Select the tab, CVCP Pick-Up Form, at the bottom To complete And print the CVCP Check Pick-Up Form." & _
                     VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
                     "Would you Like To proceed directly To the CVCP Check Pick-Up Form?", vbYesNo, "Check(s) Held For Pick-Up")
            
            If Answer = vbYes Then
                
                .Visible = xlSheetVisible: .Select
                MsgBox "Yay!"
                'This will be changed to take the user to a other tab/sheet automatically
                'that is now visible for them to complete the form.
                
            Else
                .Visible = xlSheetHidden
                'This would be to keep them on the current sheet/form
                Exit Sub
            End If
        End If
    End With
End If

End Sub

Dave
 
Upvote 0
Thank you Dave for the insight, however, the issue I am seeing now is that if the user selects no, because they need to still fill in A30 and D2 (as an example) - the other tab is now hidden. A9, G18, and J18 are still filled in and remain True. How would the other tab become available?
Or would I just need to remove the .Visible = xlSheetHidden under Else?

I do not want to add additional buttons to the sheet.

This is also not the only msgbox I have, but I figured if I could get one to work the way I need it to, I can apply the same concept to the others.

All msgboxes are more so instructions only. I only recently added the yes/no option figuring it might save the user a tiny amount of time.

Thank you again!
 
Upvote 0
How about using Worksheet_Change event:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Not Intersect(Target, Union(Range("A9"), Range("G18"), Range("J18"))) Is Nothing Then
    
                Dim Answer As VbMsgBoxResult

                If (Range("A9") = "Crime Victims Bank Account") And (Range("G18") = "Yes") And IsEmpty(Range("J18").Value) = False Then
                    
                    Sheets("CVCP Pick-Up Form").Visible = True
                    Answer = MsgBox("You have indicated that you have checks being held for pick-up. Please select the tab, CVCP Pick-Up Form, at the bottom to complete and print the CVCP Check Pick-Up Form." & VBA.Constants.vbNewLine & _
                    VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
                    "Would you like to proceed directly to the CVCP Check Pick-Up Form?", vbYesNo, "Check(s) Held for Pick-Up")
               
                        If Answer = vbYes Then MsgBox "Yay!"
                    
                Else
                   If Sheets("CVCP Pick-Up Form").Visible = True Then Sheets("CVCP Pick-Up Form").Visible = xlSheetHidden
                End If

    
    End If

End Sub

it will be triggered when you change Range("A9"), Range("G18"), Range("J18") content.
 
Upvote 0
Hi,
the suggestion was simply based on what your code was doing.

After the If statement, you made the worksheet visible if the line returned True so I assumed that you had it hidden & only made it visible if vbYes selected?

If suggestion is not what you want then see if this update helps

VBA Code: 
Sub tMsgBox()
    Dim Answer      As VbMsgBoxResult
   
    If (Range("A9") = "Crime Victims Bank Account") And _
       (Range("G18") = "Yes") And _
       IsEmpty(Range("J18").Value) = False Then
   
        Sheets("CVCP Pick-Up Form").Visible = xlSheetVisible
       
        If Val(Range("A1").ID) <> xlOff Then
       
            Range("A1").ID = xlOff
           
            Answer = MsgBox("You have indicated that you have checks being held For pick-up." & Chr(10) & _
                     "Please Select the tab, CVCP Pick-Up Form, at the bottom To complete And print the CVCP Check Pick-Up Form." & _
                     VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
                     "Would you Like To proceed directly To the CVCP Check Pick-Up Form?", vbYesNo, "Check(s) Held For Pick-Up")
           
            If Answer = vbYes Then
               
                MsgBox "Yay!"
                'This will be changed to take the user to a other tab/sheet automatically
                'that is now visible for them to complete the form.
               
            Else
               
                'This would be to keep them on the current sheet/form
           
            End If
           
        End If
End If

End Sub

note your range is unqualified & assume that the correct sheet is the active sheet when code id run?

Dave
 
Upvote 0
Thank you all for your help. I have taken suggestions from all of the replies and have added segments to my code to make it work for what I need it to do. As a reference, here is the final code that I came up with when adding in your suggestions.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="xxx"
    
    Sheets("Check Pick-Up Form").Visible = False
    Sheets("CVCP Pick-Up Form").Visible = False
    Sheets("Drop Down Menu").Visible = False

    If Target.Cells.CountLarge > 1 Then Exit Sub
        If Not Intersect(Target, Union(Range("A9"), Range("G18"), Range("J18"))) Is Nothing Then
            Dim Answer As VbMsgBoxResult
            If (Range("A9") = "Crime Victims Bank Account") And (Range("G18") = "Yes") And (IsEmpty(Range("J18").Value) = False And (Range("J18") > 0)) Then
                Sheets("CVCP Pick-Up Form").Visible = True
                Answer = MsgBox("You have indicated that you have checks being held for pick-up. The tab 'CVCP Pick-Up Form' has been made avalable to complete and print the CVCP Check Pick-Up Form." & VBA.Constants.vbNewLine & _
                VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
                "Would you like to proceed directly to the CVCP Check Pick-Up Form?", vbYesNo, "Check(s) Held for Pick-Up")
                    If Answer = vbYes Then
                        Sheets("CVCP Pick-Up Form").Select
                    ElseIf Answer = vbNo Then
                        Sheets("CVCP Pick-Up Form").Visible = True
                        MsgBox "Please remember to select the tab 'CVCP Pick-Up Form' to complete and print the CVCP Check Pick-Up Form." & VBA.Constants.vbNewLine & _
                        VBA.Constants.vbNewLine & "Thank You!"
                    End If
            ElseIf (Range("A9") <> "Crime Victims Bank Account") And (Range("G18") = "Yes") And (IsEmpty(Range("J18").Value) = False And (Range("J18") > 0)) Then
                Sheets("Check Pick-Up Form").Visible = True
                Answer = MsgBox("You have indicated that you have checks being held for pick-up. The tab 'Check Pick-Up Form' has been made avalable to complete and print the Check Pick-Up Form." & VBA.Constants.vbNewLine & _
                VBA.Constants.vbNewLine & "Thank You!" & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & _
                "Would you like to proceed directly to the Check Pick-Up Form?", vbYesNo, "Check(s) Held for Pick-Up")
                    If Answer = vbYes Then
                        Sheets("Check Pick-Up Form").Select
                    ElseIf Answer = vbNo Then
                        Sheets("Check Pick-Up Form").Visible = True
                        MsgBox "Please remember to select the tab 'Check Pick-Up Form' to complete and print the Check Pick-Up Form." & VBA.Constants.vbNewLine & _
                        VBA.Constants.vbNewLine & "Thank You!"
                    End If
            ElseIf (Range("G18") = "Yes") And (IsEmpty(Range("J18").Value) = False And (Range("J18") = 0)) Then
                MsgBox "You have selected 'Yes' to having checks held for pick-up, but have entered '0' as the number of checks being held. Please review your answers and adjust them accordingly." _
                & VBA.Constants.vbNewLine & VBA.Constants.vbNewLine & "Thank You!", , "Check(s) Held for Pick-Up Error"
            End If
    End If

    Sheet1.Protect Password:="xxx"
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Workbook_BeforeClose() is not preventing user to close even when specific ranges are blank
Replies
4
Views
307
John_w
J
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
238
ipbr21054
I
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
141
6StringJazzer
6StringJazzer
G
  • Question Question
Stop sort after data is sorted with button
Replies
6
Views
161
Peter_SSs
Peter_SSs
R
  • Solved
Msbox VBA code when cell value is changed
2
Replies
18
Views
611
Ramadan2512
R

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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