How do I create an interactive form in excel?

Posted on 2010-01-04
Last Modified: 2012-06-21
I need to create an excel spreadsheet that works sorta like an interactive form on the web.  I have dropdown menus in excel for example a lot number for a house, drop down for the house plan, dropdown for counter tops .. etc.  Each of these choices in the drop down has a price associated with it.  Id like to be able to populate a cell with these prices based on the dropdown selection so I can add a formula in for total cost based on several different drop down configurations.  A makeshift effort is attached.  I have figured out the dropdown menus, just not sure how to link their selections to and populate another cell.

Thanks in advance,
Question by:jdroger2
    LVL 5

    Expert Comment

    I don't see the worksheet but you can refer the values like this
    cells(X,Y).Value = Form.Dropdown.Value

    LVL 20

    Expert Comment

    Hi roger,
    This is doable by using macros on the page which will change the value in the cells based on the dropdown item selected. See attached file..

    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim i As Integer
       Dim str As String
       'Do nothing if more than one cell is changed or content deleted
       If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
       If Not Intersect(Target, Range("A2")) Is Nothing Then
        'Stop any possible runtime errors and halting code
        On Error Resume Next
        'Turn off ALL events so the change does not put the code into a loop.
        Application.EnableEvents = False
        Select Case (Left(Target, 1))
            Case "A"
                Range("B2") = 2000
            Case "B"
                Range("B2") = 4000
            Case "C"
                Range("B2") = 5000
            Case Else
                Range("B2") = 10000
        End Select
        'Turn events back on
        Application.EnableEvents = True
        'Allow run time errors again
        On Error GoTo 0
        End If
    End Sub

    Open in new window

    LVL 5

    Accepted Solution

    this is easier than what i suggested, after opening your sales worksheet
    just add the following formulas
    on column B2 add
    on column D2 add
    and on column F2 add

    now when you change the dropdowns it will look for the housing style on
    the list and will retrieve the cost
    then the lot as well and finally the countertop

    LVL 1

    Author Comment

    @Pari123, I was unable to make your file work properly but thank you for your assistance.

    @Fhillyer, perfect. Exactly the formula I was looking for.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now