PDA

View Full Version : Simple VBA query



Arbedark
04-25-2016, 05:18 AM
Hi folks,
I'm new here but have been using VBA for some time, however recent job shifts have taken me away from using Excel and VBA in general so I've got a little rusty. now i'm back doing my old job again I have been his with a senior moment and cant make my brain work to resolve a simple problem.

I need some VBA code to plug into a sheet that is used to record annual leave for staff. We are now only allowed a certain number of staff, three, off on any week and the sheet must record this leave so it now counts the number of people off then displays the total in cells C49 through AF49, the formula that appears in each of C49:AF49 is

=COUNTIF(I8:J34,"<>")

If the total exceeds three I want a pop up message to appear warning that tis number has been exceeded.


I can get it to do it for single cells using this;

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C49").Value > 3 Then
msg = "number bigger than 3"
pt = MsgBox(msg, vbCritical, "Exceeded Limits")
End If
End Sub

but how do I do it so it checks cells C49:AF49?

My poor old brain is refusing to work on this one so please put me out of my geriatric misery!


Jim

Paul_Hossler
04-25-2016, 05:35 AM
Us old guys have to stick together

Not tested but this should be a good start

Make sure it's in the code page for the WS that has the data on it



'force variables to be Dim-ed
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rWeek As Range
Dim msg As String


'if the changed cells are NOT in in C49:AF49 then just exit
If Intersect(Target, Range("C49:AF49")) Is Nothing Then Exit Sub

'look at each cell
For Each rWeek In Range("C49:AF49").Cells
'if it's more than 3
If rWeek.Value > 3 Then
msg = rWeek.Address & " number bigger than 3"
pt = MsgBox(msg, vbCritical, "Exceeded Limits")
End If
Next
End Sub

Bob Phillips
04-25-2016, 08:17 AM
I wouldn't check them all each time, just he cell that has changed.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("C49:AF49")) Is Nothing Then

If Target.Value > 3 Then
MsgBox Target.Address & " exceeds allowable limit", vbInformation + vbOKOnly, "Exceeded Limits"
End If
Next
End Sub

Paul_Hossler
04-25-2016, 09:00 AM
I wouldn't check them all each time, just he cell that has changed.



My concern was that if the OP did a Control-Enter to fill in Leave Times for multiple weeks and/or multiple people, more than one cell would change

Bob Phillips
04-27-2016, 03:39 AM
My concern was that if the OP did a Control-Enter to fill in Leave Times for multiple weeks and/or multiple people, more than one cell would change

In that case, I would loop Target, not the check range.

Paul_Hossler
04-27-2016, 06:18 AM
Yea, that's what I'd normally do also, but since




the formula that appears in each of C49:AF49 is

=COUNTIF(I8:J34,"<>")

If the total exceeds three I want a pop up message to appear warning that this number has been exceeded.


I started down that path since I figured as re-calc would trigger the event.

Now I'm wondering if the Worksheet_Calculate event might not be more appropriate




Option Explicit

Private Sub Worksheet_Calculate()
Dim rWeek As Range
Dim msg As String

'look at each cell
For Each rWeek In Range("C49:AF49").Cells
'if it's more than 3
If rWeek.Value > 3 Then
msg = rWeek.Address & " number bigger than 3"
Call MsgBox(msg, vbCritical, "Exceeded Limits")
End If
Next
End Sub

Arbedark
05-10-2016, 06:55 AM
Thank you for all the help folks, problem now sorted.

sorry for delay on the thanks but I've been on holiday.

Jim