ComboBox List Bug

[Olson14]

I built a userfrom to track what we call 'Take 30's' with our employees. I have several combobox's but one of them has issues about half the time. The employee combobox rowsource is referencing a named range which changes based on the value selected in the manager box. Essentially, when the manager box is populated, they can then choose from a list of the employees they are in charge of. However, sometimes after entering the employee box, the full list is not populated. The photos attached are showing when it does and doesn't work. I've played with the scroll bar, height, etc. and I can't seem to figure out why it only works part of the time. Sorry for the poor description. New to this forum & VBA but hoping someone can help?

 

[Yongle]

How does the name of the manager impact the contents of the named range ?
- I need to understand the process

 

[Olson14]

How does the name of the manager impact the contents of the named range ?
- I need to understand the process

1. Users first select the manager name from a drop down list which is populated based on the named range 'MngrList'
2. When the value of the combobox changes, it runs the following code which essentially generates a new list of employees that operate under the selected manager...
3. I have a dynamic named range called 'EmpList' which references the new list generated by the vba, and is referenced in the employee combobox

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Lists").Range("C1").value = Take30.ComboBox1.value
    ThisWorkbook.Worksheets("Lists").Range("C2:C300").ClearContents

Dim rng As Range, Cell As Range
Set rng = Range("MngrRaw")

For Each Cell In rng
If Cell.value = Sheets("Lists").Range("C1") Then
Cell.Offset(0, -1).Copy
Worksheets("Lists").Visible = True
Sheets("Lists").Select
Range("C300").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("Lists").Visible = False
End If
Next

Application.Calculate

End Sub

 

[RoryA]

I'd either avoid using Rowsource altogether, or explicitly name your result range each time it's created. By the way, it is not necessary to unhide/select/hide your Lists sheet for this:

For Each Cell In rng
If Cell.value = Sheets("Lists").Range("C1") Then
Cell.Offset(0, -1).Copy Worksheets("Lists").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
End If
Next

 

[Olson14]

I'd either avoid using Rowsource altogether, or explicitly name your result range each time it's created. By the way, it is not necessary to unhide/select/hide your Lists sheet for this:

For Each Cell In rng
If Cell.value = Sheets("Lists").Range("C1") Then
Cell.Offset(0, -1).Copy Worksheets("Lists").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
End If
Next

Thanks for the feedback. You could probably tell, I am pretty new to this! What do you mean when you say 'explicitly name your results range'? Do you have an example or something I can reference? I really appreciate your help.

 

[RoryA]

I mean that after that code that populates the range, you'd add something like this:

With Worksheets("Lists")
.Range("C2", .Cells(.Rows.Count, "C").End(xlUp)).Name = "name of range for your combobox goes here"
End With

 

[Olson14]

I mean that after that code that populates the range, you'd add something like this:

With Worksheets("Lists")
.Range("C2", .Cells(.Rows.Count, "C").End(xlUp)).Name = "name of range for your combobox goes here"
End With

Hi Rory,
So everything seems to be working thus far, and I really appreciate the help. However, in attempt to not use rowsource, I added one last line of VBA as seen below, in attempt to set the combobox list to the named range employee. However, I continue to get errors on it. I have tried multiple ways of writing it and continue to get errors. Any thoughts on how to populate the combobox without rowsource? Thanks again.

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Lists").Range("C1").value = Take30.ComboBox1.value
    ThisWorkbook.Worksheets("Lists").Range("C2:C300").ClearContents

Dim rng As Range, Cell As Range
Set rng = Range("MngrRaw")

For Each Cell In rng
If Cell.value = Sheets("Lists").Range("C1") Then
Cell.Offset(0, -1).Copy Worksheets("Lists").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
End If
Next

With Worksheets("Lists")
.Range("C2", .Cells(.Rows.Count, "C").End(xlUp)).Name = "Employee"
End With

ComboBox1.List = Worksheets("Lists").Range("Employee").value

End Sub

 

[RoryA]

That looks correct to me - as long as you have cleared the Rowsource property from the control at design time. Otherwise you'll get a Permission Denied error (Or something like that).

 

[Olson14]

That looks correct to me - as long as you have cleared the Rowsource property from the control at design time. Otherwise you'll get a Permission Denied error (Or something like that).

Hi Rory,
I had to clear the rowsource for the combobox, and it actually should've been ComboBox4. instead of ComboBox1 (Stupid error on my end). Thank you SOOOOO much for your help with this. The tool is now working properly, and I have not had any issues with the combobox since implementing your advice. I really appreciate more than you know! THANK YOU SO MUCH!!!

 

[RoryA]

Glad to help. Stay safe!