Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

naming conventions

Posted on 2014-04-13
4
Medium Priority
?
239 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 71

Accepted Solution

by:
Qlemo earned 600 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 28

Assisted Solution

by:MacroShadow
MacroShadow earned 600 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 800 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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 …
Screencast - Getting to Know the Pipeline

782 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