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

Posted on 2011-10-08
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

    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 33

    Assisted Solution

    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.
    LVL 4

    Assisted Solution

    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
    LVL 12

    Author Comment

    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 33

    Accepted Solution

    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

    Thanks for your input rowanscott.
    LVL 12

    Author Closing Comment

    Thank you all for time and inputs.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now