I suggest that you use a proper form and not a control on the worksheet.
It takes a little longer to setup but the effect is so much better.
It is visually better, and controls, labels and buttons can be grouped logically.
The code is all in one place and variables can be shared between controls, labels and of course cells on the spreadsheet.
Try the following:
Open a new spreadsheet and save it as "Sample.xls"
Open the Visual Basic editor = Alt+f11 is easiest or Tools- Macro - Visual Basic Editor
On the left should be the Project Explorer pane and you will see VBAProject (Sample.xls)
(If not visible go to View - Project Explorer)
Expand VBAProject (Sample.xls)
You will see Microsoft Excel Objects with one entry for the whole Workbook and one entry for each worksheet
We are going to add two new objects to Sample.xls.
One item will be a form and the other item will be a module.
Right click on VBAProject (Sample.xls) and select Insert - UserForm
Repeat for Module (not Class Module)
You will now have a longer 'tree' with a UserForm1 and a Module1
It would be good to give these meaningful names, but just for this demo. keep all the default names
Double click on UserForm1
Up comes a rectangle labeled UserForm1 with a grid of dots
Now we will add four items to the form:
One Combo Box,
Two Labels and
One Command Button
There should be a toolbox showing to select from (if not click View - Toolbox)
It doesn't matter where you put these items on your form - moving them later will not affect how they work
Now back to your workbook - no need to close the VBA window -
On sheet 2 enter the following from Cell B1 to cell B8
B1 Combo Box 1 Options
B2 Option 1
B3 Option 2
B4 Option 3
B5 Option 4
B6 Option 5
B7 Option 6
B8 Option 7
Now back to the form
With a form you need to have some Initialization done that gets your form ready for use:
At the top of the Project Explorer are three icons. The leftmost is View Code and the middle is View Object (the form)
Click on view code
Code belongs to various items - the User Form itself and the various tools or objects on the form as well as one general area
At the top of the code window, use the left hand drop down to select User Form
You will get a generic Click event,
but ignore it and from the right side drop down select Initialize
Whatever code gets placed in this section will run as soon as the form is brought to life!
Enter this code which will populate the ComboBox with the list we created on Sheet 2 and it will select which list item will be in view when the form opens. (Later on try changing the ListIndex based on one of those International values eg
Me.ComboBox1.ListIndex = Application.International(xlNoncurrencyDigits) -1
I have two digits set so option 2 shows):
(The first and last lines of the following code should already be on the screen for you)
Private Sub UserForm_Initialize()
Me.ComboBox1.List = Workbooks("Sample.xls").Worksheets("Sheet2").Range("B2:B8").Value
Me.ComboBox1.ListIndex = 0
Me.Label1.Caption = "You Selected :"
Try clicking on a period after the 'Me' then delete it and reenter it. You will get a list of objects on your form - sure beats trying to remember all the objects names! Use the Tab button to select an object.
Try the same for the period after ComboBox1 and you will see all the things available for a ComboBox.
Now select the ComboBox Object from the left side drop down and Change from the right side drop down
Enter the following code:
Private Sub ComboBox1_Change()
Me.Label2.Caption = Me.ComboBox1.Value
Finally enter this code for CommandButton1 - Click:
Private Sub CommandButton1_Click()
Workbooks("Sample.xls").Worksheets("Sheet2").Range("C1").Value = "Selected by User at " & Format(Now(), "hh:mm")
Workbooks("Sample.xls").Worksheets("Sheet2").Range("C2").Value = Me.ComboBox1.Value
You now have a working Form
To get the form to open we can add a subroutine in Module1
Double click module 1 in the Project Explorer
Enter this code:
vbModeless means that when the form is shown you can still get out of the form and got to the worksheet
If it was Modal you couldn't get out of the Form, except by closing it.
Now you're ready for a test.
Goto Module 1 and click inside the code - press f8 and the first line of code is highlighted (f8 is single step mode - good for testing code line by line) now hit f5 and the code will run.
The form opens !
Move windows about so that you can see Sheet 2 and your list.
Back to the form and you should see that Option1 shows in the ComboBox and label 1 says "you selected:"
and label 2 says "Option1".
Use the ComboBox and select different options
Label 2 will change to reflect what you selected and
Now click the CommandButton1 and a message like the following will appear on Sheet 2 next to your list of options:
Selected by User at 17:34
Change the ComboBox selection and click the command button and it all changes.
I didn't put a Quit button on the Form so to exit use the 'x' on the window - bad programming - but this was a quick demo to get you started.
Hope this gives you a start and shows what is possible.