#1
|
||||
|
||||
Adding another macro in the macro list
I copied the codes of highlighting the current row and column from a post in 2013 by Pecoflyer and pasted it in module 2 of a workbook containing a macro in Module 1, but when I executed the Module 2 macro, only the Module 1 macro is on the Macro list. How do I add Module 2 macro in the list? The macro in module 1 applies only to the current sheet but the macro in module 2 should run in all sheets of the workbook. I'm sorry I keep on repeating macro because I don't know any other term for it.
The code that I copied started with this line: Private Sub Worksheet_SelectionChange(ByVal Target As Range) |
#2
|
|||
|
|||
That code is for a worksheet event and goes in the sheet module not a standard module.
Right click on the sheet tab and select View Code, or double click the sheet in the Microsoft Excel Objects list in the VBA environment to get to the sheet module. This page should give more insight. |
#3
|
||||
|
||||
Thank you NoSparks. This is a related question so I am not sure if I need to post a new thread. What code/s do I need to insert to the the macro to disable the highlighting before printing the sheet? I can not use the black option in the print page set up because there are colored objects in the sheet. It is only the highlighting in the row and column that I do not want on print. I have opened the link that you sent but nowhere in the examples can I find a code for disabling the macro.
Meantime, my solution is to go to a cell outside of the print area in order that the 'highllights' won't get printed. |
#4
|
|||
|
|||
Are you asking for something to be added to or changed in a macro you're not showing us ?
Posting a sample workbook with code that illustrates the issue would be advantageous. |
#5
|
||||
|
||||
Right, these are the two codes that I copied, the first from Pecoflyer's post and the other from another forum.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' Clear the color of all the cells Cells.Interior.ColorIndex = 0 With Target ' Highlight the entire row and column that contain the active cell .EntireRow.Interior.ColorIndex = 23 .EntireColumn.Interior.ColorIndex = 37 End With Application.ScreenUpdating = True End Sub Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim RngRow As Range Dim RngCol As Range Dim RngFinal As Range Dim Row As Long Dim Col As Long Cells.Interior.ColorIndex = xlNone Row = Target.Row Col = Target.Column Set RngRow = Range("A" & Row, Target) Set RngCol = Range(Cells(1, Col), Target) Set RngFinal = Union(RngRow, RngCol) RngFinal.Interior.ColorIndex = 8 End Sub I guess there is a code that needs to be inserted so that the highlighting will be disabled before printing, that it will not show in the hard copy. Thank you. |
#6
|
|||
|
|||
In those macros, the code that removes the color are
Code:
Cells.Interior.ColorIndex = 0 Code:
Cells.Interior.ColorIndex = xlNone Add one of those lines at the beginning of the recorded macro and print via the macro. |
#7
|
||||
|
||||
Thank you. I will try this tomorrow, I hope I will get the instructions right.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding a function to this macro: | JohnGanymede | Excel Programming | 6 | 12-22-2017 02:57 PM |
Adding to a macro | Ulodesk | Word VBA | 4 | 03-07-2016 01:40 PM |
VBA Code for adding Macro to QAT | OTPM | Project | 4 | 08-22-2013 01:33 PM |
Adding macro to normal.dotm programmatically | etippelt | Word VBA | 6 | 04-08-2013 05:55 PM |
Macro for automatically adding filename without .doc | RPM7 | Word VBA | 0 | 04-29-2010 01:43 AM |