Problem populating listbox

[Ark68]

Please consider this piece of code ...

Workbooks("Sports17.xlsm").Names.Add Name:="missing_all", RefersTo:=temp_ws.Range(temp_ws.Cells(rw_pr_start, 13), temp_ws.Cells(rw_pr_end, 16))
        
        
            With uf2_assess_sched
                .uf1_tb7.Value = ar
                .uf1_tb8.Value = pr
                .uf1_tb7.Locked = True
                .uf1_tb8.Locked = True
                .uf1_tb7.ForeColor = RGB(0, 52, 89)
                .uf1_tb8.ForeColor = RGB(0, 52, 89)
                If ar = 0 Then
                    .uf1_active.Value = False
                    .uf1_active.Locked = True
                Else
                    .uf1_active.Value = True
                    .uf1_active.Locked = False
                End If
                If pr = 0 Then
                    .uf1_passive.Value = False
                    .uf1_passive.Locked = True
                Else
                    .uf1_passive.Value = True
                    .uf1_passive.Locked = False
                End If
                With .uf1_listbox3
                    .Clear
                    .ForeColor = RGB(0, 52, 89)
                    .ColumnCount = 4
                    .ColumnWidths = "30;100;80;95"
                    .List = Range("missing_all").Value
                    .ListStyle = fmListStyleOption
                    '.Locked = True
                    .MultiSelect = fmMultiSelectSingle
                End With
            End With

temp_ws has been previously defined and set as a worksheet. The line in red creates a named range in workbook sports17.xlsm consisting of a range between M1 (rw_pr_start, 13), and P28 (rw_pr_end, 16) of worksheet ws_temp

The line in green is supposed to populate uf1_listbox3 with the 4 columns of data captured in the named range.

But the listbox is empty when it reaches the code. There are no errors. When I look up named range "missing_all" in sports17's name manager, the value is correct, and the reference is correct.

Is anyone able to help me correct this problem?

 

[Ark68]

I am one to understand that if help could have been offered, it would have been now. But this is a humble plea for any thoughts. It's become a significant barrier in the progress of my project. Usually in these cases where I stump the pros, it's something simple that only a second set of observant eyes can see.

Any thoughts to pursue would be most appreciated.

 

[MickG]

Apart from the Workbook name and list box name this the same code, That works:-

Dim temp_ws As Worksheet, R As Range
Set temp_ws = Sheets("Sheet6")
Dim rw_pr_start, rw_pr_end
rw_pr_start = 1
rw_pr_end = 10
ListBox1.Clear
ListBox1.ColumnCount = 4

Workbooks("Wed 1_2_17.xlsm").Names.Add Name:="missing_all", RefersTo:=temp_ws.Range(temp_ws.Cells(rw_pr_start, 13), temp_ws.Cells(rw_pr_end, 16))

Set R = Workbooks("Wed 1_2_17.xlsm").Names("missing_all").RefersToRange
MsgBox R.Address(external:=True)

ListBox1.List = R.Value

This also works :-

Dim sht As Worksheet
Set sht = Sheets("Sheet6")
sht.Range("A1:D10").Name = "MG"
ListBox1.ColumnCount = 4
ListBox1.Clear
ListBox1.List = sht.Range("MG").Value

 

[Ark68]

Hi MickG .... thank you so much for your support with this problem.

I have only tried the first suggestion so far, but am having the same problem. The range is being properly defined (as per the message box), and the other properties (ie clear) of the listbox are being applied, but the listbox still isn't populating with the list.

One thing I forgot to mention, and I'm suspicious now, is that there are two other forms open at the same time. uf2_listbox3 isn't the forward form though. uf2_listbox3 is on userform "uf2_process_sched". (I have a with statement in my code thinking that would but the focus of commands there). This code is called from a button click on userform "group_1". Is this maybe what is causing me grief? WHat I am trying to do seems pretty straight forward and simple. But oddly, the other controls on this form are being updated from this code, just not the listbox.

 

[Ark68]

Update: I closed userform "group_1" prior to updating the controls on "uf2_process_sched". This made no improvement.

 

[MickG]

Have made sure you have not got a range in the "Rowsource" property ?????
Other than that :-
I think you need to start a new Trial listbox with new named range get that working then try and transfer that to your original problem !!!

 

[Ark68]

OK ....
I beat you on the "RowSource" potential ... I did check that early on in Properties - "uf2_listbox3" and it was empty.

Here are my efforts at isolating a problem:

1) Without changing the control, I made a new named range ("mrexcel") to define the same area. I applied it to the list property (via the r variable) of "uf2_listbox3" and the box did not populate.
2) With userform uf2_process_sched, I moved the failing uf2_listbox 3 control to another part of the form, outside of any frames. I did not change anything with this control. I created a new listbox by copying uf2_listbox3 and pasting it to the original location on the userform, and named it uf2_mrexcel. I copied the code to populate uf2_listbox3, commented that code out, and pasted it to the module changing any references from uf2_listbox3 to uf2_mrexcel.

I ran the code, and indeed, uf2_mrexcel DID populate!

I then put the original code back into service for populating uf2_listbox3 (with the named range referring to "mrexcel", and ran the code again. uf2_mrexcel populated, but uf2_listbox3 did not. I assume there is no problem with the named range, as both named ranges mrexcel and missing_all populated uf2_

Does this help isolate the problem? I would really appreciate not having to lose uf2_listbox3 as so much of my other code relies on it.

 

[Norie]

Do you need the named range?

 

[Ark68]

Do you need the named range?

I don't know. I did it this way because the list is dynamic. Perhaps just assigning the list property to the range will be all thats needed?

I tried this instead ....

.List = Range(temp_ws.Cells(rw_pr_start, 13), temp_ws.Cells(rw_pr_end, 16))

, but it leaves me with an error.
(Could not set the list property. Invalid property array index.)

 

[Norie]

To be honest, unless you are using the named range elsewhere, I don't see any point in it.

Does this work?

Dim rngMissing_All As Range


    With temp_ws
        Set rngMissing_All = .Range(.Cells(rw_pr_start, 13), .Cells(rw_pr_end, 16))
    End With
        
            With uf2_assess_sched
                .uf1_tb7.Value = ar
                .uf1_tb8.Value = pr
                .uf1_tb7.Locked = True
                .uf1_tb8.Locked = True
                .uf1_tb7.ForeColor = RGB(0, 52, 89)
                .uf1_tb8.ForeColor = RGB(0, 52, 89)
                If ar = 0 Then
                    .uf1_active.Value = False
                    .uf1_active.Locked = True
                Else
                    .uf1_active.Value = True
                    .uf1_active.Locked = False
                End If
                If pr = 0 Then
                    .uf1_passive.Value = False
                    .uf1_passive.Locked = True
                Else
                    .uf1_passive.Value = True
                    .uf1_passive.Locked = False
                End If
                With .uf1_listbox3
                    .ForeColor = RGB(0, 52, 89)
                    .ColumnCount = 4
                    .ColumnWidths = "30;100;80;95"
                    .List = rngMissing_All.Value
                    .ListStyle = fmListStyleOption
                    '.Locked = True
                    .MultiSelect = fmMultiSelectSingle
                End With
            End With