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
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