Drop arrow and List Box

Posted by RogerBlack on June 26, 2001 5:32 AM

I have set up my listbox on my form. What I want now is to have it look the way you would see it if you used "Data Validation In Excel". My box has two columns so data validation was a no-no in this case and the way my boss wants the wroksheet to look, Vlookup is not an option. Is there any code i can apply to see my dropdown arrow and list in each cell in a column? Here's hoping you can help me I am out of time.

Roger

Posted by mseyf on June 26, 2001 8:58 AM

Can't help with displaying the dropdown arrow, but this may get you started with the rest:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If Not Intersect(Target, Range("a:a")) Is Nothing Then
ListBox1.Top = Target.Top + Target.Height
ListBox1.Left = Target.Left
ListBox1.Visible = True
ListBox1.LinkedCell = Target
ListBox1.Value = Null
Else
ListBox1.Visible = False
End If
End Sub

Private Sub ListBox1_Click()
ActiveCell.Value = ListBox1.Value
ActiveCell.Select
ListBox1.Visible = False
End Sub

HTH

Mark

Posted by RogerBlack on June 26, 2001 1:09 PM

Thanks for the code, Mark, it helped quite a bit but now I am hoping that there is someone who can help with the drop down arrow and list since i am a new born where coding is concerned.

Roger

Posted by zen on June 26, 2001 5:05 PM

forgive if i'm being stupid, i'n new to vb also

is the combobox not what you need??

zen

Posted by Ivan F Moala on June 27, 2001 12:39 AM

Roger
The dropdown arrow you talk of is avail via the
combobox control.
Coding is the same .

Ivan

Posted by RogerBlack on June 27, 2001 10:56 AM

It stands to reason then that if i change my listbox to a combobox and use the coding the box would do the same thing the list box is doing, that is sitting at the top of my column and not being in each cell as i would like it to be.

Roger