Dynamic range through name manager on userform listbox

Question

I have a userform with listbox. In the userform the user required to add values in several textboxs, the values automatically are added to an excel table, and the listbox supposed to show the specific values which were added by the user. I have tried to use with Dynamic range through the manager name, and set the rowsource of the listbox to contain the dynamic range, but the listbox is empty and doesn't show any value. Please your hlp to understand what i'm doing wrong?

The dynamic range is:

 Dyn_CurrentCA= =OFFSET(CA_list!$F$4,lists!$V$10,0,lists!$V$9,6)

This is my code:

Public Dep_CA As Integer
Public Target_CA As Integer

Private Sub CB_Add_Click()

Target_CA = Sheets("lists").Range("V8").Value + 1

If T_AuditDate.Value = "" Or CB_Grade.Value = "" Or 
T_CAnum.Value = "" Or CB_Subject.Value = "" Or 
T_Findings.Value = "" Then
 MsgBox "Please fill Audit Date and Audit Result!", 
vbRetryCancel + vbCritical, "Data is missing"
Else
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 0).Value = Target_CA
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 1).Value = L_Dep.Caption
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 2).Value = T_AuditDate.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 3).Value = L_Contact.Caption
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 4).Value = L_Manager.Caption
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 5).Value = T_CAnum.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 6).Value = CB_Subject.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 7).Value = CB_SubSubject.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 8).Value = T_Findings.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 9).Value = T_DD.Value
 Sheets("CA_list").Range("CA_Start").Offset(Target_CA, 10).Value = CB_Status.Value

Call clear_CA

Dep_CA = Dep_CA + 1
Sheets("lists").Range("V9").Value = Dep_CA
ListBox1.RowSource = Dyn_CurrentCA


End If
End Sub

Private Sub UserForm_Initialize()

Dep_CA = 0 'initialize no. of lines to 0
Sheets("lists").Range("V9").Value = Dep_CA

CurrentRaw = Sheets("lists").Range("V3").Value
Sheets("lists").Range("V10").Value = Sheets("lists").Range("V8").Value + 1
L_Dep.Caption = Sheets("lists").Range("V5").Value
L_Site.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 4).Value
L_PQ.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 5).Value
L_PYear.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 6).Value
L_Auditor.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 7).Value
L_Contact.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 2).Value
L_Manager.Caption = Sheets("Internal_Plan").Range("A_Start").Offset(CurrentRaw, 3).Value
Call clear_CA


With ListBox1
.ColumnWidths = "40;60;60;260;50;40"
.ColumnCount = 6
.RowSource = Dyn_CurrentCA
.ColumnHeads = True

End With

End Sub


Sub clear_CA()

With Update_Results 'name of userform

CB_Subject.Value = ""
CB_SubSubject.Value = ""
T_CAnum.Value = ""
T_DD.Value = ""
CB_Status.Value = "Open"
T_Findings.Value = ""

End With

End Sub

This is the userform with listbox:

QHarr QHarr · Accepted Answer · 2018-08-26T11:58:23

Try with using the address of the dynamic range. You need to add the sheet name as well.

ListBox1.RowSource = Worksheets("CA_list").Range("Dyn_CurrentCA").Address(external:=True)

For ActiveX

ListBox1.ListFillRange = Worksheets("CA_list").Range("Dyn_CurrentCA").Address(external:=True)

Dynamic range through name manager on userform listbox

1 Answers