Solved

naming conventions

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

28 Experts available now in Live!

Get 1:1 Help Now