asked on

Excel VBA combobox update problem

have a form with a combobox list box that gets updated with the program. It's not...

The named range is updating properly, but the form won't... here's my bits of code:

Worksheets("1stontape").Names.Add Name:="firstlist", RefersToR1C1:="=R1C1:R" & maxrowlist + 1 & "C1"

thats for updating the name range..

the rowsource IS set to firstlist. If I manually check the range, it IS working... but my form usually don't display it.... if I play around w/it enough, I might get lucky, but...

thanks!

Guy Hengel [angelIII / a3]

please note that there is an MSOffice Topic area, where you should get more experts for that problem:
https://www.experts-exchange.com/jsp/qList.jsp?ta=msoffice

Now, how/when do you update your forms' combobox?

CHeers

starl

ASKER

I know angel... but this is a VBA prob and there's no real section for VBA. Though there is some knowledge of VBA in Office I hoped to get more here.

... update the combobox... well, it reads the listing off a named range on a sheet.

Brian Mulder

Hi starl, you provided the update of the named range, but is the form loaded and updated also?

i mean is there also a load list procedure to get the values in the combobox? or is that done at design time

:O)Bruintje

starl

ASKER

*sigh* my ignorance and delving into something I don't knwo 100% is showing...

I've created a form in the editor. There is code behind that form. The part I'm having problems with is a combobox. It gets its list from a "rowsource" which, in this case, is a dynamic named range. I have other comboboxes on the form, but they are static - they show the info no problem.

Even if I restart the program and there IS info in the range, the combobox will not necessarily reflect this.

shouldn't it at least work ALL THE TIME if I restart the program? I've either done something wrong or there's a bug/error somewhere.

btw, excel 2000

Brian Mulder

Hi starl, ok i did this

-put a form in the editor
-a combox on it
-then used this code to fill the box

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 0 To 10
ComboBox1.AddItem i * i, i
Next i
End Sub

-instead of the values i used with i you can loop through the named range to fill the box

if you need any help on that just ask....

HTH:O)Bruintje

starl

ASKER

ok.. the named range is "firstlist" and the size will vary with time... how to work with it??

Brian Mulder

:O) i had already pulled the plug on excel

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 0 To Worksheets(1).Range("firstlist").Count - 1
ComboBox1.AddItem Range("firstlist").Value(i + 1, 1), i
Next i
End Sub

HTH:O)Bruintje

ASKER CERTIFIED SOLUTION

roverm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

starl

ASKER

Ha! thanks rover - that fixed it.. I did wonder why the namedrange had that extra line in the excel list of definitions. THANK YOU!

and I WAS missing the refresh. the last piece of the puzzle - thank you.

roverm

Thanks starl! Glad I could help!

D'Mzzl!
RoverM