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

 

[danners430]

Hi ExcelRytis,

I'm not entirely sure why it isn't working, but you could try simply using the ComboBox.AddItem method

 

[ExcelRytis]

@MickG hope you don't mind me tagging you, but I've seen your replies on ComboBoxes always worked...

 

[ExcelRytis]

Not sure how I can use that? Can you give me an example please?

 

[ExcelRytis]

Please find my userform sample attached...

https://1drv.ms/x/s!Aoup4xJug-C1kQfK6jzuf-2TyuGI

I am trying to implement using method in the link below:

http://www.contextures.com/exceluserformdependentcomboboxes.html

 

[danners430]

For i = 0 To LastRow
    
    If Rows(i).Hidden = False Then ComboBox.AddItem Cells(i, 1)


Next i

 

[ExcelRytis]

Hi Danners,

would you kindly demonstrate where and how do i have to place this in my code for it to work?

 

[danners430]

Hi ExcelRytis,

Unfortunately I can't access your link to download the document, as filesharing sites are blocked on our domain. I can download it later once I'm at home and take a closer look there. Just to check though, you've set the RowSource for the combobox?

 

[MickG]

Why are you not using the "Rowsource" in column "G". ???

 

[ExcelRytis]

I guess because I'm not really understanding the concept of list in a list.... That's mainly why i struggle