Form based excel input

[Queryman]

I urgently need to create the following:
Excel workbook with two tabs, the 1st tab will only show a form for input with the following inputs:
1. Program Type (pick from list)
2. Product(pick from list)
3. Date
4. Estimated cost ($ format with 2 decimals)
5. Actual cost ($ format with 2 decimals)


I need to capture the user input and put it on the 1st tab in order of input, each input would have one rom on the worksheet, the user can see the data entered but they will not be able to change it once entered or delete any rows.

I am thinking that the user input can be captured and dsiplayed in the spreadsheet in four defined areas for the four types of programs, and it would display the the actual and estimated cost information in columns for each product by date in rows

I have some code shown below that does something similar, it takes input from a defined list of cells in the beginning of the workshhet and plops it in defined areas, I am not that good with VBA, can someone point me in the right direction to change this code to do what I need.

CODE

1) Program SPEAKER PROGRAMS
2) Product A
3) Date
4) Estimated cost $444.00
5) Actual cost
8) Click on Accept

SPEAKER PROGRAMS A B C D E F G H I J
15.00 3.10 78.00 0.30 15.00 0.00 0.00 0.00 0.00 0.00

Sub Accept_data()

Dim RowLookup As Range, ColLookup As Range
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column).Value = Range("c8").Value
Accept_data2
End Sub

Private Sub Accept_data2()

Dim RowLookup As Range, ColLookup As Range
Const COL_OFF = 11
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column + COL_OFF).Value = Range("c5").Value
Accept_data3
End Sub

Private Sub Accept_data3()

Dim RowLookup As Range, ColLookup As Range
Const COL_OFF = 22
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column + COL_OFF).Value = Range("c6").Value
Accept_data4
End Sub

Private Sub Accept_data4()

Dim RowLookup As Range, ColLookup As Range
Const COL_OFF = 33
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column + COL_OFF).Value = Range("c7").Value
End Sub

Sub show_frmCalculations()
frmCalculations.show
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Const HEAD_ROW = 11
Const COL_OFF = 10
With Target
If .Row <= HEAD_ROW Then Exit Sub 'this is the heading row
If .Count > 1 Then Exit Sub 'more than one cell selected
Set rng = Application.Intersect(Target, Range("b12:k51"))
If Not rng Is Nothing Then
ThisSelectedScenarioProduct = Cells(Target.Row, Target.Column + COL_OFF + 1).Value
' ThisSelectedYear = Cells(HEAD_ROW, Target.Column + COL_OFF)
MsgBox " Your Choices were: " & ThisSelectedScenarioProduct
End If
End With
Set rng = Nothing

End Sub
Private Sub Workbook_Open()
'Sheets("Assumptions").ScrollArea = "A2:K55"
' ZoomToRange ZoomThisRange:=Range("A2:C7"), PreserveRows:=True
Sheets("Assumptions").ScrollArea = ""

frmSplash.show
frmCalculations.show
End Sub

The second tab is just for them to enter details on each program and they can change it as they please.




Michael







Michael