It should work but it doesn't?

[KeeranB]

Hi. I'm pretty new to VBA coding, and have been given the task of adding a warning popup box when a certain cell (merged into others so this one cell occupies the space of 6), exceeds another cell's Value (which also occupies 6 cells as 1).

The first cell shows a weekly labour budget that is calculated from others dotted around the sheet with regards to target labour of 26%, divided from a weekly sales forecast = budget (cell S9). The other cell which is the cell in question is a running total of spend in £ value (cell L9). Whilst the sheet is populated with Start & Finish times manually by the store manager (on same sheet), in real time the value calculates the total wage for the day for each staff member listed as they go, x hourly rate = total wage, which is in a hidden column, then L9 is a simple =SUM to show total labour spend of those smaller values.

What I would like to happen is when L9 exceeds S9, a message box jumps out and says, "Labour Budget exceeded. Please reconfigure." I would also like the code to undo the last input just before the message box was triggered due to L9 being greater than S9.

I found some existing code listed in another thread (didn't have the undo last entry bit though), attempted to incorporate that on the VBA code screen for the sheet with a few reference changes, but it does absolutely nothing. It's as if the code isn't even there!

When I Save, and click the green 'play' button to run the code, it asks me to create a Macro. Wtf ??? Am I missing something here, coz I have no clue what's happening.

I would post my work for you to see but I don't have it with me at the moment. I'm sure you smartypants out there have the answer that makes it so obvious. Any help would be massively appreciated.

 

[JumboCactuar]

So if L9 > S9 you want to prevent the change and warn the user?

Sounds like a Data Validation job, no need for vba

 

[Norie]

What code is it you tried?

 

[KeeranB]

Thanks for reply Jumbo, yes exactly that, and thats exactly what I thought too. But as L9 and S9 are calculated cells, DV doesn't work apart manually entered data.

Thanks Norie, from what I remember,

Private Sub Worksheet_change etc etc..

Set Target = Me.Range ("L9")
If target.value > ("S9") then
MsgBox "Labour Budget exceeded.... etc"
End if

End sub

I think that's what I tried. Don't have it with me so can't be 100%. Even if the above code is correct, it just won't run??? I don't know what the issue is?

Thanks guys

 

[Norie]

If the code is for a worksheet Change event, which it looks like it might be, it will only be triggered when a manual change is made on the worksheet concerned.

 

[KeeranB]

Yes. As the user enters more and more times for their staff, cell L9 grows in value each time an entry is made. When L9 reaches the point where it's bigger than the budget cell S9, it executes a popup. L9 & S9 are not manually changeable. They're both calculated cells.

 

[Norie]

You need to monitor the cells where the user enters data rather than L9.

When there is a change in those cells then you can check the value in L9 against S9 and if needed display a message box.

 

[KeeranB]

Ok I follow u, so how would I go about doing this please? Thanks for your advice btw.

 

[KeeranB]

There are 21 editable ranges of cells on the sheet. Each range spans 2 columns, for example J14:J25, N14:J25 etc.. Its not the entire column. What you're saying is that something needs to monitor those ranges for any changes. If yes, then check if L9 is < S9, also if Yes, do nothing. If no, show warning box, also undoing last change after user clicks OK in warning box.

Quite a task... for me anyway. Wouldn't know where to start lol. Thanks for your guidance ?

 

[KeeranB]

Correction, J14:K25, N14:O25 etc... My bad, doing some filing and missed what I wrote.