Macro Runs When A Selection is Made in Listbox

SHumeniuk

New Member
Joined
Jul 22, 2008
Messages
17
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Case_Germany

Active Member
Joined
May 13, 2008
Messages
408
Hi,

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

Code:
'....
Select Case LCase(vVal)
Case "hcp - add beds"
HCPAddBeds
'....
Case_Germany
 
Last edited:

SHumeniuk

New Member
Joined
Jul 22, 2008
Messages
17
Hi,

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

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

Active Member
Joined
May 13, 2008
Messages
408
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:

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

Code:
Select Case vVal
Case "HCP - Add Beds"
HCPAddBeds
Case "HCP - Add Facility"
HCPAddFacility
Case_Germany
 

SHumeniuk

New Member
Joined
Jul 22, 2008
Messages
17
I used your second piece of code, and it is working properly now. Thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

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