Make Code Work With Any Worksheet in an Open Workbook

fun4all

New Member
Joined
Jun 17, 2012
Messages
19
Hello,

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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

fun4all

New Member
Joined
Jun 17, 2012
Messages
19
I apologize, I was able to find the problem. I accidently assigned the button to a newly created macro. Thank you all for your consideration.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,962
Messages
5,627,880
Members
416,281
Latest member
Olawunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top