VBA lookup... Perhaps

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
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

Active Member
Joined
Jun 27, 2006
Messages
345
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.
Code:
dim myvalue
is very different to
Code:
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

Board Regular
Joined
Nov 24, 2005
Messages
173
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

Code:
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

Active Member
Joined
Jun 27, 2006
Messages
345
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.
 

Forum statistics

Threads
1,136,607
Messages
5,676,778
Members
419,651
Latest member
alexanderguhr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top