How to ID calling worksheet if it isn't the active sheet?

I have a udf that uses .ActiveSheet to identify the sheet calling it.
Works great as long as the sheet doing the calculating is the active
one.

This sheet with that udf has now been replicated - various what if
version copies.  Each of these copies comes along with its own copy of
that udf in its cells.  Question is, how does the udf ID the calling
sheet if it's not the currently active one?

There are global controls that change input values used by these
copied sheets.  All must recalculate using this global value and their
own local variables.


0
cate
3/29/2010 4:49:35 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
504 Views

Similar Articles

[PageSpeed] 50

Application.Caller will be a Range object pointing to the cell from
which the UDF as called. E.g., 

Function Test() As String
	Test = Application.Caller.Address
End Test

If, though, the function is called by other VBA code,
Application.Caller will not be a Range object.  If the function is to
be called both from a worksheet cell and by other code, you should
test Application.Caller. E.g, 

Function Test() As String
	If IsObject(Application.Caller) Thne
		If TypeOf Application.Caller Is Range Then
			' called from a cell
		End If
	End If
End Function



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 29 Mar 2010 09:49:35 -0700 (PDT), cate
<catebekensail@yahoo.com> wrote:

>I have a udf that uses .ActiveSheet to identify the sheet calling it.
>Works great as long as the sheet doing the calculating is the active
>one.
>
>This sheet with that udf has now been replicated - various what if
>version copies.  Each of these copies comes along with its own copy of
>that udf in its cells.  Question is, how does the udf ID the calling
>sheet if it's not the currently active one?
>
>There are global controls that change input values used by these
>copied sheets.  All must recalculate using this global value and their
>own local variables.
>
0
Chip
3/29/2010 5:13:42 PM
To extend Chip's reply - here's a UDF which just returns the name of
the sheet it's on.

Function SheetName()
    SheetName =3D Application.Caller.Parent.Name
End Function

Tim

On Mar 29, 9:49=A0am, cate <catebekens...@yahoo.com> wrote:
> I have a udf that uses .ActiveSheet to identify the sheet calling it.
> Works great as long as the sheet doing the calculating is the active
> one.
>
> This sheet with that udf has now been replicated - various what if
> version copies. =A0Each of these copies comes along with its own copy of
> that udf in its cells. =A0Question is, how does the udf ID the calling
> sheet if it's not the currently active one?
>
> There are global controls that change input values used by these
> copied sheets. =A0All must recalculate using this global value and their
> own local variables.

0
Tim
3/30/2010 12:30:30 AM
Reply:

Similar Artilces:

Update Case History automatically after New Service Activity is sa
Hi All, When a technician completes a service activity, a custom field on the form "Current Status" will contain the details of the current status of the case. The "Regarding" field on the Service Activity form already contains the particular case. I want to add the "Service Activity (Current Status)" field contents to the beginning of the "Case (Current Status)" field to allow users to see the current status of each case, with the last entry showing on the top of the form. Any ides would be welcome. Thanks -- David Dalton IT Systems Ltd Kill...

Call OnReceive and discard the data
When OnReceive is been notified, I use the Receive function to receive data. Sometimes, I don't want to care the data, and I discard it. My problem is [ If I don't call the Receive function when OnReceive is been notified, the data will be discarded? ] i guess not, data will remain in the tcp buffer ...... u need to call receive by urself and do whatever u want to do with the data "mmlab_js" <mmlabjs@discussions.microsoft.com> wrote in message news:4D3D31D5-B2E4-418A-9619-ED4DE8AA8FA9@microsoft.com... > When OnReceive is been notified, I use the Receive function ...

AJAX UpdatePanel does not refresh after calling Update()-Method
Hi, I'm new to AJAX and has a little Problem to get an UpdatePanel inside an CustomServerControl to work. My goal is to create an CustomServerControl with an (at designtime) unknown number of UpdatePanels, that can be updated seperatly. Since I'm stuck I create a litte more static testproject to analyse the problem but I ran out of ideas what I could been missing. I would be realy glad if someone can show me hwo to get the UpdatePanels working. I created an ServerControl-Project called ServerControlTest and an ASP WebApplication called AXAJTest The Default.asxp look...

Error message when copying sheets
Hi, On some documents (not all spreadsheets), I get an error message when I try to copy a sheet. I get a dialogue box that says: "A formula or sheet you want to copy contains the name 'a1', which already exists on the destination worksheet. Do you want to use this version of the name? - To choose the name as defined in the destination sheet, click Yes. - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box." I can then click yes or No. If I click Yes, I get the same dialogue box for the name &...

how do i get the name of a newly added sheet that i add with Sheets.Add in a vba macro in excel?
how do i get the name of a newly added sheet that i add with Sheets.Add in a vba macro in excel? Hi Daniel, See http://www.mvps.org/dmcritchie/excel/sheets.htm variable = ActiveSheet.Name 'Rename current Sheet ActiveSheet.Name = "Renamed14a" ActiveSheet.Name = "D" & Format(Date, "yyyymmdd") ActiveSheet.Name = "D" & Format(Range("a1"), "yyyymmdd") ActiveSheet.Name = "D" & Format(Range("a1"), "yyyy_mmdd_hhmm")Also see http://www.mvps.org/dmcritchie/excel/pathname.htm...

How do I create 1 chart from 2 different sheets?
Using Excel 2003 how do I create one pie chart by using data from 2 different sheets? all of the examples i see are created by clicking and dragging data from one sheet. Jon Peltier has instructions for charting data from different sheets: http://www.peltiertech.com/Excel/ChartsHowTo/ChartDataFromDifferentSheets.html tclbeach wrote: > Using Excel 2003 how do I create one pie chart by using data from 2 different > sheets? all of the examples i see are created by clicking and dragging data > from one sheet. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.cont...

Loop to retrieve value in worsheet A and put into worksheet B
I am hoping someone out there cyber-world can help me with some code that I need. I have a workbook from an outside source. I need to loop through Worksheet named "Details", column A to find the characters "C:" and return the value from Worksheet "Details" , column B into Worksheet "New Details", Column A. There are blank rows in the data and the only designation between what would be considered a new record is a series of dashes (14 dashes to be exact). Is Anyone able to help with this? Anything is greatly appreciated. Is there an...

Help Excel 2007
Hoping that someone can help with this. I'm trying to print out a small worksheet on a '11x17' sheet. I've set the print area to the size I want and I have the scaling set to scale to '1 sheet wide x one sheet high' and the layout to 'landscape'. But there's no scaling happening. Cells still show up in a small corner of the sheet. I've never seen this behavior in any previous version. Any suggestions? Print area?? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Dab" <noThanks@hotmail.com> wr...

How do I display repeating text between worksheets only once on ta
Hi, I'm new to XLS and trying to work out how to display details from a XLS report I receive. I copy and paste the all details to a new work sheet. I want to display the names of the resources I'm tracking to a new work sheet. The catch is first cell (column a) starts blank first time this is run so I cant use vlookup as far as I can tell, Once the first name is displayed I then need it to skip this name as it repeats (sometimes) in the report - then display the next name and so on to each next free cell in the column. The 2nd time I run this I want any new names to be added to ...

Postmaster E-mail ID
Hi I want to change Postmaster E-mail ID with new Domain name, how to do this . Thanks Shaikh ...

server setting don't activate
I was using outlook 2003 and I didn’t find any problem to activate my account, But when I upgrade to the version 2007 wish is already activated , when I tried to set my same email account it don’t work Thanks a lot Fadi Fadi wrote: > I was using outlook 2003 and I didn�t find any problem to activate my > account, But when I upgrade to the version 2007 wish is already activated > , when I tried to set my same email account it don�t work Thanks a lot You cannot activate e-mail *accounts* at the mail server using Outlook. You have to do that with whatever means the e-mai...

Dynamic copying of data to new sheets
I need to create a seperate sheet in an excel workbook that contains th information from a data sheet. There are multiple columns and I need to seperate the rows out tha match values in one column. Number Status Notes 1 Fail none 2 Pass none 3 Not Run none 4 Missing none I need to make a seperate sheet for fail, pass, not run, and missing. I would like these sheets to update automatically, as the data sheet i quite large and it would take sometime to update the information b hand. Thanks, Quirthano -- Message posted from http://www.ExcelForum.com ...

product id
Is the product Id the same as the serial number? I have Microsoft Money 2004 PREMIUM edition that I just purchased. This name does not appear on the msft website and the serial number isn't recognized. I got the software from a friend who works at microsoft so I know there is no foul play. Can someone please direct me? In microsoft.public.money, "Dr Singh" <dolls15213@yahoo.com.(donotspamme)> wrote: >Is the product Id the same as the serial number? Yes. > I have Microsoft Money 2004 >PREMIUM edition that I just purchased. > >This name does n...

Return all sheets to cell A1
How do you return all sheets of a workbook to cell A1 all together? glenlee, one way, group the sheets and select A1, ungroup the sheets -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "glenlee" <glenlee@nospam.com> wrote in message news:4335dc27.14201560@news.verizon.net... > How do you return all sheets of a workbook to cell A1 all together? Grouping the sheets is not working....only one sheet goes back to cell...

Worksheet skipped
We created a 340 worksheet for 1 particular store. It keeps getting skipped. I have no idea why. There are 2 skus and they were both created in 2004, so they are not new. Never mind... We figured it out. "Rick@ASP" wrote: > We created a 340 worksheet for 1 particular store. It keeps getting skipped. > I have no idea why. There are 2 skus and they were both created in 2004, so > they are not new. ...

calling MS remove printer utility
Hi I am implementing a Printer delete utility. It is able to remove all the driver files. But it can not remove all the registry keys. Idea is to call the ms print remove utility. Is there any way to do that ? thanks in advance. Thanks & regards ~ Rudresh Look at http://www.robvanderwoude.com/index.html You can a lot of printer control with "RunDLL32.exe PrintUI.dll, PrintUIEntry ..." "Rudresh" wrote: > Hi > I am implementing a Printer delete utility. It is able to remove all the driver files. > But it can not remove all the registry keys. Idea is ...

Phone call notification form
I have a user that needs a phone call notification or phone call message form in Outlook 2000. I can't seem to find a template or form that matches this. I do have one user that has this form, but I don't know who to have her E-mail this form to another user or myself. Please help. Thanks, Dede They can open and then save the form as an OFT file (File | Save As...) -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Dede" <dclinch@citss.com> wro...

Service Call
We frequently have the need to move an item which is on a Service Call to a different ticket. Most of the time, these were put on a PO. Current functionality does not allow the part to be moved to a different Call without cancelling the PO and then re-creating it - not a good/reasonable solution. At a minimum, there should be a utility/button to remove the PO reference from the parts line thereby allowing the part to be deleted/moved. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, cl...

help w/deleting rows if 2 conditions & calling macro from other bo
hi, i found the code below and it works when i'm in the same workbook but how do i modify it so that i call from book 1 and it works on book 2. i also need to add a second condition .... any ideas?? what i want to do is delete all rows that DON'T have "Attendance" in column G and aferwards delete all rows that are "blank" in column J the macro will be called from the master.xls BUT will work on another file called raw.xls Sub stantial() Dim myrange, MyRange1 As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set myrange = Sheets(...

Three questions about fields in worksheets
Hello! I have a workbook that tracks patients (mothers) and contacts (mom's family members). The mother worksheet is named MAT_INF and the contacts one is named CONTACTS. 1. In MAT_INF, I have a field named CASE_NUMBER. How can I tell this field to automatically start out with a value like "2009-"? An example case number would look like 2009-001 or 2010-099. 2. Once CASE_NUMBER, LAST_NAME_MOM and FIRST_NAME_MOM have been populated by the user in MAT_INF, how can I automate adding those values to the CONTACT sheet, where the corresponding fields are named CASE_N...

From Command button, Find records beginning with ID#
ID# is the primary key. I have a form where you can enter an ID and the Command button finds the record that is equal to the ID. This uses stLinkCriteria (through the wizard). What I really want is for all the records beginning with the ID entered to be available on the form. I can't make an 'Or' condition work with the stLinkCriteria and that may be because I'm still in early learning process. I can change the condition to be > than the ID and that works fine, except it does not show the record the person actually entered; it begins with the next record. I thou...

Inserting filename in Excel worksheet
Hey guys I want to show the filename/path directly in a celle, of course I can show it in the prewiev option, but are there any function to make it appear as a value in a cell. If not: How will a macro that "make the cake" be programmed? Hopefully regards Snoopy http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Snoopy" <blaksnm@jjuc.no> wrote in message news:1186554383.112521.287440@l70g2000hse.googlegroups.com... > Hey guys > I want to show the filename/path d...

Data collecting from different sheets
Hi! I have a workbook where I collect data to "raw data sheets". In theese sheets the average of eath data category is calculated. What I need is a macro that i can run in each new sheet so that theese averages are filled into a presentation sheet. To make things more complicated there will be different amounts of data (rows) in eath sheet. The setup I need to work with is this: Column A: States the type of data (only in the row where the average data is, otherwise blank) Columns C,E,G,I,K,M,O,P: The data I wish to copy. (But only in the average data rows, determ...

Wrong Investment in Download Activity
I'm running into a frustrating problem that I'm hoping someone has an idea as to how to solve. For what ever reason, Money is trying to match an existing investment to a new investment. Everytime I download my investment statements, it sees I have X shares of MSFT, decides that it doesn't match the existing Microsoft investment, and creates a new investment of "Microsoft Stock". It then adds X shares (essentially doubling it) into the account. I've deleted the "Microsoft Stock" investment multiple times, but it keeps recreating it. How do I get i...

Not able to close an activity within Outlook
When we trial to close an activity in Outlook 2003 we've got the following message. An error has occurred. For more information, contact your system administrator. We look in hte event viewer of the server and we have this description. Error: Can't clean up the following tables: Activity. If we access via the web we do not have this problem. Does anyone resoved this ? Regards ...