VBA Comparing Name on schedule to vacation list

[Realjoshtodd]

I'm hoping someone on here can help we with what I'm trying to create in our Excel work schedule.

In the Sheet "Availability" it lists all of the employee's in Column B. With the date listed in Row 2.

In the Sheet "Assignments" is where we enter each employee's assignment for that day (Date is listed in Row 2).


When you enter a name into a cell in "Assignments" I need it match the name to the Employee name in "Availability" to verify the spelling is correct (we have multiple Thompsons and other names, so they are listed "Thompson, A" and "Thompson, N") and it's matching the correct person in "Availability"

Secondly I need it to verify that the employee is actually scheduled to be working (not on vacation or otherwise off). It would have one of the following "Days, E Swings, L Swings, Lates" in the date for the employee if they are scheduled to work. Anything else or blank would have them off. This is the "Availability" sheet.


I'm hoping that either as you enter the information it pops up a message if there is something wrong (name spelled wrong, not scheduled to work) so it can be corrected, or be able to click a box that will verify either the shift or the whole day to make sure everything is correct.


I've tried looking at doing =IF statements but that would be in each cell and once you add information it would be over written, plus that many formulas will overload the workbook even on opening.

Any help would be great, I'm lost.

 

[Trevor_S]

I'm assuming that employee names are manually typed into the Assignments sheet?

If so, could you have a VBA userform containing a dropdown box and OK button? Whenever a user selects a cell within the appropriate range on the Assignments sheet, a Selection_Change macro could populate the dropdown box with the names of only those who are available on that date. You could also have some standard options that also appear on the list - such as the "Closed" and "Sign-Up" as shown on your screen image. It might also be feasible to build in functionality to only list those that are working an appropriate shift for the assignment, and to exclude the names of those already assigned to another row on that day. The user could simply pick a name from the list and click OK - the macro would then put that name into the Assignments sheet.

Of course this would only determine whether the selection was valid at the time of input. If it's possible that the Availability sheet could be changed later, you could perhaps have conditional formatting on the Assignments sheet to highlight those which were no longer valid?

Happy to help with the VBA coding if that idea sounds useful.

 

[Fluff]

Cross posted VBA code to compare names entered in multiple sheets - Work Schedule

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Realjoshtodd]

That sounds like exactly what I'm picturing in my head with it. If you are willing to help that would be greatly appreciated.

 

[Trevor_S]

(Moderators: thanks for flagging the cross posting. As I'd already started replying on one forum, I'm posting this reply to both - hope that is ok.)

I've created a mini version to demonstrate - link here: AvailableNameTest.xlsm Explanation and VBA are below

I've create two named ranges:
"Avail": Availability sheet, from B2 to the bottom right corner of data - i.e. including date headings/names
"Assign": Assignments sheet, from B2 to the bottom right corner of data - i.e. including date headings, but excluding anything before the first column containing names.

I've created user form PickName, with combo box AvailableNames and command button OkButton.

In the VB Editor, this code goes in the bit for the assignments sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.Intersect(Target, Range("Assign")) Is Nothing Or Target.Row = 2 Then
    ' Do nothing as active cell is either outside the range, or in the date row

Else
' Find column in Availability sheet with the same date as the selected cell in the Assignments sheet
DateColumn = Application.Match(CLng(ActiveSheet.Cells(2, Target.Column).Value), Sheets("Availability").Range("2:2"), 0)

' Set up the dropdown box, and display the form
With PickName
.AvailableNames.Clear

' Add standard items to always appear
.AvailableNames.AddItem ("Closed")
.AvailableNames.AddItem ("Sign-Up")

' Loop to find available names and add to list
For n = 1 To Sheets("Availability").Range("Avail").Rows.Count ' Loop through rows and add names to dropdown
CheckAvail = Sheets("Availability").Cells(n + 2, DateColumn).Value
If CheckAvail = "Days" Or CheckAvail = "E Swings" Or CheckAvail = "L Swings" Or CheckAvail = "Lates" Then
.AvailableNames.AddItem (Sheets("Availability").Cells(n + 2, 2).Value) ' Get the name from column B, the second column
End If
Next

' Display the form
.Show

End With
End If

End Sub

This code goes in the bit for the user form:

Private Sub OkButton_Click()
ActiveCell.Value = AvailableNames.Value
PickName.Hide

End Sub

 

[Realjoshtodd]

This works really well. I integrated it into my workbook. Is there a way to limit select rows in the assignment sheet to only read those with that shift? Like Rows 9 through 51 are only "DAYS", Rows 77 through 120 is E SWING and L SWING. and LATES in ROWS 128 through 171?

 

[Realjoshtodd]

Cross posted VBA code to compare names entered in multiple sheets - Work Schedule

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Sorry about that. I will make sure I note that going forward

 

[Fluff]

 

[Trevor_S]

I think that's possible (although I haven't tested this in Excel).

Although I started the named range Assign at row 2, I don't think it really needs to include the date row - it just needs to cover the cells that you want to trigger the userform. And there's no reason why you couldn't have multiple named ranges to cover different rows. So say you had names Assign1, Assign2 and Assign3 set up on that sheet to cover the relevant columns of the three row ranges mentioned. In the main code:

Change the first "If" line to read:

If Application.Intersect(Target, Range("Assign1")) Is Nothing Then

Change the "If" line in the loop to read:

If CheckAvail = "Days" Then

You've now got the code to get "days" shifts for the first block of rows. Copy all of the macro (apart from the "Private Sub ..." and "End Sub" lines) and paste twice just before the "End Sub". You've now got code for the three blocks. You just need to amend the two lines mentioned above in each of the two new blocks - to refer to ranges "Assign2" and "Assign3" - and to test for the correct shift description(s) relating to those ranges.

The userform code is unchanged.