Need help for VBA/Macro please!

[VincentW29]

Good morning all,

I'm having some difficulty with setting up a macro which will trigger if two conditions are met in a worksheet.

Require a message box to appear if the value in a particular cell (D4) in the D column shows the keyword “Supplier” and if the value in a different cell (H7) in column H shows the keyword “Shipment”.

The values in Cells H7 and D4 are selected via dropdown lists.

Desperate for help here as I’ve tried many different ways but am unable to find a solution, any help is appreciated!

 

[DanteAmor]

Hi and welcome to MrExcel!

Put the following code in the events on your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("D4, H7")) Is Nothing Then
    If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then
      MsgBox "two conditions are met"
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[VincentW29]

Hi and welcome to MrExcel!

Put the following code in the events on your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("D4, H7")) Is Nothing Then
    If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then
      MsgBox "two conditions are met"
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Hi Dante! Thank you so much for your help, can’t wait to try this out, but before that just a quick question - if I have multiple selections (apart from D4 and H7), and would like additional message boxes to appear on the same sheet if:
- A5 matches a certain date or
- Another two cells (H5 and I5) shows specified values
- K10 is more than a certain numerical value

could use back the same code as above, which you so kindly provided? Or will the specified range have to change?
So sorry for the multiple questions as I’m rather new to this and not exactly a coder like yourself! Thanks so much again!

 

[VincentW29]

Hi and welcome to MrExcel!

Put the following code in the events on your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("D4, H7")) Is Nothing Then
    If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then
      MsgBox "two conditions are met"
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Hi Dante!

I tried entering the code and it works! But within the same worksheet, I actually require multiple message boxes, for example:
- if cell H7’s value is “Public”
- if cell J5’s numerical value exceeds 50,000
- if cells D4 and H7 meet another set of criteria (D4 = Customer / H7 = Receipt), another message box to appear.

It would really be life saving if you could enlighten me on this! Thank you in advance!

 

[DanteAmor]

If you only want a few warnings, you should use data validations.

For example, to this:

- if cell H7’s value is “Public”

To put a data validation, follow the steps in the following link:

How to use Data Validation in Excel – custom validation rules and formulas - Ablebits.com

Learn how to use Data Validation in Excel based on a custom rule with your own Excel data validation formula. Formula examples to allow only numbers or text values, or only text beginning with specific characters, validate dates and times, and more.

www.ablebits.com

In the Formula field put:

=H7<>"Public"

On Error Alert tab, choose "Inforamation" in Style and enter the message in Error message.

More about messages in data validation:

How to Make Custom Excel Data Validation Message Help Users

How to create data validation custom messages to help users enter data correctly. Special error messages to help people fix invalid entries without frustration

www.contextures.com