Excel buttons

Microsoft Office excel 2003
August 25, 2010 at 02:36:04
Specs: Windows XP
I have a grid of numbers to select from, the choice is typed (or selected from drop down) into an adjacent cell. is there a way I can insert little selector buttons into each range that would insert the cell value into the cell we currently manually type into?
Can it be set to only allow one selection per range?

See More: Excel buttons

Report •

August 25, 2010 at 05:34:20

You can add a series of command buttons to your worksheet.
There is no need for them to be linked to a range of cells making up a grid - the buttons themselves form the 'grid'

On the Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to the value you wish to enter.
Right-click the button again and select Properties
On the Name row, change the name to something that relates to the button's action, e.g. CB_23 if the Command Button is designed to enter the value 23. Close the Properties box.
Right-click the button again and select View Code
You will see this:

Private Sub CB_23_Click()
End Sub

The 'click' line will use the name you chose in the Properties box.
Now add a line of code to make the button transfer data to a selected cell - I used A1 for this example:
Private Sub CB_23_Click()
ActiveSheet.Range("A1").Value = Me.CB_23.Caption
End Sub

Note that I used Me in the code.
If you paste in the first part:
ActiveSheet.Range("A1").Value = Me
then enter a period and CB_23 will be one of your choices - select it and hit the Tab button and it's entered for you. Enter another period and Caption is available to be selected and hit Tab again.
This method is of value when you are going to create a series of buttons all with slightly different names/captions, as the first part of the line is the same for all buttons and can be copied and pasted multiple times - then just change the end of the line to match the button.

Click File - Save from the Visual Basic Menu.
Click Alt+f11 together to return to the main Excel window and repeat the process for the next button - Add the button, Edit its caption, change its name in Properties then add the code to it.

Hope that this is the sort of thing that you were looking for.


PS Always change the name of the button before adding code to it. If you change the name after adding code, the code will be left orphaned attached to a non-existent button and you will have to re-enter the code for your newly named button.

Report •

August 26, 2010 at 07:46:46
Fantastic answer, thanks. I got what I needed from that. The bit about making the buttons unique or grouping is in the properties section too. There's a field called GroupName and everything with the same GroupName is grouped (duh!).

Great help, much appreciated.


Report •

Related Solutions

Ask Question