ComboBox List dependent on a list

[ExcelRytis]

Hi there,

please help me understand why my cboLnName appears empty in the userform and help me fix it?

Private Sub cboLnName_AfterUpdate()On Error Resume Next
Dim ws As Worksheet
Dim cPart As Range
Set ws = Worksheets("LookupData")


Me.txtAllJobs.Value = ""
Me.txtAllJobs.RowSource = ""


With ws
   .Range("CritLnName").Cells(2, 1).Value _
      = Me.cboLnName.Value
   .Columns("C:D").AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=.Range("CritLnName"), _
      CopyToRange:=.Range("ExtJobName"), _
      Unique:=False
End With


'redefine the static named range
ThisWorkbook.Names.Add Name:="LnSelList", _
  RefersTo:="=" & ws.Name & "!" & _
  ws.Range("LnSelCatList").Address


Me.txtAllJobs.RowSource = "LnSelCatList"


End Sub


Private Sub Reset_Click()
Unload Me
performa.Show
End Sub


Private Sub submit_Click()
Dim nextrow As Range
'check for values
If Me.txtDates.Value = "" Or Me.txtOperators.Value = "" Or Me.txtAllJobs.Value = "" Or Me.txtQuantity.Value = "" Then
MsgBox "Please ensure all fields contain a value"
Exit Sub
End If
'find the next blank row
Set nextrow = Sheet4.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
'send the data to the database
With nextrow
.Value = Me.txtDates.Value
.Offset(0, 1).Value = Me.txtOperators.Value
.Offset(0, 2).Value = Me.txtAllJobs.Value
.Offset(0, 3).Value = Me.txtQuantity.Value
.Offset(0, 4).Value = Me.txtTime.Value
.Offset(0, 5).Value = Me.txtTotalTime.Value
End With
'update the worksheet
Application.ScreenUpdating = True
'clear the values
With Me
.txtAllJobs.Value = ""
.txtQuantity.Value = ""
.txtTime.Value = ""
.txtTotalTime.Value = ""
End With
'give the user the thumbs up
MsgBox "The data has been sent to the database"
End Sub


Private Sub Quit_Click()
'close the userform
Unload Me
End Sub


Private Sub txtDates_Change()
txtDates.Value = Format(txtDates.Value, "dd/mmm/yyyy")
End Sub


Private Sub txtQuantity_AfterUpdate()
'check for values and datatype
If Not IsNumeric(Me.txtQuantity.Value) Then
MsgBox "Please make sure all data is correct"
Me.txtQuantity.Value = ""
Exit Sub
End If
'send the values to the worksheet
With Sheet4
.Range("J5").Value = Me.txtDates.Value
.Range("K5").Value = Me.txtOperators.Value
.Range("L5").Value = Me.txtAllJobs.Value
.Range("M5").Value = Me.txtQuantity.Value
End With
'retrieve the results
With Me
.txtTime.Value = Sheet4.Range("N5").Value
.txtTime.Value = Format(Me.txtTime.Value, "General Number")
.txtTotalTime.Value = Sheet4.Range("O5").Value
.txtTotalTime.Value = Format(Me.txtTotalTime.Value, "General Number")
End With
End Sub

 

[ExcelRytis]

Okay so now I used the LnList name as the rowsource and changed it to column G, however now it doesn't automatically find all the jobs for that. What am I missing now?

 

[MickG]

You can either use the "RowSource" in column "G".

NB:- Right Click sheet Tab , select "View Code" , click "Ctrl + R" to see the Properties window in code module.
In properties window , Double click "performas" to see Userform Layout.
Right click 3rd Combobox, Select "Properties" to see its Properties Window.
Scroll down to "RowSource" and enter :- G2:G8
The "RowSource" should now be set for "cboLnName"



Or Place this code in the Userform "Performas" code module.
The data will load when the Userform is opened.

Private Sub UserForm_Initialize()
Dim Rng As Range, Dn As Range, n As Long
Set Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        .Item(Dn.Value) = Empty
    Next
cboLnName.List = Application.Transpose(.Keys)
End With
End Sub

 

[ExcelRytis]

You've pointed me in the right direction, and it now works! thanks!

 

[MickG]

You're welcome