I have a userForm that finds a match of a selection made by user then enters data in the cells next to that match. I want to be able to run the userform on any sheet and have it find and place data on "Data" sheet no matter where the user is within the workbook. My match code and an example of the data written to the sheet is attached:
Code
Private Sub cmbBookMark_Change()
Dim checkBookMark As Range
Dim firstRow As Long
firstRow = 7
On Error GoTo Err:
Set checkBookMark = Range("A" & firstRow & ":A3000")
matchBM = cmbBookMark.Value
y = Application.WorksheetFunction.Match(matchBM, checkBookMark, 0)
Exit Sub
Err:
MsgBox "No Match Found X-Out and Choose a BookMark"
End Sub
Private Sub cbEnter_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.cmbBookMark.Value = "" Then
MsgBox "Please enter a BookMark.", vbExclamation, "What People Wear"
Me.cmbBookMark.SetFocus
Exit Sub
End If
' Sets y equal to reletive match
Dim checkBookMark As Range
Dim firstRow As Long
firstRow = 7
Set checkBookMark = Range("A" & firstRow & ":A2000")
matchBM = cmbBookMark.Value
y = Application.WorksheetFunction.Match(matchBM, checkBookMark, 0)
' Writes Data to sheet
With Worksheets("Data").Range("A7")
.Offset(y - 1, 6).Value = Me.cmbPersonID.Value
.Offset(y - 1, 10).Value = Me.cmbDayNight.Value
If Me.ckbNoHead.Value = False Then
.Offset(y - 1, 11).Value = Me.cmbItemHead.Value
.Offset(y - 1, 12).Value = Me.cmbColorHead.Value
.Offset(y - 1, 13).Value = Me.cmbPatternHead.Value
End If
End With
End Sub
Display More
I know it is because the match doesn't call out the sheet but I tried placing it and got run time errors.
Any help would be appreciated. Thanks for your time.