Simple Dynamic UserForm

Question

I want to create ComboBox that list the not empty values in the dynamically changed range from C5 to last not empty column in the row "C". Thanks to user3598756 I build below solution but I get the error "Permission denied" when using both code blocks: ComboBox1.RowSource and ComboBox.List.

Private Sub UserForm_Initialize()
    Dim wsRoadmap As Worksheet
    
    Set wsRoadmap = Sheets("Roadmap")
    
    With wsRoadmap
        ComboBox1.RowSource = .Range("C5", .Range("DJ5").End(xlToLeft)).Address
    End With
    
    With wsRoadmap
        ComboBox1.List = .Range("C5", .Range("DJ5").End(xlToLeft)).Value
    End With

End Sub

Answer 1

if you want to add items to combobox from columns then use this code -

Option Explicit

Private Sub UserForm_Initialize()

Dim TCol As Long, CCol As Long
Dim wsRoadmap As Worksheet

Set wsRoadmap = Sheets("Roadmap")

TCol = wsRoadmap.Cells(5, Columns.Count).End(xlToLeft).Column

Me.ComboBox1.Clear

'loop from column C to the last used column of 5th row
For CCol = 3 To TCol
    
    If VBA.Trim(wsRoadmap.Cells(5, CCol).Value) <> "" Then
    
        Me.ComboBox1.AddItem wsRoadmap.Cells(5, CCol).Value
    
    End If
    
Next CCol

End Sub

but if you want to add items to combobox from rows then use this code-

Private Sub UserForm_Initialize()

Dim TRow As Long, CRow As Long
Dim wsRoadmap As Worksheet

Set wsRoadmap = Sheets("Roadmap")

TRow = wsRoadmap.Cells(Rows.Count, 3).End(xlUp).Row

Me.ComboBox1.Clear

For CRow = 5 To TRow
    
    If VBA.Trim(wsRoadmap.Cells(CRow, 3).Value) <> "" Then
    
        Me.ComboBox1.AddItem wsRoadmap.Cells(CRow, 3).Value
    
    End If
    
Next CRow

End Sub