Hello,
I currently have a userform that works for a single worksheet inside a workbook.
I would like to know how to get the userform to open and work on any selected worksheet in the workbook.
I attempted to set ws = worksheet(activesheet.name) instead of setting ws = worksheet("2012"), but the macro for the button used to open the userform no longer works (tells me that the macro is not usable for the active sheet).
Thank you for you consideration.
I currently have a userform that works for a single worksheet inside a workbook.
Code:
Private Sub EditData_Click()
Dim range0 As Range, range1 As Range, res As Variant
Set range0 = Worksheets("2012").Range("A1:P1000").Columns(1)
res = Application.Match(CODE.Text, range0, 0)
Set range1 = range0.Cells(res, 1)
Dim ws As Worksheet
Dim fm As Worksheet
Set ws = Worksheets("2012")
Set fm = Worksheets("Form")
If Not IsError(res) Then
ws.Cells(res, 1).Value = Me.CODE.Value
ws.Cells(res, 2).Value = Me.ITEM.Value
ws.Cells(res, 3).Value = Me.COMPONENT.Value
ws.Cells(res, 4).Value = Me.CRITERIA.Value
ws.Cells(res, 5).Value = Me.NUMBOARDS.Value
ws.Cells(res, 9).Value = Me.DEVELOPERNAME.Value
ws.Cells(res, 7).Value = Me.VENDOR.Value
fm.Cells(12, 3).Value = Me.CODE.Value
fm.Cells(20, 3).Value = Me.COMPONENT.Value
fm.Cells(22, 3).Value = Me.CRITERIA.Value
fm.Cells(41, 4).Value = Me.NUMBOARDSSUPPLIER.Value
fm.Cells(41, 7).Value = Me.NUMBOARDQA.Value
fm.Cells(41, 11).Value = Me.NUMBOARDSDEV.Value
Else
MsgBox "Data Not Found On Data Table Or Is Not a Valid Code Name"
End If
End Sub
I would like to know how to get the userform to open and work on any selected worksheet in the workbook.
I attempted to set ws = worksheet(activesheet.name) instead of setting ws = worksheet("2012"), but the macro for the button used to open the userform no longer works (tells me that the macro is not usable for the active sheet).
Thank you for you consideration.