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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top