Solved

naming conventions

Posted on 2014-04-13
4
231 Views
Last Modified: 2014-04-13
Hi All,

This may seem like a bit of a silly question that I should have known by now...but I don't.

Now that I am combining lots of different code I am finding that I am getting tripped up quite a bit.  

So for example:

Private Sub Worksheet_Change(ByVal Target As Range)

I know that this line (and the rest of the subsequent code) should probably go into a module.  But how do I get the sub, once it is in a module, to point to the correct sheet?

This one above, I think, by default points to sheet1.

But how would I name this if sheet2 = BlueSheet or Sheet3 = RedSheet and I want to point to Bluesheet or Redsheet?

Sorry for the dumb Sunday AM question.

thanks!
0
Comment
Question by:BostonBob
4 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 150 total points
ID: 39997618
You need to put that code into each sheet's module, as it is a Sheet Event Trigger sub.
If you have to do the same for many sheets, you usually just code the trigger sub to call another, general sub with a explicit reference to the sheet you need to sub to run on.
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 150 total points
ID: 39997645
In simple words, you have to copy the code to each sheet you want it available to.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 39997709
A Worksheet_Change sub always knows which worksheet it is dealing with. You do not need to explicitly specify the worksheet's name. In other words, if you use Range("B2") in a Worksheet_Change sub for bluesheet, it will point to cell B2 on bluesheet. If you take that exact same sub and put it in the code pane for redsheet, Range("B2") will now automatically refer to cell B2 on redsheet.

Qlemo makes a very good point, however. If you find that you need the same (or almost the same) Worksheet_Change sub in several worksheets, it is better to move the parts of the code that do the work into a sub in a regular module sheet. You then call that sub by short Worksheet_Change stubs in the worksheet code panes. For example:
'Goes in worksheet code pane. Replicate as required.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2:H10010")) Is Nothing Then 
    ActionSub Target
End If
Exit Sub

Open in new window

'Goes in regular module sheet
Sub ActionSub(Target As Range)
'Code in this sub needs to explicitly specify the worksheet
Dim ws As Worksheet
Set ws = Target.Worksheet         'You can now use ws instead of redsheet or bluesheet
'Do stuff
'Range("B2").Value = 3          'Very bad practice!!! 
ws.Range("B2").Value = 3       'The right way to do it
End Sub

Open in new window

0
 

Author Closing Comment

by:BostonBob
ID: 39997914
Thanks for school. THAT MAKES sense!!!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an explanation of a simple data model to help parse a JSON feed
This is about my first experience with programming Arduino.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question