Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
ok, I have this code below that populates ComboBox3 with the text in MyRange.
What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
When I do this I try to leave everything sequential so I can use the
Index of the combobox to shift columns, like this: if ComboBox3 has options of data from K through M and "K" is selected then Range(Cells(3,ComboBox3.ListIndex + 10),Cells(18,ComboBox3.ListIndex)).Select Let me know if you need help implementing that or if you need a different solution altogether. Charles Chickering Kevin wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
I would just add a hidden column and store the address there. Then you can
you can retrieve the information when you need it. Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") With ComboBox3 .ColumnCount = 2 .ColumnWidths = ";-1" For Each c In MyRange If c < "" Then .AddItem c.Value .List(.ListCount - 1, 1) = c.Address End If Next End With End Sub ' some demo code showing how to get at it Private Sub ComboBox3_Click() With ComboBox3 MsgBox .Value & " address: " & .List(.ListIndex, 1) End With End Sub This is form a userform, but a similar approach would work on a combobox on the worksheet (from the controls toolbox toolbar) -- Regards, Tom Ogilvy "Kevin" wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
sorry, I had a typo in my post. The columnWidths command should be:
..ColumnWidths = "-1;0" to hide the address column. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: I would just add a hidden column and store the address there. Then you can you can retrieve the information when you need it. Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") With ComboBox3 .ColumnCount = 2 .ColumnWidths = ";-1" For Each c In MyRange If c < "" Then .AddItem c.Value .List(.ListCount - 1, 1) = c.Address End If Next End With End Sub ' some demo code showing how to get at it Private Sub ComboBox3_Click() With ComboBox3 MsgBox .Value & " address: " & .List(.ListIndex, 1) End With End Sub This is form a userform, but a similar approach would work on a combobox on the worksheet (from the controls toolbox toolbar) -- Regards, Tom Ogilvy "Kevin" wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
Those are interesting results, but I cant quite get what I need out of it.
I'm sure its me. Lets say A1 says test1 and A2:A6 is 1-5. Then B1 says test2 and B2:B6 is a-e. I want combobox1 to be filled with a1:b1 text, then I want to choose one of the 2 and when I click it it populates combobox2 with the 1-5 or a-e depending on which text is selected. test1 test2 1 a 2 b 3 c 4 d 5 e The reason I dont want to just refer to the cells is I want to be able to add more columns and more rows without having to update a lot of code. Thanks for the help guys. "Tom Ogilvy" wrote: I would just add a hidden column and store the address there. Then you can you can retrieve the information when you need it. Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") With ComboBox3 .ColumnCount = 2 .ColumnWidths = ";-1" For Each c In MyRange If c < "" Then .AddItem c.Value .List(.ListCount - 1, 1) = c.Address End If Next End With End Sub ' some demo code showing how to get at it Private Sub ComboBox3_Click() With ComboBox3 MsgBox .Value & " address: " & .List(.ListIndex, 1) End With End Sub This is form a userform, but a similar approach would work on a combobox on the worksheet (from the controls toolbox toolbar) -- Regards, Tom Ogilvy "Kevin" wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
Paste these subs into your Userform Code:
Private Sub ComboBox1_Change() Dim cnt As Long ComboBox2.Clear For cnt = 1 To 5 'Change 5 to the number of cells you want to list 'To offset columns, Change the + 1 after ComboBox1.ListIndex to the First used Column ComboBox2.AddItem Cells(cnt + 1, ComboBox1.ListIndex + 1), cnt - 1 Next ComboBox2.ListIndex = 0 End Sub 'This procedure autoloads comboBox1 with the number of used columns in Row 1 Private Sub UserForm_Activate() Dim cnt As Long ComboBox1.Clear ComboBox2.Clear For cnt = 1 To Cells(1, Columns.Count).End(xlToLeft).Column ComboBox1.AddItem Cells(1, cnt), cnt - 1 Next ComboBox1.ListIndex = 0 End Sub Let me know if you need help. Charles Chickering Kevin wrote: Those are interesting results, but I cant quite get what I need out of it. I'm sure its me. Lets say A1 says test1 and A2:A6 is 1-5. Then B1 says test2 and B2:B6 is a-e. I want combobox1 to be filled with a1:b1 text, then I want to choose one of the 2 and when I click it it populates combobox2 with the 1-5 or a-e depending on which text is selected. test1 test2 1 a 2 b 3 c 4 d 5 e The reason I dont want to just refer to the cells is I want to be able to add more columns and more rows without having to update a lot of code. Thanks for the help guys. "Tom Ogilvy" wrote: I would just add a hidden column and store the address there. Then you can you can retrieve the information when you need it. Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") With ComboBox3 .ColumnCount = 2 .ColumnWidths = ";-1" For Each c In MyRange If c < "" Then .AddItem c.Value .List(.ListCount - 1, 1) = c.Address End If Next End With End Sub ' some demo code showing how to get at it Private Sub ComboBox3_Click() With ComboBox3 MsgBox .Value & " address: " & .List(.ListIndex, 1) End With End Sub This is form a userform, but a similar approach would work on a combobox on the worksheet (from the controls toolbox toolbar) -- Regards, Tom Ogilvy "Kevin" wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding test and cell address to combo box.
That works fine if the cells are contiguous as in your second example. That
is not the example you gave the first time. So if they are contiguous, then you are set. If not, then you just wasted a lot of time barking at the moon when you could have been moving on to the next phase of your problem. <g -- Regards, Tom Ogilvy "Kevin" wrote in message ... Got that to work, thanks a bunch for all you guys help. "Die_Another_Day" wrote: Paste these subs into your Userform Code: Private Sub ComboBox1_Change() Dim cnt As Long ComboBox2.Clear For cnt = 1 To 5 'Change 5 to the number of cells you want to list 'To offset columns, Change the + 1 after ComboBox1.ListIndex to the First used Column ComboBox2.AddItem Cells(cnt + 1, ComboBox1.ListIndex + 1), cnt - 1 Next ComboBox2.ListIndex = 0 End Sub 'This procedure autoloads comboBox1 with the number of used columns in Row 1 Private Sub UserForm_Activate() Dim cnt As Long ComboBox1.Clear ComboBox2.Clear For cnt = 1 To Cells(1, Columns.Count).End(xlToLeft).Column ComboBox1.AddItem Cells(1, cnt), cnt - 1 Next ComboBox1.ListIndex = 0 End Sub Let me know if you need help. Charles Chickering Kevin wrote: Those are interesting results, but I cant quite get what I need out of it. I'm sure its me. Lets say A1 says test1 and A2:A6 is 1-5. Then B1 says test2 and B2:B6 is a-e. I want combobox1 to be filled with a1:b1 text, then I want to choose one of the 2 and when I click it it populates combobox2 with the 1-5 or a-e depending on which text is selected. test1 test2 1 a 2 b 3 c 4 d 5 e The reason I dont want to just refer to the cells is I want to be able to add more columns and more rows without having to update a lot of code. Thanks for the help guys. "Tom Ogilvy" wrote: I would just add a hidden column and store the address there. Then you can you can retrieve the information when you need it. Private Sub UserForm_Initialize() Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") With ComboBox3 .ColumnCount = 2 .ColumnWidths = ";-1" For Each c In MyRange If c < "" Then .AddItem c.Value .List(.ListCount - 1, 1) = c.Address End If Next End With End Sub ' some demo code showing how to get at it Private Sub ComboBox3_Click() With ComboBox3 MsgBox .Value & " address: " & .List(.ListIndex, 1) End With End Sub This is form a userform, but a similar approach would work on a combobox on the worksheet (from the controls toolbox toolbar) -- Regards, Tom Ogilvy "Kevin" wrote: ok, I have this code below that populates ComboBox3 with the text in MyRange. What I would like to do is record the address of the cell somwhere for reference once a value is selected in combobox3. EX. If ComboBox3 is populated and I select the text that is on cell K1 I need the address K1 recoreded somewhere so that I can populate a second combo box with range k3:k18. Or if the text for L2 is selected I need a second combo box with L3:L18 and so forth. ========================================== Dim MyRange As Range Set MyRange = Sheets("sheet1").Range("k1:z1,k21:z21") For Each C In MyRange If C = "" Then GoTo 10 Else ComboBox3.AddItem C.Value End If 10: Next ========================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combo function of adding % | Excel Worksheet Functions | |||
combo reference on another combo box for picking address etc. | Excel Worksheet Functions | |||
Converting test to numbers and adding into a summary page | Excel Worksheet Functions | |||
Adding Sheets to a combo Box | Excel Programming | |||
adding info to combo box | Excel Programming |