Simple Module/Macro Question

[goldenvision]

I feel really daft asking this but how do I make my module available as a macro?

I am currently having to copy and paste the module code into each sheet to allow it to run.

Module below:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

 

[stanleydgromjr]

goldenvision,

Please TEST this FIRST in a COPY of your workbook.

Remove your original code from the worksheets.


Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Sub MyMacro()
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

Please TEST this FIRST in a COPY of your workbook.

Then, in any of the worksheets, run the 'MyMacro' macro.

Have a great day,
Stan

 

[gallan]

If I understand it, you would like to be able to automatically run the macro without having it on each page, thus saving some space and some annoyance if you have to do a code change later

so make up the macro as the fellow above says

then use the worksheet activate event to call the macro

 

[Tom Urtis]

I am currently having to copy and paste the module code into each sheet to allow it to run.

Sounds to me like what you are really saying is, you want that code to apply to any sheet in the workbook. So, insert it only once, but in the Workbook module instead of each individual worksheet module, and make it a Workbook SheetSelectionChange event.

Go into every sheet module where you have placed that code and delete it.

Next, press Alt+Q to return to the worksheeet.

From there, to easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet. Note, those directions involve an extra step to get to the Workbook module. If you are comfortable in the VBE, after removing all the individual sheet procedures, then from the Properties window, find your workbook object, find its ThisWorkbook module, and stick the below code in there that way.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Static rr 
    Static cc 

    If cc <> "" Then 
        With Columns(cc).Interior 
            .ColorIndex = xlNone 
        End With 
        With Rows(rr).Interior 
            .ColorIndex = xlNone 
        End With 
    End If 

    r = Selection.Row 
    c = Selection.Column 
    rr = r 
    cc = c 

    With Columns(c).Interior 
        .ColorIndex = 20 
        .Pattern = xlSolid 
    End With 
    With Rows(r).Interior 
        .ColorIndex = 20 
        .Pattern = xlSolid 
    End With 
End Sub

 

[goldenvision]

Thanks for all of the assistance guys. It is working great.