• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

How do I create an interactive form in excel?

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,
Chase
SalesTest.xlsx
0
jdroger2
Asked:
jdroger2
  • 2
1 Solution
 
fhillyer1Commented:
I don't see the worksheet but you can refer the values like this
cells(X,Y).Value = Form.Dropdown.Value

0
 
Ardhendu SarangiSr. Project ManagerCommented:
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..

Thanks,
Ardhendu
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

SalesTest.xls
0
 
fhillyer1Commented:
this is easier than what i suggested, after opening your sales worksheet
just add the following formulas
on column B2 add
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
on column D2 add
=VLOOKUP(C2,Sheet2!C:D,2,FALSE)
and on column F2 add
=VLOOKUP(E2,Sheet2!E:F,2,FALSE)

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

0
 
jdroger2Author Commented:
@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.
0
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now