? To Tom Ogilvy or others on VBA Listbox

[Brad K.]

Tom,
A week or two back you responded to a question on how to program a Listbox
into the system. I have tried everything to make this work but have
consistently gotten the message "The macro "...xls'!Box_Click' cannot be
found." What am I doing wrong? The code you wrote is:

==================================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.ListBoxes.Delete
On Error GoTo 0

If Target.Column = 1 Then
With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _
ActiveCell.Top, 100, 150)
.Name = "Listbox1"
.ControlFormat.ListFillRange = "Sheet2!a1:a18"

End With
Me.ListBoxes("Listbox1").OnAction = "Box_Click"
End If

End Sub

In a general module (no the module associated with the sheet) put in this
code


Sub Box_Click()
Set lbox = ActiveSheet.ListBoxes(Application.Caller)
sVal = lbox.List(lbox.ListIndex)
ActiveCell.Value = sVal
End Sub
===================================================

Thanks for any help you can give.
Brad K.

 

[augustus]

I tried your code, with the following objects in Project Explorer under VBE:
-Sheet1
-Sheet2
-Sheet3
-Module1

Placed the Private Sub WorkSheet_SelectionChange......End Sub under sheet1,
and Sub Box_Click() ....End Sub under Module1, no problem.

The only thing that I have to change is the Worksheet(3) to Sheet1 for the
quick test that I did. Since, from memory, the Worksheets(3) depends on how
the worksheet got indexed.

 

[Brad K.]

Whoops, should have mentioned Excel 2002 and Windows 2000.
Brad

 

[Bob Phillips]

Works for me. Did you put the Box_Click macro in a general module, not the
sheet module?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I tested it before posting and it worked for me. Others seem to have had
good luck. If you want to contact me via email and give me your email
address, I can send you a sample workbook.

 

[Brad K.]

Thanks all. I realized that despite what I thought, I was not correctly
putting the Click_Box macro into a general module.

One more question, how do I close or delete the Listbox once I have made my
selection (other than clicking on a new cell).
Thanks,
Brad

 

[Bob Phillips]

Brad,

Just put this code in a separate macro.

On Error Resume Next
Me.ListBoxes.Delete
On Error GoTo 0


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub Box_Click()
Dim sVal as String
Dim lbox as ListBox
Set lbox = ActiveSheet.ListBoxes(Application.Caller)
sVal = lbox.List(lbox.ListIndex)
ActiveCell.Value = sVal
lbox.Delete
End Sub