UserForm: Initialize a ListBox

[dlh]

I've created a UserForm with two ListBoxes. The list entries are pulled from a named range on my worksheet. Populating the list is working fine.

My problem is with creating a default value for the ListBox. I'm doing this by assigning the .Value field. This is baffling because sometimes has worked, and sometimes it has not, with the only difference that I can tell being seemingly irrelevant changes to the workbook (i.e, delete an unrelated sheet, etc.)

The first time I tried creating a stripped down version of my workbook to isolate the problem, the problem went away. The second time, I carefully removed pieces one at a time, testing at every step, and this time the problem didn't disappear.

My initialization code:



Private Sub UserForm_Initialize()
Dim MasterCompanyRange As Range
Dim MasterRegionRange As Range
Dim iCell As Range
' clear leftover values in output variables
CreateJobField_Company = ""
CreateJobField_Region = ""
CreateJobField_Number = 0
' fill company list
Set MasterCompanyRange = ThisWorkbook.Names("job_table").RefersToRange.Rows(1).Cells
For Each iCell In MasterCompanyRange
If iCell.Address = iCell.MergeArea.Cells(1, 1).Address Then
Me.CompanyList.AddItem iCell.Value
End If
Next iCell
' preselect 1st company in list
Me.CompanyList.Value = MasterCompanyRange.Cells(1, 1).Value
' fill region list
Set MasterRegionRange = ThisWorkbook.Names("job_table").RefersToRange.Cells(1, 1).Offset(1, 0) _
.Resize(1, ThisWorkbook.Names("region_count").RefersToRange.Value)
For Each iCell In MasterRegionRange
Me.RegionList.AddItem iCell.Value
Next iCell
' preselect 1st region in list
Me.RegionList.Value = MasterRegionRange.Cells(1, 1).Value
MsgBox "in UserForm_init, value to use as default = " & MasterRegionRange.Cells(1, 1).Value
MsgBox "in UserForm_init, Me.RegionList.Value = " & Me.RegionList.Value
End Sub


My stripped-down workbook illustrating the problem is enclosed. Explicit description of the symptom:

Open the workbook. Click the button "Add Job." After a couple MsgBoxes for debugging, the UserForm appears. Do not click in the ListBoxes (that would override the default). Type any number in the "Job Number" TextBox. Click the "Okay" CommandButton. My button-click-event procedure will then echo the 3 values that the UserForm collects.

The various MsgBoxes I've added for debugging show that the value intended to be used for default in the Region ListBox = "North" but for some reason ListBox.Value fails to retain this after the initialization procedure finishes. But the Region ListBox does show "North" being highlighted, which I don't think would happen without the .Value property being set.

I know that I can code a workaround-error-check in the unload procedure, but I'd like to understand why this isn't working as is.

Much gratitude for any enlightenment.

[mdmackillop]

Try


' preselect 1st region in list
Me.RegionList.Value = MasterRegionRange.Cells(1, 1).Text

[dlh]

Thank you for the response, mdmackillop.

Your rewrite does not solve the problem for me. But it does illustrate how perplexing this problem is.

I first tried making the switch from .Value to .Text in my full workbook. It didn't work; the problem persisted.

Next I opened the stripped down workbook that I have posted here. In the stripped down version, it did work.

Okay, that's reassuring. Repeat the test to be certain.

Next I closed both the full version and the stripped down version, reopened the stripped down version and again made the change. This time it did not work.

This was repeatable. The switch from .Value to .Text changes the outcome in the stripped down version only if my other workbook is not open.

This is just as confusing as when I previously made the problem disappear and reappear as I constructed the stripped down version. Making changes to aspects of the workbook completely unrelated to the UserForm changed my results!

Any further ideas?

[dlh]

Please forgive the extra post. After making some unrelated revisions to the non-stripped-down version of my workbook, the problem has gone away again.

But I'd still like to understand the "why." Has anyone got a clue what is the fundamental problem with the example workbook above?