Help with worksheet_change

[rcicconetti]

I am trying to display a msgbox if a user selects certain options in a dropdown box.

Here's the scenario.

This is for a scheduling assistant I am building.
Daily schedules have several cells with a dropdown lists that refer to the entire staff.

I know the staff's availability, so I have seperate lists containing that information.
The list of available employees for Monday AM can be found in ("Sheet2").Range("E11:E200")

*******
(Not sure if this is an issue, but this list (in Column E - Monday AM) is populated by a formula.)
=IF(OR(ISNUMBER(SEARCH({"Monday AM ONLY","Monday ANYTIME"},$C11))),$B11,"")
Where
Column B contains the Entire Staff.
Column C contains the availablity information.
Column E has only the names of employees available on Monday Morning (so there are several empty cells in this range)
*******


Assume a user is working on Monday's Schedule (found in Sheet20.) From a dropdown list, I choose "Johnny D."

Johnny D. isn't typically available on Mondays and although his name appears in the dropdown, it DOES NOT appear in ("Sheet2").Range("E11:E200")

I would like a msgbox to appear alerting the user that there is a conflict. I still want the ability to add Johnny to the list (on this particular Monday, he happens to be available) so using the B85:B200 as the validation list won't work. I just want to throw out a reminder to the scheduler that "This Employee is not typically available on this day." The user can hit "ok" ands continue on.


I've tried several combinations of ideas, but I can't seem to get the result that I'm looking for.
I've had an issue referencing ("Sheet2").Range("E11:E200") in the code, so I made a local reference on the sheet in Range(B85:B274). It's not ideal, but at least it got me closer to a result.

This was my most recent attempt. It gives me the EXACT OPPOSITE of what I want. If the name in the dropdown exists in Range(B85:B274), I get the msgbox, if it does not exist, I get nothing.

I assumed that changing "=" to "<>" would give me the correct result, but when I change line 3 to "If Cell.Value <> Target.Value Then" it throws the msgbox EVERYTIME, no matter what is selected.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target.Value
For Each Cell In Range("B85:B274")
If Cell.Value = Target.Value Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee"
Exit For
Else
End If
Next
End With
End Sub

ANY HELP WOULD BE GREATLY APPRECIATED!!!!

THANKS

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[rcicconetti]

Here is a link to a MockUp of the situation I explained above:

https://mancoandmancopizza-my.share...JLpyhPrGogpIkBjVN209tIzB6NgasuynTnSw?e=H9dfnU

I would like to have a msgbox pop up whenever a user attempts to schedule an employee who is NOT available on a certain day.

In this mockup:

"A. Lyon#" IS NOT available on Monday AM. When the name is selected, I get no msgbox.
"A. Schmidt#" IS available on Monday AM. When the name is selected, I DO get the msgbox.

This is opposite of what I am looking for.


Furthermore, I need to be able to apply this principle to specific ranges.

As you'll see in the mockup:
Focusing On SERVERS
Each sheet has named ranges. For Monday there are "MON_AM_Servers", "MON_PM_Servers", etc.
The cells in these ranges all have data validation referring to a department specific Master Employee List. ("Server Data").Range("B10:B49")
Servers who are available on Mondays for the AM Shift are represented in ("Server Data").Range("E10:E49")
Servers who are available on Mondays for the PM Shift are represented in ("Server Data").Range("F10:F49")

For some reason, I could not reference the range from the sheet "Server Data" directly, so I made a reference to it on the "Schedule" sheet in Range("B37:C150")

Here is what I've got so far, and I know it's way off. As I mentioned in the initial post, the results are OPPOSITE of what I am looking for and changing
"If Cell.Value = Target.Value Then" to "If Cell.Value <> Target.Value Then" did not work. It actually just calls the msgbox every time.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Value
For Each Cell In Range("B36:B149")
If Cell.Value = Target.Value Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee"
Exit For
Else
End If
Next
End With

End Sub

Also, I realize that I am not accounting for the seperate lists. For example, if a user changes a cell the MON_AM_Server range, calling the msgbox needs to be dictated by the list in range("B37:B150"). However, if a user changes a cell the MON_PM_Server range, calling the msgbox needs to be dictated by the list in range("C37:C150").

(Again, I would like to eliminate the need for the local reference, so ideally the code should pull the data for ("B37:150") from ("Server Data").Range("E10:E49") and the data from ("C37:C150")
from ("Server Data").Range("F10:F49")

 

[mumps]

The link you posted takes me to the 'Sign-In' page. I need a direct link to the file.

 

[rcicconetti]

Not sure what happened to that link.

Please try this:

Scheduling MockUp.xlsm

Shared with Dropbox

www.dropbox.com

 

[OaklandJim]

I hope that this does what you want. Most often Mr Excel forum people make my code better but I think that this works despite "verbosity" of my code.

Option Explicit  '<= good practice to use this so variables must be declared before they are used.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim bFound As Boolean
    
    Dim rCell As Range
    
    bFound = False
        
    For Each rCell In Me.Range("B36:B149") 'Me refers to the worksheet that the event macro is in.

        If Trim(rCell.Value) = Trim(Target.Value) _
         Then
            bFound = True
            Exit For
        End If
        
    Next rCell
        
    If Not bFound _
     Then
        MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
    End If
        
End Sub

The modified workbook is HERE.

 

[rcicconetti]

OaklandJim!

Thank you! That was the first part of the problem, and it works!

In order to address seperate named ranges, I combined your code with the "If not intersect" method.

...perfect match!

Thank you so much!



This leads me to a new problem (I think).

Can(should) this sub be shortened?

This only represents 1 DEPARTMENT for 1 full Day. There are 6 departments scheduled over 14 days, and the all have AM and PM shifts. So the code below would be repeated 168 times with slight variations that will address different ranges.

Is there a more efficient way to write this?
Should I make 168 different subs? Or continue to nest them?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim bFound As Boolean

    Dim rCell As Range

    bFound = False

    'WEDNESDAY AM SERVER

   If Not Intersect(Target, Range("WED_AM_SER")) Is Nothing Then

    For Each rCell In Sheet2.Range("S11:S800") 'Me refers to the worksheet that the event macro is in.

        If Trim(rCell.Value) = Trim(Target.Value) _
         Then
            bFound = True
            Exit For
        End If

    Next rCell

    If Not bFound _
     Then
        MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
    End If

   End If


   'WEDNESDAY PM SERVER

   If Not Intersect(Target, Range("WED_PM_SER")) Is Nothing Then

    For Each rCell In Sheet2.Range("U11:U800") 'Me refers to the worksheet that the event macro is in.

        If Trim(rCell.Value) = Trim(Target.Value) _
         Then
            bFound = True
            Exit For
        End If

    Next rCell

    If Not bFound _
     Then
        MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
    End If

   End If

 

[rcicconetti]

I created a new thread for this question.

YOU CAN FIND IT HERE

Thanks Again OaklandJim!