[Ripperdan]

I have a macro written in one of my Excel worksheets but I want to be able to use it in all of the worksheets in the workbook. If I insert a module and put the macro there it doesn't. What do I need to do to get it to work in all sheets?

[Rollin_Again]

Can you post your macro code? The code should work on any sheet as long as you are not referencing the sheet name directly in the code.

Regards,
Rollin

[Ripperdan]

That’s the problem. Each Button (Rectangle) is individually referenced on a particular Sheet.

Quality Control Technicians make tests on pieces of equipment and are required to indicate wheather that particular test “Passed”, “Failed” “N/A”. These tests are recorded on pre-formatted Test Template Worksheets. A new Test Template is opened and renamed according to the equipment being tested.

Each Workbook will have multiple Sheets. Each Sheet will have multiple Toggle Buttons.

Let’s say there are 10 to 20 tests on each Sheet. I could put 10 or 20 of this code on each sheet and manually change the code referencing the individual “Rectangles” but I would rather put the code in a Module so that I only had to write it once, referencing the Sheet and particular “Rectangle”.

Zack Barresse has helped me a lot to get the code to work. He has helped so much I hate to ask him to use more of his time to help me.

At any rate, attached is a sample Excel file of the form as it stands now.

[Zack Barresse]

It would help if you kept one issue per thread. You should have told me the entire scope in your other thread. (Thread here)

You can do this by assigning all of your routines to a single call, which then calls the routine you want. If you take all of your code out of the worksheet modules and just have the following code in a standard module, you'll get what you want...

Option Explicit
Option Base 1

Sub GlobalButtonCall()
    Call ToggleButton
End Sub

Sub ToggleButton()
    Dim oShp As Variant, iNum As Long, x As Variant
    Dim aValues() As Variant, aColors(1 To 4, 1 To 3) As Long
    aValues = Array("PASS", "FAIL", "N/A", "")
    aColors(1, 1) = 0: aColors(1, 2) = 255: aColors(1, 3) = 0
    aColors(2, 1) = 255: aColors(2, 2) = 0: aColors(2, 3) = 0
    aColors(3, 1) = 192: aColors(3, 2) = 192: aColors(3, 3) = 192
    aColors(4, 1) = 255: aColors(4, 2) = 255: aColors(4, 3) = 200
    Set oShp = ActiveSheet.Shapes(Application.Caller)
    On Error Resume Next
    x = WorksheetFunction.Match(oShp.TextFrame.Characters.Text, aValues(), 0)
    On Error GoTo 0
    iNum = x + 1
    If x = UBound(aValues) Then iNum = 1
    oShp.TextFrame.Characters.Text = aValues(iNum)
    oShp.Fill.ForeColor.RGB = RGB(aColors(iNum, 1), aColors(iNum, 2), aColors(iNum, 3))
End Sub

Assign all objects to the GlobalButtonCall routine. Notice how it sets the object to the Application.Caller?

On your worksheet example, on your PLC sheet, I had to delete and recreate the first rectangle (copied the second, selected the cell above, pasted it, then had to change the name - with it selected click in the name box and change, otherwise there were two duplicate rectange names and both would change the one instance).

So stick to one thread, and ALWAYS, ALWAYS, ALWAYS (did I say always?) be completely up front with ALL requirements. The other thread was almost a waste of time because you weren't honest and up front with all of your requirements. This makes for twice as much work for us, sometimes more if others are involved. So let's not waste people's time here. I know you didn't know, and it's ok, but from now on please post everything up front.


Edit: Also, another option you have, which would exclude having to use objects, is to just use a worksheet double click event. That way you'd keep everything in the cells and the user would double click the cell in col A and the change would occur. I would recommend using objects as a last resort, and would recommend the double click method first IMHO. If you want that, please just say so and we'll get you the code for it.

[Cookiegal]

Closing duplicate.

Please stay within one thread only for the same issue.