VBA lookup... Perhaps

[irresistible007]

okay on sheet 1 there is a list box and a command button. Sheet 2 is having names on Col A and their respective id # on column B.

Now back to Sheet 1, list box is successfully loaded with the names currently on Col A of sheet 2.

On clicking command button, I want to dim a variable whose value must be the id # of name selected on list box....how to do that?

 

[hayden]

I can't do it solely from VBA... but you could use an index function to provide the value slected in the list box, and then use vlookup to return the ID number of that value. Then assign the variable to the cell with the ID number in it.

Good Luck

Hayden

 

[patrickmuldoon99]

Why do you have to wait until the user selects something before you dimension a variable?

You talk about setting its value, which is something completely different to setting the name of a variable.

dim myvalue

is very different to

myvariable = myvalue

You cannot dimension variables at run time. The only thing you can do is have a set of variables ready in the form of a variant array. Is that what you are after?

 

[irresistible007]

I did it myself... but i havent experimented it on my actual sheet.... here i explains my temp file:

i have putted everything on sheet 1, command button 1 is used to populate the list box and command button2 is used to set the value i.e. id

Moreover to confirm it i used a line to display the value of that variable in cell D20

Private Sub CommandButton1_Click()
ListBox1.Clear

Dim nindex As Integer
For nindex = 2 To 200
    If Cells(nindex, 1) <> "" Then
    Sheet1.ListBox1.AddItem Cells(nindex, 1).Value
    End If
Next
End Sub

Private Sub CommandButton2_Click()
Dim id As Variant

Dim mystr As String
mystr = ListBox1.Text

Dim nindex As Integer
For nindex = 2 To 200
    If Cells(nindex, 1).Value = mystr Then
    id = Cells(nindex, 2)
    End If
Next

Range("D20").Value = id

End Sub

 

[patrickmuldoon99]

Okay, i'm with you

So you have a variant variable, which you have declared at design time, that is being assigned a value (not being dimensioned) when you click the command button. Forgive me, that seems quite different to what you were asking originally.