Microsoft Excel allows you to create the form with radio buttons, text boxes, check boxes and everything needs to complete the form. Here’s the problem though, form elements in Excel are small and you can’t make them larger.
Even if you try to make them larger you are not lucky enough as it does not make large enough. Excel MVP Jordan Goldmeier will let you know how to make Radio Buttons larger with Excel VBA. You can apply the same technique for other form elements in Excel.
Steps to Make Radio Buttons Larger With Excel VBA
To proceed further as part of the tutorial, first you need to make sure that Excel you are using needs to have Developer tab and Camera icon. If they are not enabled, follow below steps.
1 – Enable Developer Tab in Excel
- Click on ‘File’ Tab and then on ‘Options’ on the left side.
- From the ‘Excel Options’ dialog box, click on ‘Customize Ribbon’ on the left side.
- On right side, under ‘Main Tabs’ tick the option ‘Developer’ and click Ok. Now, you could see Developer Tab in Excel.
2 – Add Camera Icon to Excel
- Follow Step 1 as above.
- On left side click on ‘Quick Access Toolbar’.
- From the dropdown ‘Choose commands from’ select the option ‘All Commands’.
- All options are displayed alphabetically and scroll down till you see ‘Camera’ option.
- Select ‘camera’ option and click on ‘Add’ button. It gets added to the ‘Customize Quick Access Toolbar’.
- Click ‘Ok’ and you could see that Camera icon being added to Excel.
3 – Insert Radio Buttons in Excel
Now, in this step lets us create the normal Radio Buttons which Excel provides us.
- Under Developer tab in Controls section, click on the Insert It shows you different form controls and click on Option button symbol from them (Last option in the first line).
- Now click on the spread sheet where you want to place this Option button. If you want to add more number of these buttons, copy the previous button and paste it where you want.
- If you see, I added 3 Options buttons in the Spread sheet. You can edit the label by right clicking on it and click ‘Edit Text’.
4 – Add Format Control to Radio Buttons
Format Control returns the unique value for every Radio Button you selected. This will let you know which Radio Button was checked and can use that value wherever needed. Let us do that now.
- Right click on the Radio Button added and click ‘Format Control’.
- In ‘Format Controls’ dialog box, under ‘Control’ tab refer to the cell where you want to display the value of the selected Radio Button.
- In this example, I selected ‘G3’ cell to display the selected Radio Button.
- Now, let us give the name to the G3 cell as ‘CheckboxReturnedValue’ by selecting the cell G3 and typing the name in Name Box.
5 – Create Snapshots of the Added Radio Buttons
- Highlight the radio button and click on the Camera Button.
- It copies the snapshot and click on the spreadsheet where you want to add it.
- Repeat this for other 2 Radio Buttons also.
- Now, you could see 3 radio buttons and their snapshots added. You can enlarge these snapshots by dragging the corners.
- Let us name each of these snapshots as Button1, Button2 and Button3 respectively using Name Box.
6 – The Trick (VBA Code)
To make Radio Buttons Larger in Excel we write the VBA code now.
- Under ‘Developer’ tab click on the ‘Visual Basic’ option.
- It opens the ‘Microsoft Visual Basic for Applications’.
- Now, copy the below VBA code and paste it.
Public Sub WriteNumberToResponse()
Dim ButtonIndex As Integer
ButtonIndex = CInt(Replace(Application.Caller, Button, “”))
[CheckboxReturnValue].Value = ButtonIndex
Jordan has explained the each and every line of the code in the video.
7 – Assign Macro to the Radio Button Snapshot
Now, right click on the snapshot of the Radio Button and click ‘Assign Macro’ option. Next, select the created macro and click ‘Ok’. Do this for all the snapshots of the Radio Buttons. From now on, when you click on the snapshot, you could see the returned value.
Get The Download
Download The Sample File Jordan Used
It is interesting right? From now you can have larger Radio Buttons with Excel VBA. If you have anything to add, please do share with us through comments.