How to  force insert the desired value for the option selection in Excel 2007

Posted on 2011-10-08
Medium Priority
Last Modified: 2012-05-12
I have a simple form attached.
I would like to have a set of values which reflect correctly in the linked cell.
I have indicated the desired value in the attached file.

These values will later be collected to do some collection.

Is there a straight forward technique  to get the desired results?
Finally the column H will not be displayed in the survey Form.

Question by:asidu
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 500 total points
ID: 36935521
You can use macros to enforce this behavior

I have attached the code and the updated workbook (Code is located under Sheet1 on the workbook)


Sub OptionButton5_Click()
   Range("J6").Value = 0
End Sub

Sub OptionButton6_Click()
   Range("J6").Value = 1
End Sub

Sub OptionButton7_Click()
   Range("J6").Value = 2
End Sub

Sub OptionButton8_Click()
   Range("J13").Value = 0
End Sub
Sub OptionButton9_Click()
   Range("J13").Value = 2
End Sub
Sub OptionButton10_Click()
   Range("J13").Value = 7
End Sub

Open in new window

LVL 35

Assisted Solution

Norie earned 1000 total points
ID: 36935530
If you just use a linked cell then all you will get is what you currently have, eg 1,2,3.

You would need to use a formula based on the value in the linked cell to return the values you want.

For the first one it's straightforward:


For the second one something like this:


These formulas don't go in the linked cell, they need to go in another cell.

If you don't want to see the value in the linked cell format it so it can't be seen, eg change text colour to white.

Assisted Solution

rowanscott earned 500 total points
ID: 36935540
This may not be the most elegant but it works and gives the most control. I have removed the link between the option button group and the cell and linked each option button to a macro which inserts the desired value HowtoForcethe-desired-output-for.xlsm
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 12

Author Comment

ID: 36935570
Thanks Guys for your ideas and time.

A neat solution by writing the VB code.
What I imagine now is that  if I have 25 codes in the Form i will have 75 codes to write. If any changes are made in the Form then there is  need to amend the codes.

Would it be possible to use the code for say Q1 and Q3 which has the same type of ratings allotted. Any ideas or comments please.

Would there be a way to lock these cells formulas ? so that user will not be able to delete them off accidently. I am thinking to put the formula in  far off row eg AZ, any recommendation.
LVL 35

Accepted Solution

Norie earned 1000 total points
ID: 36935571
You should go with rowan's suggestion.

Or even something like this, which would be assigned to all the option buttons via Assign Macro...
Option Explicit

Sub HandleOptionBtns()

    Select Case Application.Caller
        Case "Option Button 5"
            Range("J6") = 0

        Case "Option Button 6"
            Range("J6") = 1
        Case "Option Button 7"
            Range("J6") = 2
        Case "Option Button 8"
            Range("J13") = 0
        Case "Option Button 9"
            Range("J13") = 2
        Case "Option Button 10"
            Range("J13") = 7
    End Select

End Sub

Open in new window

PS That code needs to go in a standard module.
LVL 12

Author Comment

ID: 36935577
Thanks for your input rowanscott.
LVL 12

Author Closing Comment

ID: 36935588
Thank you all for time and inputs.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question