Private Sub cboAssetGroup_Change()
Dim rng As Range, cell As Range
'Set cboSubGroup with list of SubGroup assets for given AssetGroup
cboSubGroup.Rowsource = ""
Set rng = Range(cboAssetGroup.Value & "_SubGroup")
cboSubGroup.Clear
For Each cell In rng
cboSubGroup.AddItem cell.Value
Next cell
End Sub

Private Sub cboSubGroup_Change()
Dim rng As Range, cell As Range
'Set cboSystem with list of System assets for given SubGroup
cboSystem.RowSource = ""
Set rng = Range(cboSubGroup.Value & "_System")
cboSystem.Clear
For Each cell In rng
cboSystem.AddItem cell.Value
Next cell
End Sub

In your initialize routine, you shouldn't need to loop to set your
rowsource:

for example
'Initialize Station
For Each c In Sheets("Station").Range("B2:B150")
If c.Text = "" Then
cboStation.RowSource = "Station!B2:B" & c.Row - 1
Exit For
End If
Next

replace with

'Initialize Station
With Sheets("Station").
set c = .Range(.Range("B2"),.Range("B2").End(xldown))
End with
cboStation.RowSource = c.Address(external:=True)

Repeat for the rest of your initializations as appropriate.

--
Regards,
Tom Ogilvy


"Little pete" wrote in message
...
Its a big ask but if some one could review my code and see where i am
going
wrong. ive mixed up quite a few bits of code to get this and you might
guess
that i am blaging most of it. but its fun to do.

hoping the code explains but if not here goes;
have a form where users select an item from one cbo which determines what
they view in the next cbo (total of 4 boxes).

errors that i am getting is on the last part
"Run-time error '2147467259 (80004005)"
unspecified error

Any help is wicked!!




Private Sub UserForm_Initialize()

'Intialize Room Number
txtRoomNumber.Value = ""

'Initialize Station
For Each c In Sheets("Station").Range("B2:B150")
If c.Text = "" Then
cboStation.RowSource = "Station!B2:B" & c.Row - 1
Exit For
End If
Next

'Initialize Asset Group
For Each d In Sheets("Hierarchy").Range("B2:B150")
If d.Text = "" Then
cboAssetGroup.RowSource = "Hierarchy!B2:B" & d.Row - 1
Exit For
End If
Next

'Initialize Sub Group
For Each e In Sheets("Hierarchy").Range("E2:E150")
If e.Text = "" Then
cboSubGroup.RowSource = "Hierarchy!E2:E" & e.Row - 1
Exit For
End If
Next

'Initialize System
For Each f In Sheets("Hierarchy").Range("H2:H150")
If f.Text = "" Then
cboSystem.RowSource = "Hierarchy!H2:H" & f.Row - 1
Exit For
End If
Next

'Initialize Sub System
For Each g In Sheets("Hierarchy").Range("K2:K150")
If g.Text = "" Then
cboSubSystem.RowSource = "Hierarchy!K2:K" & g.Row - 1
Exit For
End If
Next

cboStation.Value = ""
cboAssetGroup.Value = ""
cboSubGroup = ""
cboSystem.Value = ""
cboSubSystem.Value = ""
cboStation.SetFocus
End Sub


Private Sub cmdOk_Click()

ActiveWorkbook.Sheets("Asset Change").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Offset(0, 0) = cboStation.Value

ActiveCell.Offset(0, 1) = txtRoomNumber.Value

ActiveCell.Offset(0, 2) = cboAssetGroup.Value

ActiveCell.Offset(0, 3) = cboSubGroup.Value

ActiveCell.Offset(0, 4) = cboSystem.Value

ActiveCell.Offset(0, 5) = cboSubSystem.Value


Range("A1").Select

End Sub


Private Sub cmdCancel_Click()
Unload Me
End Sub


Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub


Private Sub cboAssetGroup_Change()
Dim rng As Range, cell As Range
'Set cboSubGroup with list of SubGroup assets for given AssetGroup
Set rng = Range(cboAssetGroup.Value & "_SubGroup")
cboSubGroup.Clear
For Each cell In rng
cboSubGroup.AddItem cell.Value
Next cell
End Sub

Private Sub cboSubGroup_Change()
Dim rng As Range, cell As Range
'Set cboSystem with list of System assets for given SubGroup
Set rng = Range(cboSubGroup.Value & "_System")
cboSystem.Clear
For Each cell In rng
cboSystem.AddItem cell.Value
Next cell
End Sub