Listbox rowsorce with Named Variable's

[Bernieg]

Hi Guys

I use the code below to refresh listboxs in forms.
The code works well when the the sheets are named, in this case "Change_Controll" .

What i would like to do is use a named variable called "Controll_sht"
Can't get it to work though.

.RowSource = Controll_sht!(a2:L" & last_Row)

Thanking you in advance

Bernie

Sub Refresh_Data()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Change_Controll")

Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 12
.ColumnWidths = "25,50,70,70,70,25,70,25,30,70,70,70"
If last_Row = 1 Then

.RowSource = "Change_Controll!A2:L2" ' Sheet called Change_Controll
Else
.RowSource = "Change_Controll!a2:L" & last_Row
End With
End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Sub Refresh_Data()
    Dim sh              As Worksheet
    Dim last_Row        As Long
    Dim Controll_sht    As String
    
    Set sh = ThisWorkbook.Sheets("Change_Controll")
    
    Controll_sht = sh.Name
    
    last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
    If last_Row = 1 Then last_Row = 2
    
    With Me.ListBox1
        .ColumnHeads = True
        .ColumnCount = 12
        .ColumnWidths = "25,50,70,70,70,25,70,25,30,70,70,70"
        .RowSource = "'" & Controll_sht & "'!a2:L" & last_Row
    End With
    
End Sub

Dave