Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
I use a few forms and i'm finding i have to cyle through controls, i've
given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
Chris,
I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
As I read his code, it would be more like this: (every value in every column
didn't go in every combobox) Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then controls("Combobo" & col-60).AddItem .Value End If End With Next rw Next col End Sub -- Regards, Tom Ogilvy Doug Glancy wrote in message ... Chris, I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
As I was bicycling across town after submitting my answer, it occurred to me
that I'd misread. Thanks, Tom. Also, "Combobo" in the code below, should read "ComboBox" Doug "Tom Ogilvy" wrote in message ... As I read his code, it would be more like this: (every value in every column didn't go in every combobox) Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then controls("Combobo" & col-60).AddItem .Value End If End With Next rw Next col End Sub -- Regards, Tom Ogilvy Doug Glancy wrote in message ... Chris, I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
Thanks for the correction.
As Doug said: controls("Combobo" & col-60).AddItem .Value should be controls("Combobox" & col-60).AddItem .Value Sorry about the typo. -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... As I was bicycling across town after submitting my answer, it occurred to me that I'd misread. Thanks, Tom. Also, "Combobo" in the code below, should read "ComboBox" Doug "Tom Ogilvy" wrote in message ... As I read his code, it would be more like this: (every value in every column didn't go in every combobox) Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then controls("Combobo" & col-60).AddItem .Value End If End With Next rw Next col End Sub -- Regards, Tom Ogilvy Doug Glancy wrote in message ... Chris, I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
It's easy when you know how hey!?
I get it now, I was struggling trying to increment the controls. After looking at this though I can't understand what the 'Dim ctl As Control' line is for, so i tried it commented out and it worked fine. I spotted the typo though so i'm guessing i must be getting better at this (at least let me think so) Thanks for the help. Chris A "Tom Ogilvy" wrote in message ... Thanks for the correction. As Doug said: controls("Combobo" & col-60).AddItem .Value should be controls("Combobox" & col-60).AddItem .Value Sorry about the typo. -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... As I was bicycling across town after submitting my answer, it occurred to me that I'd misread. Thanks, Tom. Also, "Combobo" in the code below, should read "ComboBox" Doug "Tom Ogilvy" wrote in message ... As I read his code, it would be more like this: (every value in every column didn't go in every combobox) Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then controls("Combobo" & col-60).AddItem .Value End If End With Next rw Next col End Sub -- Regards, Tom Ogilvy Doug Glancy wrote in message ... Chris, I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
This can't be the best way to do this
Dim ctl as Control
isn't necessary. -- Regards, Tom Ogilvy "Chris A" wrote in message s.com... It's easy when you know how hey!? I get it now, I was struggling trying to increment the controls. After looking at this though I can't understand what the 'Dim ctl As Control' line is for, so i tried it commented out and it worked fine. I spotted the typo though so i'm guessing i must be getting better at this (at least let me think so) Thanks for the help. Chris A "Tom Ogilvy" wrote in message ... Thanks for the correction. As Doug said: controls("Combobo" & col-60).AddItem .Value should be controls("Combobox" & col-60).AddItem .Value Sorry about the typo. -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... As I was bicycling across town after submitting my answer, it occurred to me that I'd misread. Thanks, Tom. Also, "Combobo" in the code below, should read "ComboBox" Doug "Tom Ogilvy" wrote in message ... As I read his code, it would be more like this: (every value in every column didn't go in every combobox) Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then controls("Combobo" & col-60).AddItem .Value End If End With Next rw Next col End Sub -- Regards, Tom Ogilvy Doug Glancy wrote in message ... Chris, I think this boils it down for you: Sub Fill() Dim Tx As String Dim rw, col As Long Dim ctl As Control For col = 61 To 72 For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, col) If .Value < "" Then For Each ctl In UserForm1.Controls If TypeOf ctl Is msforms.ComboBox Then 'assuming that you're doing this with all your comboboxes Tx = .Value ctl.AddItem Tx End If Next ctl End If End With Next rw Next col End Sub hth, Doug "Chris A" wrote in message s.com... I use a few forms and i'm finding i have to cyle through controls, i've given it a go and can't seem to find what i need, this works but again, i have a feeling it's not quite right. Sub Fill() Dim Tx As String Dim rw As Long For rw = 25 To 35 With Worksheets("Sheet1").Cells(rw, 61) If .Value = "" Then Else Tx = .Value ComboBox1.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 62) If .Value = "" Then Else Tx = .Value ComboBox2.AddItem Tx End If End With With Worksheets("Sheet1").Cells(rw, 63) If .Value = "" Then Else Tx = .Value ComboBox3.AddItem Tx End If End With ' <...snip... This repeats quite a bit, With Worksheets("Sheet1").Cells(rw, 72) If .Value = "" Then Else Tx = .Value ComboBox12.AddItem Tx End If End With Next rw End Sub Thanks for looking ChrisA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|