Worksheet Change By Val Target as Range

[Aryzona]

I am working on a "scheduling" worksheet - there is a Monday tab, Tues etc ... I need to be able to isolate changes to column K - Column N for each row ... K12:N55. Each row = 1 the employee

Each employee can only sign up for an activity in column K, L, M, N 1 time a week.

The worksheet already has validation in place for this area with the "data validation" in Excel that determines if there is space available in this task or if it is already "full" ... The numbers on the 3rd visible row are the number we need, and the numbers below reflect the amount who have signed up. (See image below) - This is why i really need to utilize VBA for the secondary validation.

I need to evaluate the spreadsheet for ONLY column K - N (Peach Color) for each row . Default value is 0, if they sign up for these columns they can do .50 (half day) or 1.00 full day - but they can only do 1.00 total 1 time in the entire week.

I need the worksheet to see the change - pop a message that says "This is your 1 Peach area for the Week" and to subsequently Look at all the sheets (mon - fri) and pop a message if they TRY to sign up a second time that says "You have already signed up for the peach tasks this week. Please choose something else."

I am a complete newby with 1 working VBA Excel Macro under my belt. I have a semi working macro right now .. but if they change the worksheet anywhere and there is a value in K - N it pops their message " you have already signed up .. " This is not helpful if they changed a value is "C" - not column k - n.

Please be kind, i am learning on the fly ... Here is the "semi-working" module i have currently it only pops message 1 .... and it only encompasses row 12 (1st active row) for testing ... it will ultimately need to work for rows 12 - 55.

Sub IF_THEN()

If Range("K12").Value = "1" Then
MsgBox "This will be your 1 Peach activity for the week"

End If

If Range("L12").Value = "1" Then
MsgBox "This will be your 1 Peach activity for the week"

End If

If Range("M12").Value = "1" Then
MsgBox "This will be your 1 Peach activity for the week"

End If

And the Worksheet Change ...

I do have a column (not visible ) Q that adds up the values for the row... but since it is a summed total and not "user" changed .. it wont trigger the error message automatically ... but that would be the easiest way ... if Q = 1 then "you have signed up ..." and if it equals anything greater than 1 then "You have already signed up this week .. choose another"

Help Please!!!

 

[Aryzona]

I have solved my first issue

Module code:
Public r As Range, myVal

Workbook Code:
Private Sub Workbook_Open()
Set r = Sheet7.Range("Q12:Q55")
myVal = Application.Transpose(r)
Worksheet Code:
Private Sub Worksheet_calculate()
On Error GoTo halt
With Application
.EnableEvents = False
If Join(myVal) <> Join(.Transpose(r)) Then
MsgBox "You have selected Peach"
End If

myVal = .Transpose(r)
forward:
.EnableEvents = True
End With
Exit Sub

halt:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume forward


End Sub

Now i need to add a second round of validation to check to see if Sheet12 for the same range on sheet 12 is greater than 1 and if it is Msgbox " Nope" .

Any thing i try to create Stops the msgbox from 1st round of validation from appearing ...

How can i make it validate the first set and display the message box and THEN validate the 2nd set and pop a message box if the value in set 2 is Greater than 1?

I have added second set to Module as:
Public n As Range, myValn
and to Work book as:
Set n = Sheet12.Range("Q12:Q55")
myValn = Application.Transpose

How do I do 2nd validation on Transpose and NOT negate first validation of Transpose(r) ?

 

[Aryzona]

I have solved my first issue

Module code:
Public r As Range, myVal

Workbook Code:
Private Sub Workbook_Open()
Set r = Sheet7.Range("Q12:Q55")
myVal = Application.Transpose(r)
Worksheet Code:
Private Sub Worksheet_calculate()
On Error GoTo halt
With Application
.EnableEvents = False
If Join(myVal) <> Join(.Transpose(r)) Then
MsgBox "You have selected Peach"
End If

myVal = .Transpose(r)
forward:
.EnableEvents = True
End With
Exit Sub

halt:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume forward


End Sub

Now i need to add a second round of validation to check to see if Sheet12 for the same range on sheet 12 is greater than 1 and if it is Msgbox " Nope" .

Any thing i try to create Stops the msgbox from 1st round of validation from appearing ...

How can i make it validate the first set and display the message box and THEN validate the 2nd set and pop a message box if the value in set 2 is Greater than 1?

I have added second set to Module as:
Public n As Range, myValn
and to Work book as:
Set n = Sheet12.Range("Q12:Q55")
myValn = Application.Transpose

How do I do 2nd validation on Transpose and NOT negate first validation of Transpose(r) ?

the thumbs down should be "" did not know that would trigger a picture

 

[Aryzona]

the thumbs down should be "the letter n" did not know that would trigger a picture