UserForm Dropdown from another sheet

[TimC986]

Hello, wondering if someone can help me, I think this will be a relatively simple question for someone.

I have a UserForm with drop downs for Department and Name.

I have an ever growing and changing list of Names in a sheet called ‘Staff Database’. This contains columns Department, Name and Hours.

In my UserForm I’d like to be able to select the department first in the drop down and then it only give me the list of people in that department based on the list in the Staff Database. Then when I click add record it needs to populate Name and Hours in the rows in the relevant sheet for each department (I’ve created one for each department).

I’d be very grateful for the help

 

[Fluff]

Hi & welcome to MrExcel.
How about

[COLOR=#ff0000]Dim UfDic As Object[/COLOR]

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.list = UfDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   
   Set UfDic = CreateObject("scripting.dictionary")
   With Sheets("Staff Database")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not UfDic.Exists(Cl.Value) Then UfDic.Add Cl.Value, CreateObject("scripting.dictionary")
         UfDic(Cl.Value)(Cl.Offset(, 1).Value) = Empty
      Next Cl
   End With
   Me.ComboBox1.list = UfDic.Keys
End Sub

The line in red must go at the very top of the module, before any code.