Macro Runs When A Selection is Made in Listbox

[SHumeniuk]

I have created a listbox from the forms toolbar in Excel 2007. What I would like is to assign a macro to the listbox, so that when a selection is made from the list, a specified macro will run. I googled and found some VBA code online that I have tried to use, but it is not working. I have 12 selections on the listbox; I have only included two in this code (what's the point of adding more until I can get it working!). I would appreciate any assistance.

This is the code I am using:

Sub EndorsementTypeListbox()
Dim sName As String, vVal As String
Dim lbox As ListBox
sName = Application.Caller
Set lbox = ActiveSheet.ListBoxes(sName)
vVal = lbox.List(lbox.ListIndex)
Select Case LCase(vVal)
Case "HCP - Add Beds"
HCPAddBeds
Case "HCP - Add Facility"
HCPAddFacility
End Select

End Sub


Much appreciated,

Stephanie

 

[Case_Germany]

Hi,

if you write LCase, then the condition must be written in such a way:

'....
Select Case LCase(vVal)
Case "hcp - add beds"
HCPAddBeds
'....

Case_Germany

 

[SHumeniuk]

Hi,

if you write LCase, then the condition must be written in such a way:

'....
Select Case LCase(vVal)
Case "hcp - add beds"
HCPAddBeds
'....

Case_Germany

Do I have to use LCase? Is there a better way to do this? When I run this macro, and make a selection from the listbox, absolutely nothing happens. No error messages - nothing.

 

[Case_Germany]

Hi,

it prevents errors with LCase or UCase to work, because it is no matter, how the text is then located in the ListBox. In addition, you can write in such a way for UCase:

Select Case UCase(vVal)
Case "HCP - ADD BEDS"
HCPAddBeds
Case "HCP - ADD FACILITY"
HCPAddFacility

Or:

Select Case vVal
Case "HCP - Add Beds"
HCPAddBeds
Case "HCP - Add Facility"
HCPAddFacility

Case_Germany

 

[SHumeniuk]

I used your second piece of code, and it is working properly now. Thank you so much!