Solved

naming conventions

Posted on 2014-04-13
4
228 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 68

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 26

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now