Userform Combobox Rowsource not populating

[jollie]

Hi,

I'm a basic VBA user needing help. I have just upgraded to MS Excel 2010 and some Userforms from 2003 are now not working.

When I open the Userform, the Combobox is not populated with the Rowsource. It used to work fine!

The Rowsource property is just the Named Range that is in the worksheet. This is not down through code, just the properties window.

Is there a reason it would no longer be working?

Thanks

 

[RoryA]

How is the named range defined? Is it a static address, or dynamic?

 

[jollie]

The named ranges are dynamic in that a macro runs before the userform is initialised to create the ranges: its some very old code:

<CODE>
 
Worksheets("Customer List").Select
 
Range("A2").Select
 
While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
Wend
 
ActiveCell.Select
 
rwnb = ActiveCell.Row
Range("A2", Cells(rwnb, 1)).Name = "Customer_List"

<CODE>

There is no code in the Userform Combobox to add Customer_List as the Rowsource. It is simply defined in the properties window as being Customer_List.

Userform is called Start_CustStatement
Combobox is called CustomerBox

Thanks for your help, much appreciated.
</CODE></CODE>

 

[RoryA]

I can't see anything immediately wrong with that - have you checked in Name Manager to see if the ranges are defined correctly?

 

[jollie]

Hi,

I checked in name manager and they seem to be defined correctly.

Name Customer_List
Refers to ='Customer List'$A$2:$A$142
Scope Customer List

Is is becayse the scope is for the worksheet only? The macro is not run from this worksheet.

There are no other ranges by that name in the workbook.

Still stumped.

Thanks,

 

[RoryA]

That's odd because your code should be creating a name scoped to the workbook. What happens if you delete the name and then run the code again?

 

[jollie]

Wait, I found something! The combobox WAS populated with the list (stupid me), but in the code I force it to show ""

Sub Call_Start_CustStatement()
Call Create_Customer_List
Application.ScreenUpdating = False
    Start_CustStatement.CustNoBox.Value = ""
    Start_CustStatement.CustomerBox.Value = ""
    Start_CustStatement.BusinessBox.Value = ""
    Start_CustStatement.Show
End Sub

What the problem is, is that the drop down arrow is not working to show me the list below the combo box. If I make an entry in the box, I can move up and down the list with arrow keys - but I had been checking with the down arrow to see that the list is there before making any entries. The drop down arrow is not working to show a scroll list under the combobox to choose an entry from.

Surely this is something much more simple I need to fix in the properties window so that the drop down arrow will show the list (I have ListRows set to 8 normally)?

Thanks for your help.

 

[RoryA]

So you click the dropdown arrow and nothing happens at all?

 

[jollie]

Yep, nothing happens at all

 

[RoryA]

I don't think I've ever seen that before.
Any chance you could post the workbook somewhere for inspection?