ComboBox,select case all working except one column not showing

[tynawg]

Hello,
I have used some code from the net to create a dynamic data range for selection from the combo box. It all works well except one column. The column title displays in the first combo-box but the range does not display in the second? It is called the exact same way as all the rest and i have checked the Name Manage and it refers to the correct location and range. I have checked font, spelling and anything else that i could think of but have not found a solution.
Here's the code,

The title that shows is Locks but the range below will not display? Every other title and range display without fault?

Any help will be much appreciated.

Private Sub ComboBox1_Change()
Me.ComboBox2 = ""

Select Case Me.ComboBox1

        Case "Carpenter_Joiner"
    
            Me.ComboBox2.RowSource = "Carpenter_Joiner"
    
        Case "Concreting"

            Me.ComboBox2.RowSource = "Concreting"
        
        Case "General_Flooring"
    
            Me.ComboBox2.RowSource = "General_Flooring"
    
        Case "General_Labour_Costs"
    
            Me.ComboBox2.RowSource = "General_Labour_Costs"
        
        Case "Gyprock_Plastering"
    
            Me.ComboBox2.RowSource = "Gyprock_Plastering"
        
        Case "Irrigation"
    
             Me.ComboBox2.RowSource = "Irrigation"
    
        Case "Locks"
    
            Me.ComboBox2.RowSource = "Locks"
    
        Case "Mowing"

            Me.ComboBox2.RowSource = "Mowing"
        
        Case "Tiling"
    
            Me.ComboBox2.RowSource = "Tiling"
    
        Case "Travel_and_Overhead"
    
            Me.ComboBox2.RowSource = "Travel_and_Overhead"
        
        Case "Window_Cleaning"
    
            Me.ComboBox2.RowSource = "Window_Cleaning"
        
        Case "Painting"
    
            Me.ComboBox2.RowSource = "Painting"
        
End Select
    
End Sub



Private Sub CommandButton1_Click()

End Sub

Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastcolumn As Long
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets("sheet1")

    For I = 1 To lastcolumn
        With .Columns(I)
        lastrow = Sheet1.Cells(Rows.Count, I).End(xlUp).Row
            'With Range(Cells(1, I), Cells(lastrow, I))
            'Range(Cells(1, I), Cells(lastrow, I)).Select
            'Selection.CreateNames Top:=True
            'End With
            Range(Cells(1, I), Cells(lastrow, I)).CreateNames Top:=True
        End With
    Next I
End With

Me.ComboBox1.RowSource = "Trades"

            
End Sub

 

[dmt32]

Hi,
welcome to forum

Untested but see if this update to your codes will do what you want

Dim arr() As Variant
Private Sub ComboBox1_Change()
    Dim Index As Integer
    Index = Me.ComboBox1.ListIndex
     With Me.ComboBox2
      If Index = -1 Then .RowSource = "": Exit Sub
        .RowSource = arr(Index + 1)
    End With
End Sub


Private Sub UserForm_Initialize()
    Dim lastrow As Long, lastcolumn As Long
    Dim i As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets("Sheet1")
    
    lastcolumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ReDim arr(1 To lastcolumn)
        
    For i = 1 To lastcolumn
        lastrow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
        ws.Cells(1, i).Resize(lastrow).CreateNames Top:=True
        arr(i) = ws.Cells(1, i).Text
    Next i
        
    With Me.ComboBox1
        .RowSource = ""
        .List = arr
    End With
End Sub

note the variable arr which MUST sit at very TOP of the useforms code page OUTSIDE any procedure.

Code is dynamic & should automatically adjust to any changes in the lists on your worksheet (hopefully)

Dave

 

[tynawg]

Thank dmt32 for taking the time to reply and post a solution. It works, with one minor flaw I have observed so far. The second combobox does not clear, rather still has the previous selection showing leaving potential for a mistaken entry? As I have yet to try and understand your code i know not how to fix that. I had spent a lot of time on the code i posted and was becoming comfortable in where i need to make changes.
Regards,

 

[dmt32]

Thank dmt32 for taking the time to reply and post a solution. It works, with one minor flaw I have observed so far. The second combobox does not clear, rather still has the previous selection showing leaving potential for a mistaken entry? As I have yet to try and understand your code i know not how to fix that. I had spent a lot of time on the code i posted and was becoming comfortable in where i need to make changes.
Regards,

Hi,
try this update

Private Sub ComboBox1_Change()
    Dim Index As Integer
    Index = Me.ComboBox1.ListIndex
     With Me.ComboBox2
        .RowSource = ""
        .Text = ""
       If Index = -1 Then Exit Sub
        .RowSource = arr(Index + 1)
    End With
End Sub

I just added an array which is populated in your For Next Loop (which builds the named ranges) with the name of each column header.
This approach saves all that hard coding you were applying and makes code dynamic to allow for any name changes / additions in your lists.

Dave

 

[tynawg]

Bugger, I typed a full reply to thank you but clicked wrong button and lost it. Changes below. I will no doubt be posting more to continue this project. Awesome, and thank you Dave.

Dim arr() As Variant
Private Sub ComboBox1_Change()
    Dim Index As Integer
    Index = Me.ComboBox1.ListIndex
     With Me.ComboBox2
      If Index = -1 Then .RowSource = "": Exit Sub
        .RowSource = arr(Index + 1)
    End With
End Sub


Private Sub UserForm_Initialize()
    Dim lastrow As Long, lastcolumn As Long
    Dim i As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets("Sheet1")
    
    lastcolumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ReDim arr(1 To lastcolumn)
        
    For i = 1 To lastcolumn
        lastrow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
        ws.Cells(1, i).Resize(lastrow).CreateNames Top:=True
        arr(i) = ws.Cells(1, i).Text
    Next i
        
    With Me.ComboBox1
        .RowSource = ""
        .List = arr
    End With
End Sub

 

[dmt32]

welcome, glad suggestion resolved issue

Many thanks for feedback


Dave

 

[tynawg]

Hello anyone who may have further input.
My next part i hope for direction as to where I may research further is as follows.
Solved so far is creating a dynamic array of headings taken from an excel sheet from the first row which appear in the first combobox drop down menu for selection, next combobox (2) dropdown menu show the data in the column below each heading.
Next I am trying to find how to now have new comboboxs with data related to each item selected from combobox 2 to now appear and be displayed either after combobox 2 item is selected or the comboboxs are simply blank, but now get populated after box 2 data is selected. Also how I structure the data in the source file. At the moment it is simply containing the data for box 1 and 2. do i source from another worksheet? or specified columns?
Any help will be much appreciated.
I have been searching the last week but i think this is getting too specific for google.
Regards,

 

[dmt32]

looks like you are asking a new question & probably better to start new thread (You can add link to this thread) where likely get more responses.

Dave