VBA Comparing Name on schedule to vacation list

Realjoshtodd

New Member
Joined
Sep 26, 2017
Messages
34
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.


1596071730345.png


1596071872851.png
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 26, 2017
Messages
34
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

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

(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:
Code:
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:
Code:
Private Sub OkButton_Click()
ActiveCell.Value = AvailableNames.Value
PickName.Hide

End Sub
 

Realjoshtodd

New Member
Joined
Sep 26, 2017
Messages
34
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

New Member
Joined
Sep 26, 2017
Messages
34

ADVERTISEMENT

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
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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:
Code:
If Application.Intersect(Target, Range("Assign1")) Is Nothing Then
Change the "If" line in the loop to read:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,853
Messages
5,627,263
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top