Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Hell!!!
Hi , I am having a terrible time getting a combobox to fill up now
that I have moved everything from a WorkSheet to a UserForm When they were on a worksheet everything was fine as I could just use ComBoBox.ListFillRange = ("A1:A10) As long as my list was on the same sheet. I never got this to work tryimg to load a list from another sheet ( and I tried the examples from here but no luck, but I could live with that) So I changed my bad design of lots of sheets to just a few and a master data sheet so I could read and write from it...and life was good So I try to go a step further and put all the controls on a nice user form from a worksheet and spend hours because you can only drag and drop them one at a time from a worksheet to a form and rename them all. But thats OK too....cause I am going to be almost done I think Now I can only fill a combobox by changing its properties in the form. I tried these examples from an earlier post here and none of them work. I have to show 2 columns in some and now this does not work anymore because now the comboboxes are on a form instead of a worksheet where I could get them to work either by changing object properties or using VBA Is there something so insanely stupid I am not seeing from lack of sleep or can you really fill a combobox on a form using VBA code instead of clicking on every one of these $%&^*(# things to get them to fill up. ThanksInAdvanceDaveBaranas Using Excel 2002 ------------------------------------------------------------------------------------------------------------------- ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value or ComboBox1.RowSource = "Sheet1!A1:A10" or Dim cell As Range For Each cell In Worksheets("Sheet1").Range("A1:A10") ComboBox1.AddItem cell.Value Next cell For more info on comboboxes/listboxes see www.rubbershoe.com/listbox.htm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Hell!!!
"Dave Baranas" wrote in message ... Hi , I am having a terrible time getting a combobox to fill up now that I have moved everything from a WorkSheet to a UserForm When they were on a worksheet everything was fine as I could just use ComBoBox.ListFillRange = ("A1:A10) As long as my list was on the same sheet. I never got this to work tryimg to load a list from another sheet ( and I tried the examples from here but no luck, but I could live with that) So I changed my bad design of lots of sheets to just a few and a master data sheet so I could read and write from it...and life was good So I try to go a step further and put all the controls on a nice user form from a worksheet and spend hours because you can only drag and drop them one at a time from a worksheet to a form and rename them all. But thats OK too....cause I am going to be almost done I think Now I can only fill a combobox by changing its properties in the form. I tried these examples from an earlier post here and none of them work. I have to show 2 columns in some and now this does not work anymore because now the comboboxes are on a form instead of a worksheet where I could get them to work either by changing object properties or using VBA Is there something so insanely stupid I am not seeing from lack of sleep or can you really fill a combobox on a form using VBA code instead of clicking on every one of these $%&^*(# things to get them to fill up. Sure suppose you want to use the values in column 1 rows 1 to 8 Dim Rownum As Long, n As Long Rownum = 8 ComboBox1.Clear For n = 1 To Rownum With ActiveSheet ComboBox1.AddItem .Cells(n, 1) End With Next n ComboBox1.ListIndex = 0 Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Hell!!!
'pls try this
'Create sheet name = mySheet 'insert this code to userform with ComboBox1 Private Sub UserForm_Initialize() On Error GoTo error_hen 'Worksheets("mySheet").Select With Worksheets("mySheet").Range("A:A") 'attention! name of the sheet, range Set c = .Find(What:="*", LookIn:=xlValue) ' "*" stands for every not empty If Not c Is Nothing Then firstAddress = c.Address Do Me.ComboBox1.AddItem c.Value Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Exit Sub error_hen: MsgBox "Error !" End Sub '############################## 'mailto:R_K_Gajda@ poczta.fm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Hell!!!
The simple code you show should work:
ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value or ComboBox1.RowSource = "Sheet1!A1:A10" or Dim cell As Range For Each cell In Worksheets("Sheet1").Range("A1:A10") ComboBox1.AddItem cell.Value Next cell You should put one of these approaches in the initialize event of the userform. By the way, copying the controls from a worksheet to a userform doesn't seem necessary. You can just use the controls from the control toolbox and place them on the form in the VBE - this takes much less time, even if you have to change a few properties. When specifing the listfillrange or the rowsource, include the sheet name: Sheet1!A1:A10 this avoids the problems you were having. For a multicolumn combobox or listbox, set the columncount property. -- Regards, Tom Ogilvy "Dave Baranas" wrote in message ... Hi , I am having a terrible time getting a combobox to fill up now that I have moved everything from a WorkSheet to a UserForm When they were on a worksheet everything was fine as I could just use ComBoBox.ListFillRange = ("A1:A10) As long as my list was on the same sheet. I never got this to work tryimg to load a list from another sheet ( and I tried the examples from here but no luck, but I could live with that) So I changed my bad design of lots of sheets to just a few and a master data sheet so I could read and write from it...and life was good So I try to go a step further and put all the controls on a nice user form from a worksheet and spend hours because you can only drag and drop them one at a time from a worksheet to a form and rename them all. But thats OK too....cause I am going to be almost done I think Now I can only fill a combobox by changing its properties in the form. I tried these examples from an earlier post here and none of them work. I have to show 2 columns in some and now this does not work anymore because now the comboboxes are on a form instead of a worksheet where I could get them to work either by changing object properties or using VBA Is there something so insanely stupid I am not seeing from lack of sleep or can you really fill a combobox on a form using VBA code instead of clicking on every one of these $%&^*(# things to get them to fill up. ThanksInAdvanceDaveBaranas Using Excel 2002 -------------------------------------------------------------------------- ----------------------------------------- ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value or ComboBox1.RowSource = "Sheet1!A1:A10" or Dim cell As Range For Each cell In Worksheets("Sheet1").Range("A1:A10") ComboBox1.AddItem cell.Value Next cell For more info on comboboxes/listboxes see www.rubbershoe.com/listbox.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Hell!!!
Yes putting this into the initialize event worked.
Thanks everyone! On Fri, 15 Aug 2003 09:02:32 -0400, "Tom Ogilvy" wrote: The simple code you show should work: ComboBox1.List = Worksheets("Sheet1").Range("A1:A10").Value or ComboBox1.RowSource = "Sheet1!A1:A10" or Dim cell As Range For Each cell In Worksheets("Sheet1").Range("A1:A10") ComboBox1.AddItem cell.Value Next cell You should put one of these approaches in the initialize event of the userform. By the way, copying the controls from a worksheet to a userform doesn't seem necessary. You can just use the controls from the control toolbox and place them on the form in the VBE - this takes much less time, even if you have to change a few properties. Its just that I had all my controls on sheets and then wanted to get them off the sheets and onto a form . I had over 100 of them so it was a pain to drag them over 1 at a time. looking back at least I know never to do that again!! When specifing the listfillrange or the rowsource, include the sheet name: Sheet1!A1:A10 this avoids the problems you were having. For a multicolumn combobox or listbox, set the columncount property. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Petty cash hell | Excel Discussion (Misc queries) | |||
Pls tell me how in hell am I to ask a technical question in 4 wor | New Users to Excel | |||
Hyperlink hell | Excel Discussion (Misc queries) | |||
Excel ADO.Net Hell :S | Excel Programming |