Adding data to sheet with user form

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Have a user form with list boxes that the user selects three choices.
Using these three choices, I need to insert the data into a worksheet.
One is product number, the second is a location from about 13 choices
and the third is a date

rows contain product number in descending order far left, then the 13
locations.

Columns contain the date (for a year by week)

Basically what I am asking is that how do I capture the input and then
perform a three way lookup to add the data.

Know its not the ideal way of setting this up, but it is 'live' sheet
that needs updating until the required results appear.

Many Thanks
 
I put 3 comboboxes on a userform. I put two commandbuttons (cancel and ok) at
the bottom.

I used this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim wks As Worksheet
Dim MatchCol As Variant
Dim MatchRow As Variant
Dim ErrMsg As String
Dim resp As Long

Set wks = Worksheets("Sheet1")

With wks
MatchCol = Application.Match(Me.ComboBox1.Value, .Range("a1").EntireRow,
0)
MatchRow = Application.Match(Me.ComboBox2.Value,
..Range("a1").EntireColumn, 0)

ErrMsg = ""
If IsError(MatchCol) Then
ErrMsg = Me.ComboBox1.Value & " not found on: " & .Name & vbLf
End If
If IsError(MatchRow) Then
ErrMsg = ErrMsg & Me.ComboBox2.Value & " not found on: " _
& .Name & vbLf
End If

If ErrMsg <> "" Then
MsgBox ErrMsg
Exit Sub
End If

With .Cells(MatchRow, MatchCol)
If .Value = "" Then
resp = vbYes
Else
resp = MsgBox _
(prompt:="Overlay: " & .Value & vbLf & "with: " _
& Me.ComboBox3.Value & "?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
.Value = Me.ComboBox3.Value
End If
End With

'clear existing entries?
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""

End With
End Sub

I assumed the headers were in Row 1 and Column A.

Combobox1 looked for the Column. Combobox2 looked for the Row. Combobox3
held the value to be placed in the cell.
 
Back
Top