Solved

naming conventions

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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 …

733 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