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

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.

LVL 12
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
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

NorieVBA ExpertCommented:
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.
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

asiduAuthor Commented:
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.
NorieVBA ExpertCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asiduAuthor Commented:
Thanks for your input rowanscott.
asiduAuthor Commented:
Thank you all for time and inputs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.