MS excel VBA: Message Box dependant on a Vlookup result

Question

I am struggling to find the correct bit of code to pop up a message box in Excel based upon a date table. My Workbook contains a front page where the user selects a free to choose date range, and I need my workbook to pop up a message if any of the dates correspond to a date in my date table (I attached some screenshots to create a clear picture.

the task of creating a pop up box etc.. I am perfectly comfortable with, however I cannot really find a way to only show a message box if any of the dates between the selected dates have a event logged.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

Application.EnableEvents = True

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("D6")

If Not Application.Intersect(KeyCells, Target.Range("A1:G25")) _
           Is Nothing Then
                 Call EventFinder
End If
End Sub

Result

It is actually very simple. Use the `Worksheet_Change` Event to Check if the relevant date range has any event or not. And since you are going to work with `Worksheet_Change`, you may want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) for starters. Give it a **try**. If you are still stuck then post the code that you tried so that we can take it from there — Siddharth Rout, Dec 27 '19 at 12:16
@SiddharthRout Thanks for the fast help. If you check the edited question you can see how far I've come so far. The Vlookup does not work at the moment due to a type mismatch error that I get right now? Also, I need not just one date checked but all dates between Day of Arrival and Day of C/O. I already have a list of these dates (starting at Range("L5")) in my sheet for formula purposes. — MartijnDib, Dec 27 '19 at 12:50
Your code doesn't match the images. Can you confirm my understanding? **1.** You have a C/I Date in say `D6` and C/O date in say `F6` in 2nd Image. **2.** You want to check for an event on the C/I date in the 1st image or for any event in that date range. (5/1-11/1) i.e all 6 days? — Siddharth Rout, Dec 27 '19 at 12:59
It's assumption 2, sorry always a bit tricky to make clear. Our res dept fills in the picture labeled as "Result", once they have done that I need Excel to search in the Date Table for all the dates between C/I and C/O to see if we have events on that day!. Because I dont really know how to make my code search for multiple values my own idea was to loop this function for each date or until a value was found. — MartijnDib, Dec 27 '19 at 13:03
ok so you want to know if there is an event in anyone of the date range. For this, like I mentioned use the `Worksheet_Change` event. Use the `C/I`as your search text. Loop through the Events Database from the first row to [Last Row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then search for the C/I Date. Once you find that check for the next 6 (Number of Night) rows. — Siddharth Rout, Dec 27 '19 at 13:13
Do you intend to use your code from so named `EventCheck()` event? If yes, a 'Worksheet_Change' event will be triggered only if a change will be done in "D3" cell or in a multiple range including "D3" cell... Are you sure that this is what you want? — FaneDuru, Dec 27 '19 at 13:56
@FaneDuru Yes, I want my code to run only when 1 specific cell is changed in my sheet is changed. I got that Working with the code written above. — MartijnDib, Dec 27 '19 at 14:12
@SiddharthRout Thanks for the help! this was one of the major challenges in this project! — MartijnDib, Dec 27 '19 at 14:14
@SiddharthRout https://stackoverflow.com/questions/59502155/vba-loop-a-vlookup-function-until-result-is-not-empty I created a new question to solve the looped VLookup macro to keep things a bit more clear. But yes, the automatic trigger of the macro works like an absolute charm! — MartijnDib, Dec 27 '19 at 14:30
So, do you want the `Worksheet_Cange` event to be triggered only if the changed cell will be in the range `"A1:D6"`? If yes, why the strange intersection need? If not, did you try the code for a cell being outside `"A1:D6"` range? — FaneDuru, Dec 27 '19 at 14:30

score 0 · Answer 1 · answered Dec 27 '19 at 14:32

0

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

Application.EnableEvents = True

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("E6")

If Not Application.Intersect(KeyCells, Target.Range("A1:G25")) _
           Is Nothing Then
                 'Call Any Macro You Want
End If
End Sub

With this Code I was able to launch my macro any time the cell E6 was changed in my worksheet! Please mind that this code needs to be inserted in the workbook object! not into a module. The to be called macro can be inserted into a module.

answered Dec 27 '19 at 14:32

MartijnDib

28
6

If you just wonted your event to be triggered by any change in "E6" cell, why didn't you simply write `If Target.Address = "$D$6" Then Call YourMacro`? – FaneDuru Dec 27 '19 at 14:39
In the way you made the code, the event will be triggered for any change in range `"A1:E6"`. And if this is what you wanted and only wrongly explained, why that strange intersection with range `"A1:G25"`? That 25 could be any number bigger or equal to 6... – FaneDuru Dec 27 '19 at 14:48
`If Not Intersect(Target,Range("E6")) Is Nothing Then` This is good enough – Siddharth Rout Dec 27 '19 at 14:53

MS excel VBA: Message Box dependant on a Vlookup result

1 Answers1