Code execution

excelsean

New Member
Joined
Sep 12, 2002
Messages
5
Yesterday was my first post. Thank you 'Brian in Maui' for your reply.

Today I'm inserting canned code into a worksheet module macro. When I drop it in an individual sheet, it works great. When I drop it in "This Workbook" the code does not function. If I want the code to function for every sheet, how can one do it?

Thanks!

Sean.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Maybe you can't park the code in one place, for all sheets.

What's the name of the routine(s)?

You may just need to take the code, put it in a routine of your naming, and call that routine from the event.
 

excelsean

New Member
Joined
Sep 12, 2002
Messages
5
Hello Steve;

What I'm doing is inserting the color band code from the hall of fame section of this site. When I drop the code into "sheet1", it works great only sheet1. When I drop the code into "thisworkbook", it doesn't work in any sheet. I want it to work in all sheets with the least amount of effort. Any suggestions. (p.s. I'm a newbie)

Talk in short sentences.<grin>

Thanks!

Sean.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Okay, found your post. You are working with Worksheet_SelectionChange, which is specific to each sheet.

You would need to paste the core code into a routine of your own naming, in ThisWorkbook. Let's call it DoTheBars

Then, in each _SelectionChange event, insert this line:
ThisWorkbook.DoTheBars

That is, IF the code is currently robust enough to figure out what sheet you are trying to reference.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

In each worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.DoTheBars Target
End Sub


In ThisWorkbook:

Sub DoTheBars(Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub
 

excelsean

New Member
Joined
Sep 12, 2002
Messages
5
Steve;

I copied the large string of code to sheet 1, and the short string of code to page 2.

Got an error message for page 2. First line of code was yellowed. "DoTheBars" was also yellowed in line 2.

any clues?
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

Follow the instructions of my (next to last) post.
 
L

Legacy 98055

Guest
Why can't you use a Workbook level event?
This will apply to every worksheet. Place in the workbook module.

<pre>
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
With Sh
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

.Cells.FormatConditions.Delete

'// Horizontal color banding
With .Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With .Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(.Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With
End With
End Sub

</pre>

Tom
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Tom is correct; you can have every sheet covered with his technique.

If there would be an 'exception to the rule' worksheet, you could use my technique.

Steve
 

Forum statistics

Threads
1,144,052
Messages
5,722,242
Members
422,418
Latest member
Chipsy

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