adding test and cell address to combo box.

[Guest]

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
==========================================

 

[Die_Another_Day]

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

 

[Guest]

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)

 

[Guest]

sorry, I had a typo in my post. The columnWidths command should be:

..ColumnWidths = "-1;0"

to hide the address column.

 

[Guest]

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.

 

[Die_Another_Day]

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

 

[Guest]

Got that to work, thanks a bunch for all you guys help.

 

[Tom Ogilvy]

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>