Start Free Trial

asked on

Name Selection and Sorting

Good Morning Experts,
I have a large list of employees that I need to team together during specific shifts and then have the teams cross-train each other. Please see the attached spread sheet as an example. I will constantly be added or removing Employee Names from the Column 1. In Column 2, I would like to select from a drop down for each name, the employees available shift for work, i.e. All, Weekdays, Weeknights, Weekends, None. In Column 3, I would like the Possible Team Member to be displayed in a dropdown for selection based upon their overlap of Column 2, Shift Availability... That is, if an employee is available for All shifts, then their name would appear in the "Possible Team Member" dropdown for all employees; but, if no shifts are available (None) then their name would not appear in the dropdown as a Possible Team Member. Following this logic, a Weekday employees would only team up with All and Weekday available employees.

Also... If possible, once a "Team" has been paired, I would like to have the name removed from the "Possible Team Member" dropdown selection for other employees.... And, when I move down the employee list to the name of an employee who is already paired above, the only "Possible Team Member" name in the dropdown list should be the person with whom they are paired above.

Finally, each Team will be cross-training other employees... perhaps up to seven different employees may be trained by a team. Can a dropdown be generated to fill in the possible names of individuals available to be Cross-Trained minus those who are the Team Members and minus those who have already been selected to be trained by a Team? This list will include every employee in Column 1.

Thank you for your help!
Michael
Employee-Selection.xlsx

Ejgil Hedegaard

Try attached.
Accept macros.

When a cell is selected, the validation list is created.
The order you select names does not matter.
If a list does not appear in the cell, then it is because no names are possible to select.
Employee-Selection.xlsm

ASKER

Hi Ejgil,
Thank you for the code... Unfortunately, I cannot seem to run it without crashing. I can see the first dropdown box... select a name, then when I try to move to any other cell it aborts. I am using Microsoft 365 on a Mac.... could this be the problem?

Thanks,
Michael

ASKER

Hi Ejgil,
OK... So I have finally been able to keep the sheet open without crashing, but all that functions is the dropdown for the "Shifts Available" for all names in Column 1. Under Column 2, "Possible Team Members," only the first cell has a dropdown... no other cells in the table appear to have a dropdown function.

Also, there would need to be a slight correction in the names appearing in "Possible Team Members" dropdown ... In the case of the first individual, it is not gathering all the possible names, in that a person that is available "All" the time, should see the entire list of people, because he personally could any shift.

Thanks again for your help,
Michael

Ejgil Hedegaard

Misunderstood your requirements for All.
Here is a new version.

You must be able to run macros, if not it will not work.
The validation list is created by the macro when a cell is selected, so none exist until the macro creates the list.

I don't know how Excel 365 on Mac works.
Employee-Selection.xlsm

ASKER

Thank you Ejgil for this revision...

I made a mistake in the last description of dropdown generated for the individuals that are available for All, Weekday, Weekends, and Weeknights Shifts... They will never be able to have a "Possible Team Member" that has "None" listed in Shifts Available.

The "None" individuals will only appear in the dropdowns for Cross-Training.

Thanks again... I really appreciate your help with this!
Michael

Ejgil Hedegaard

None excluded.

Did you get it to run.
Employee-Selection.xlsm

ASKER

Hi Ejgil,
Sorry for the delay in getting back to you. No... I still can't get it to run. The error code I get is:

Run-time error '91':
Object variable or With block variable not set

So, I'm not sure what to do. This error occurs as soon as I try to move to a different cell in the sheet.
I have tried other code Excel docs and they work fine, so I know I am missing some variable that needs defining.

Thanks,
Michael

ASKER

Also, when I try to debug the code, it just shuts down Excel, so I can't really tell what the code problem is... I wish I know this better.
Thanks

ASKER

Hi Ejgil,
Are there perhaps some specific References I need to add to the VBAProject to make the program function?
Thanks

Ejgil Hedegaard

Open the VBA editor.
In Excel it is Alt+F11.
If you have a developer tab, you can also open it there.
The code is in the worksheet module for Sheet1.
Set a breakpoint at the first executable line.
rwMax = Range("A1").CurrentRegion.Rows.Count
Set the marker somewhere in the line and press F9, it turns brown.
Then the code stops at that point, and you can step with F8 to see where it fails.
In the above line, Range refers to active sheet, but perhaps Mac Excel needs a sheet specification.

Here is the code

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rwA As Integer, rwC As Integer, rwMax As Integer, col As Integer
    Dim List As String, i As Integer
    rwMax = Range("A1").CurrentRegion.Rows.Count
    
    'Team member
    If Not Intersect(Target, Range("C2:C" & rwMax)) Is Nothing And Target.Count = 1 And Range("B" & Target.Row) <> "None" Then
        List = ""
        For rwC = 2 To rwMax
            If Range("C" & rwC) = Range("A" & Target.Row) Then
                List = List & Range("A" & rwC)
            End If
        Next rwC
        If List = "" Then
            For rwA = 2 To rwMax
                If (Range("B" & rwA) = Range("B" & Target.Row) Or Range("B" & rwA) = "All" Or Range("B" & Target.Row) = "All") _
                  And Range("B" & rwA) <> "None" And rwA <> Target.Row And Range("C" & rwA) = "" Then
                    i = 0
                    For rwC = 2 To rwMax
                        If rwC <> Target.Row Then
                            If Range("C" & rwC) = Range("A" & rwA) Then
                                i = 1
                            End If
                        End If
                    Next rwC
                    If i = 0 Then
                        If List <> "" Then
                            List = List & ","
                        End If
                        List = List & Range("A" & rwA)
                    End If
                End If
            Next rwA
        End If
        Target.Validation.Delete
        If List <> "" Then
            With Target.Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=List
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If

    'Cross-Training
    If Not Intersect(Target, Range("D2:J" & rwMax)) Is Nothing And Target.Count = 1 Then
        List = ""
        For rwA = 2 To rwMax
            If rwA <> Target.Row And Range("A" & rwA) <> Range("C" & Target.Row) Then
                i = 0
                For rwC = 2 To rwMax
                    For col = 3 To 10
                        If Not (rwC = Target.Row And col = Target.Column) Then
                            If Cells(rwC, col) = Range("A" & rwA) Then
                                i = 1
                            End If
                        End If
                    Next col
                Next rwC
                If i = 0 Then
                    If List <> "" Then
                        List = List & ","
                    End If
                    List = List & Range("A" & rwA)
                End If
            End If
        Next rwA
        Target.Validation.Delete
        If List <> "" Then
            With Target.Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=List
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If

End Sub

Open in new window

ASKER

Hi Ejgil,
Thanks again for your help... OK, I have found that I crash on the first line of code.
If I change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) - to -

Private Sub Worksheet_Selection(ByVal Target As Range) - or -
Private Sub Worksheet_Activate(ByVal Target As Range)

Then at least Excel doesn't crash... but, of course, the code doesn't function either.
Thoughts?

ASKER

Hi Ejgil,

If I only have this portion of the code in the functioning, Excel does not crash:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rwA As Integer, rwC As Integer, rwMax As Integer, col As Integer
Dim List As String, i As Integer

End Sub

But as soon as I include:

rwMax = Range("A1").CurrentRegion.Rows.Count

It crashes...

Ejgil Hedegaard

I have filled in a request for help by a moderator.

ASKER

Thank you... I have tried this Excel doc on Windows and Mac platforms and it still crashes.
Very confused.

ASKER

Hi Ejgil,
I have another questions... I have been trying to work through your code so that I can understand it. So, just to make sure... what else, if anything, needs to be added to your code so that the following permutations work in populating the team member dropdown (column 3)?

1) If an employee is available for "All" shifts - Then they can work with other employees that are available "All, Weekday, Weekend, Weeknight"
2) If an employee is available for "Weekday" shifts - Then they can work with other employees that are available "All, Weekday"
3) If an employee is available for "Weekend" shifts - Then they can work with other employees that are available "All, Weekend"
4) If an employee is available for "Weeknight" shifts - Then they can work with other employees that are available "All, Weeknight"

Thanks,
Michael

Ejgil Hedegaard

It should do just that.
The first part, line 11-15 checks if employee is already selected, and if so, the list only shows 1 item.
Line 18-19 add name to validation list if
- shift is the same as the row for validation (Target.Row), Range("B" & rwA) = Range("B" & Target.Row)
- or shift is All, Range("B" & rwA) = "All"
- or shift is All in validation row, Range("B" & Target.Row) = "All"
- excluding when shift is None, Range("B" & rwA) <> "None"
- and excluding the validation row itself, rwA <> Target.Row
- and name has no assignment yet, Range("C" & rwA) = ""
But not if name has already been used, line 20-27.

Line 28-33 add the name to the list.
And line 37-50 delete the existing validation list, and creates a new.

It will be clear when you get VBA to work.
I guess the problem is that Visual Basic has to be installed.

ASKER

Thanks Ejgil... I really appreciate you walking me through this. Sorry for my lack of understanding. I have copied a few VBA codes to test in Excel, and they seem to work fine, but I have found a similar problem discussed... please take a look at this link, as it describes my same problem. Any ideas?
Thanks,
Michael

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ddb94df-6042-48c2-bba8-4cf0af863f5c/excel-2010-pro-64bit-crashes-on-change-by-val-sub?forum=exceldev

Ejgil Hedegaard

Just try as suggested.

In an new workbook, insert this in a worksheet module, to see if that works.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Cell " & Target.Address & " selected"
End Sub

If it does, save as xlsm or xlsb, close Excel, and reopen workbook.
Then try with your original employee workbook.
If that works, replace with the code.
Finally save as xlsm, or perhaps as xlsb.

ASKER

Hi Ejgil,

Interesting... I made a new workbook and inserted the following code into the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Cell " & Target.Address & " selected"
End Sub

It worked with out a problem, identifying any cell click on without crashing. So, I opened up the worksheet and then added:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rwA As Integer, rwC As Integer, rwMax As Integer, col As Integer
Dim List As String, i As Integer

End Sub

Again... no problem and no crash, so I added:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rwA As Integer, rwC As Integer, rwMax As Integer, col As Integer
Dim List As String, i As Integer
rwMax = Range("A1").CurrentRegion.Rows.Count

End Sub

But, now it crashes? ...in both Window and Mac.
There must be something simple I'm missing... Any other ideas?

Ejgil Hedegaard

Try adding a worksheet identifier.

Dim ws As Worksheet
Set ws = ActiveSheet
rwMax = ws.Range("A1").CurrentRegion.Rows.Count

And change rwMax As Integer, to rwMax As Long

Type something in A1 so it is not empty.

ASKER

Hi Again,

OK... So I have as code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rwA As Integer, rwC As Integer, rwMax As Integer, col As Integer
Dim List As String, i As Integer

MsgBox "Cell " & Target.Address & " selected"

Dim ws As Worksheet
Set ws = ActiveSheet
rwMax = ws.Range("A1").CurrentRegion.Rows.Count

End Sub

The first cell I select (A1), does not respond.
If I select cell A2, the sheet responds with: Cell $A$2 Selected "OK"
When I select OK... the program crashes.

If, however, select a cell anywhere else in the table away from A1:A6, that cell is identified, i.e. Cell $F$11 Selected "OK"
Clicking on "OK" results in an endless loop of $A$1:$A$6 Selected "OK"

Note: It does not seem to make any difference if I have rwMax As Integer or rwMax As Long

I've attached the file I was using...
Thanks
New-Workbook3.xlsm

ASKER

Hi Ejgil... Do you have any other thoughts on this code? How do I move it forward from here to get help?
Thank you so much for all you have done.
Michael

Ejgil Hedegaard

I think you should make a new question, about why the code does not run on Mac.

Make a reference to this question.

ASKER

Thanks Ejgil... I'll do that.

ASKER

Hi Ejgil,
Thanks for all the help... I just got a new version of Excel for Windows... And your spread sheet works! Thank You! I don't know what is up with Mac, but I will address that later. Anyway, I still need your help to refine the program a bit more....

Once I have names populate in Columns A and C, when I move over to the Cross-Training columns (D-J), I only see the names of those individuals that were identified as "None" in Column B (Shift Available). I need to see all the names in D-J, excluding the corresponding individual in that row and any already selected in other Cross-Training cells of Columns D-J. Ideally, it would be helpful that if a Team is composed of:

Weekend + All: Then the Cross-Training would only show - Weekend + All + None individuals
Weekday + All: Then the Cross-Training would only show - Weekday + All + None individuals
Weeknight + All: Then the Cross-Training would only show - Weeknight + All + None individuals
All + All: Then the Cross-Training would only show - Weekday + Weekend + Weeknight + All + None individuals
Weekday + Weekday: Then the Cross-Training would show - Weekday + All + None individuals
Weekend + Weekend: Then the Cross-Training would show - Weekend + All + None individuals

Also, I tried adding additional names in Column A, but it does not include them into the cell functions. It's as though they are uniquely separate. Column B drop-down does not work at all with the new names. I tried saving, closing, and re-opening the sheet to see if this would make a difference, but I saw no change. How do we make it so Column A can be added to or deleted from and still have the functions work? I'm probably missing something simple here.

Thanks again for your help Ejgil!

Ejgil Hedegaard

Nothing in the code for Cross-Training eliminates to only None.
Cross training includes all on the list.
I don't remember if it was different in the first version.
See attached where the sheet opens, all names are available.

I have changed so the list can have blanks in column A.
Employee-Selection.xlsm

ASKER

Thank you Ejgil... I'll try it out.

ASKER

Hi Ejgil,

Thank you for the file... amazingly, whatever specific change you made, makes the sheet function in Excel on Mac???

Please see the attached file... I populated column 3 all the way out. Now, when I enter a cross-training cell, I still only see the "None" available employees? What needs to be done to see all of them?

I will try the file in Windows just to make sure this isn't a Mac issue, and let you know.
Thanks,
Michael
Employee-Selection--4-.xlsm

ASKER

Hi Ejgil,
I tried the sheet in Windows Excel, and it functions as described above... the Cross-Training cells only show the "None" employees. I need it to function as described earlier:

Weekend + All: Then the Cross-Training would only show - Weekend + All + None individuals
Weekday + All: Then the Cross-Training would only show - Weekday + All + None individuals
Weeknight + All: Then the Cross-Training would only show - Weeknight + All + None individuals
All + All: Then the Cross-Training would only show - Weekday + Weekend + Weeknight + All + None individuals
Weekday + Weekday: Then the Cross-Training would show - Weekday + All + None individuals
Weekend + Weekend: Then the Cross-Training would show - Weekend + All + None individuals

Thanks,
Michael

ASKER

Hi Ejgil,

One other criteria I just realized is that in the Cross-Training cells... The same two "Teamed" employees (whatever two rows they are in) will be cross-training the same 1-7 employees, so each of these "Teamed" employees in their respective rows should see the same cross-training names. Does this make sense?

Thanks again,
Michael

Ejgil Hedegaard

Check attached.
All names on Cross-Training list, unless already used.
There was an error in the check for used names.

When you select a name in the Cross-Training area, the name is inserted for the team member in column C, in the same Cross-Training column.
If you change, the other is also changed.
Employee-Selection--4-.xlsm

ASKER

Hi Ejgil.... This is AWESOME! Thank You So Very Very Much!!! You are the best! I think this will work great!
It works both on Windows and Mac Excel...
Thank you again!

ASKER

Hi Ejgil... One tiny thing... When I delete a name in the Cross-Training list from one Team Member, it should also delete that name from the other Team Member. Can this be done?

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you Ejgil... This works GREAT!

Ejgil Hedegaard

After a long journey we got to the destination.
Please close the question by accepting the answer.

ASKER

Thank you Ejgil... This solution is AWESOME!

Ejgil Hedegaard

The question is solved with https:#a42065298, and should be closed by accepting that.

ASKER

Hi Ejgil,
I am having problems I guess in closing/accepting your solution.... the link https:#a42065298 is not working for me... Your guidance would be appreciated.
Thanks

Ejgil Hedegaard

Just find the answer above, and accept that.
It is the final answer with the file from 2017-03-26.

ASKER

Thanks for the great help with this issue!