Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to combobox- forget the blanks?
I saw a post not long ago showing how to populate a combobox from vba, using
a range of cells but ignoring the blanks. Excellent just what i needed, However, i can't get mine working right, I get permission denied on my other userform which i use to call the next, using the userform.show at the end of my sub. This only happens when I add a loop on the other form Here's what I saw.. I wonder if somebody could explain the for each line. Or post another solution that may help me solve my problem TIA Chris Private Sub Userform_Initialize() Dim sh as Worksheet Dim cell as Range set sh = Worksheet("sheet1") for each cell in sh.range(sh.cells(2,5),sh.cells(rows.count,5).End( xlup)) if not isempty(cell) then combobox1.AddItem cell end if Next End sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to combobox- forget the blanks?
No idea what you problem is, or where this code is located, but try
Private Sub Fill() Dim Tx As String For rwIndex = 25 To 35 With Worksheets("Sheet1").Cells(rwIndex, 61) If .Value = "" Then Else Tx = .Value Userform1.ComboBox1.AddItem Tx End If End With Next rwIndex End Sub change userform1 to represent the userform name of the userform that holds the combobox. -- Regards, Tom Ogilvy Chris A wrote in message ... OK, so i pondered and looked, came up with this, great for the first combobox on the form but i have 12 and whenever i try to add some sort of looping through the comboxes to fill the same way I get my userform.show giving permission denied. Am i missing the point? Private Sub Fill() Dim Tx As String For rwIndex = 25 To 35 With Worksheets("Sheet1").Cells(rwIndex, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With Next rwIndex End Sub I feel this should be a little more referenced or maybe in the wrong place. I'm learning lots, most is good, but BOY what a learning curve. Thanks for looking. Chris "Chris A" wrote in message s.com... I saw a post not long ago showing how to populate a combobox from vba, using a range of cells but ignoring the blanks. Excellent just what i needed, However, i can't get mine working right, I get permission denied on my other userform which i use to call the next, using the userform.show at the end of my sub. This only happens when I add a loop on the other form Here's what I saw.. I wonder if somebody could explain the for each line. Or post another solution that may help me solve my problem TIA Chris Private Sub Userform_Initialize() Dim sh as Worksheet Dim cell as Range set sh = Worksheet("sheet1") for each cell in sh.range(sh.cells(2,5),sh.cells(rows.count,5).End( xlup)) if not isempty(cell) then combobox1.AddItem cell end if Next End sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to combobox- forget the blanks?
Cheers Tom, It appears that i'm a dimbat, I forgot to remove the old
references from the rawsource property, it's amazing what you can miss!? Mind you having several user forms it would be wise to add a more difinitive reference. thanks again. Chris "Tom Ogilvy" wrote in message ... No idea what you problem is, or where this code is located, but try Private Sub Fill() Dim Tx As String For rwIndex = 25 To 35 With Worksheets("Sheet1").Cells(rwIndex, 61) If .Value = "" Then Else Tx = .Value Userform1.ComboBox1.AddItem Tx End If End With Next rwIndex End Sub change userform1 to represent the userform name of the userform that holds the combobox. -- Regards, Tom Ogilvy Chris A wrote in message ... OK, so i pondered and looked, came up with this, great for the first combobox on the form but i have 12 and whenever i try to add some sort of looping through the comboxes to fill the same way I get my userform.show giving permission denied. Am i missing the point? Private Sub Fill() Dim Tx As String For rwIndex = 25 To 35 With Worksheets("Sheet1").Cells(rwIndex, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With Next rwIndex End Sub I feel this should be a little more referenced or maybe in the wrong place. I'm learning lots, most is good, but BOY what a learning curve. Thanks for looking. Chris "Chris A" wrote in message s.com... I saw a post not long ago showing how to populate a combobox from vba, using a range of cells but ignoring the blanks. Excellent just what i needed, However, i can't get mine working right, I get permission denied on my other userform which i use to call the next, using the userform.show at the end of my sub. This only happens when I add a loop on the other form Here's what I saw.. I wonder if somebody could explain the for each line. Or post another solution that may help me solve my problem TIA Chris Private Sub Userform_Initialize() Dim sh as Worksheet Dim cell as Range set sh = Worksheet("sheet1") for each cell in sh.range(sh.cells(2,5),sh.cells(rows.count,5).End( xlup)) if not isempty(cell) then combobox1.AddItem cell end if Next End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
Charts forget range or data; fixed by manual alteration then Undo | Charts and Charting in Excel | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Show one range in a combobox and write the 2nd range! | Excel Programming | |||
ComboBox.Value To Range | Excel Programming |