User defined functions aware of what cell they are placed in?

Hi,

I would like to make a user defined function which needs to know in
what cell and what worksheet it is placed in. I will be using this UDF
in multiple cells on multiple worksheets. I originally just passed the
cell row and column as parameters to the UDF however this ended up
updating all worksheets and not just the one the UDF was on.

Is there any way to do this?

0
9/18/2007 12:20:07 PM
excel 39879 articles. 2 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 14

Option Explicit
function myfunct(something as somethingelse) as something

   msgbox application.caller.address & vblf _
          & application.caller.parent.name  & vblf _
          & application.caller.parent.parent.name 

End function

(application.caller is what you're looking for)

Chris wrote:
> 
> Hi,
> 
> I would like to make a user defined function which needs to know in
> what cell and what worksheet it is placed in. I will be using this UDF
> in multiple cells on multiple worksheets. I originally just passed the
> cell row and column as parameters to the UDF however this ended up
> updating all worksheets and not just the one the UDF was on.
> 
> Is there any way to do this?

-- 

Dave Peterson
0
petersod (12004)
9/18/2007 12:35:21 PM
Thanks!

Exactly what I've been looking for

On Sep 18, 8:35 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Option Explicit
> function myfunct(something as somethingelse) as something
>
>    msgbox application.caller.address & vblf _
>           & application.caller.parent.name  & vblf _
>           & application.caller.parent.parent.name
>
> End function
>
> (application.caller is what you're looking for)
>
> Chris wrote:
>
> > Hi,
>
> > I would like to make a user defined function which needs to know in
> > what cell and what worksheet it is placed in. I will be using this UDF
> > in multiple cells on multiple worksheets. I originally just passed the
> > cell row and column as parameters to the UDF however this ended up
> > updating all worksheets and not just the one the UDF was on.
>
> > Is there any way to do this?
>
> --
>
> Dave Peterson


0
9/18/2007 1:26:34 PM
Reply:

Similar Artilces:

User Form Landscape
Hi All On a userform I have the following code Private Sub CmdPrint1_Click() UserForm1.PrintForm End Sub Is it possible without changing the print option in the control panel to have the UserForm print in Landscape by adding to or changing the above code. Thanks for any help Cheers Mully ...

Combining Cells #2
I wish to combine two cells, want the cell to appear as Chicago Golf Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 is cell b1. When I use the formula a1&" "&b1 the date is shown as a number. Is there anyway it can be shown as a date? This cell will then be used to save the file, using a macro. Hi try =A1&" "&TEXT(B1,"MM/DD/YY") -- Regards Frank Kabel Frankfurt, Germany ABYPFCS wrote: > I wish to combine two cells, want the cell to appear as Chicago Golf > Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 ...

Setting up a new user
I'm experimenting with Exchange server 2003 in a lab environment. I've got one user set up and able to access his mailbox. I then created a second user on the server. When I try to connect to his mailbox from a client PC, Outlook tells me: "Unable to open your default e-mail folders. You must connect to your Microsoft Exchange Server computer with the current profile before you can synchronize your folders with your offline folder file". What the heck does that mean? How do I do what it is asking? The client PC is running XP Pro, and I did restart it and log on to the...

cell formatting zero padding on binary no's
I'm struggling through doing some binary math on excel. Ugh. You'd think Excel is written by programmers, so they would see the value of having some programmer-friendly bitwise math and binary, octal and hex formatting stuff in there, if for no other reason than they could use it themselves. Anyway, that's my rant, here's my question I have some numbers in cells that I convert to binary C5: 55 DEC2BIN(C5) gives me 1010101 I'd really like to display that as 0101 0101 .... ie with a leading zero and a space between the nibbles. It appears that ce...

The numbers entered change to symbols when leaving the cell
I am trying to enter 10,418 in a dollar amount, when I leave the cell it changes to this :########. How can I fix this? Make the column wider, or the font size smaller. George Gee "CH100688" <CH100688@discussions.microsoft.com> wrote in message news:303ADD36-C519-4A11-B38D-86E61DA66D53@microsoft.com... >I am trying to enter 10,418 in a dollar amount, when I leave the cell it > changes to this :########. How can I fix this? Thanks...that worked!! "George Gee" wrote: > Make the column wider, or the font size smaller. > > George Gee > >...

scrolling functionality to edit box
Hello, My application consists of drawing a series of rectangles in the edit box. I'm currently having some problems adding both vertical and horizontal scrolling features to an edit box in a dialog. Though I have checked "Horizontal Scroll", "Auto HScroll", "Vertical Scroll" and "Auto VScroll" in the "Styles" tab of this edit box, no scrolling goes on when I draw to my edit box. When I do a paint, the drawing exceeds the width of the edit box and even paints up to the borders of the dialog. What's strange is if I type text into the e...

CHR() function not working in Excel 2003
I've written a VBA application in Excel 2002, which works correctly. This should work on all recent versions of Excel, so I've been testing it on Excel 2003. This includes the lines: Dim a as String a = "text" + Chr(34) On Excel 2003, I'm getting the error "Compile error: Can't find project or library", and the Chr function is highlighted. If it's helpful, I've tried commenting this line out and I also get the same error elsewhere when I use the Space() function. The rest of the code runs successfully. I've seen elsewhere that I should look ...

Hiding formula/functions
I received a spreadsheet that someone wants me to help them with. The vba code that runs when a button is clicked exports a range to a csv file. I entered the following formula into the first column so that it would enter a 1 only when something is entered into column N. This part works. =IF(N8<>"",1,"") However, when I know export the data it thinks it should export all 92 rows because it thinks the formula is part of the data. How do I tell the code to NOT include the formula when it searches for a blank row? Thanks, I am thinking there is...

"Adding" Named Users to Business Portal (3.0 or 4.0)
Hello: A client of ours already has Business Portal, but they want to set it up with 25 Named Customers to be able to view their invoices, shipping, and inventory. The time to set this up is pretty nominal, isn't it? I mean, is it a matter of simply entering new registration keys and showing the customers how to use the Portal or is there a lot more work involved than this? Thanks! childofthe1980s Nevermind....I researched and got my answers "childofthe1980s" wrote: > Hello: > > A client of ours already has Business Portal, but they want to set it up > w...

Mail Merge Functionality in 3.0
I am told that customizing mail merge documents for MSCRM 1.2 is very limited. Example, you cannot add any desired additional fields on the mail merge template. My question is, in MSCRM 3.0 can this be done? Or do you still need to purchase a third party add on program? Thank you in advance for your help! ~Cayla hmmm sort of looks like you can use custom fields in everything apart from activities...I have not tried it yet...anyone care to comment? ========================= John O'Donnell Microsoft CRM MVP http://www.microsoft.com/businesssolutions/crm/community/microsoft_crm_co...

Copy Cell Value (text) to Header?
I want to grab the text in a cell (company's name) and automatically copy it to the header using a macro. Thanks for any help. Mike Mike, Try something like ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("A1").Text -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "theFrog" <none@none.com> wrote in message news:3uyrb.8918$si2.4367128@news4.srv.hcvlny.cv.net... > I want to grab the text in a cell (company's name) and automatically copy it > to the header using a macro. ...

Cell Data Change to Trigger Macro
Is there a way to trigger a macro whenever data changes in a cell? The value in the cell will be varying numbers, so it can't be TRUE/FALSE etc. Thanks Mike, Yes. Put it in the sheet module, instead of a regular module. It should look like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then MsgBox "You changed cell A1, you did." End If If Not Intersect(Target, Range("A1").EntireColumn) Is Nothing Then MsgBox "You changed something in column A. You sure did." End If End ...

cell reference from =max
What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet...

Install add-in with user defined functions 02-12-08
Hi, Looking for an easy way to make user defined functions available for any mdb to open in Access 2003. I thought: create and mda, write VBA code and use Tools - Add-in Manager. That doesn't work because of a USysRegInfo table ...? Do I realy have to go through that 'misery' or is a simpler / better way? Many thanks, Frans van Zelm ...

Can I use a logo in place of the standard chart markers
I was hoping someone could help me figure out how to use my company's logo as my series marker on a chart I am working on. Hi Kimberly, Take a look at Jon Peltier's page to see the options available. http://peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html Kimberly Bassininsky wrote: > I was hoping someone could help me figure out how to use my company's logo > as my series marker on a chart I am working on. > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

How do I move some information in one cell to another cell?
I have a column of information on a worksheet. Each cell in this column holds a number that looks like this 0408015568GH. I need to take the "GH" and move it to another column on the same worksheet. How do I do this? Hi Deborah, Not sure if you want the original to remain. If you don't a macro might be better. Don't know if it is always the last two characters but the following will handle it that way. =LEFT(A1,LEN(A1)-2) =RIGHT(A1,2) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/e...

How to merge columns and rows into one cell besides using Merge and Center Icon? #2
J.E: Although David McRitchie's page seems to have everything about basic macros which you have said that this is the only solution to my question, I have tried the example of recording and running macros on my question but it doesnt seem to work; the rows and columns did not merge into one cell. heres how my question goes; I have text data value in D1; Super-Com Distribution Pte Ltd, and in D2; Sales and Stock Half-Yearly Report For 2002. I need to merge the rows and columns from A1 to J2 into one cell and centralise the data within. Could you just show me the way of doing it? I'm b...

I want to copy the same image into worksheet with a function
I wish to use a function on a worksheet to copy and image from another worksheet in the same workbook. The function "CopyImage" is listed but no help to indentify the parameters Functions return values to cells in which they reside. I do not know of a "copyimage" function. Maybe you mean the CopyPicture method in VBA? Or just copy and paste? Gord Dibben MS Excel MVP On Thu, 7 Jan 2010 11:35:01 -0800, Garry <Garry@discussions.microsoft.com> wrote: >I wish to use a function on a worksheet to copy and image from another >worksheet in...

Event ID 23009 Can't migrate user from Exchange 5.5 to 2003
I am getting the following error: Source: MSExchangeMig Error Event ID 23009 Error processing attribute 'mail' for user LDAP://SLG_PDC/cn=Support,cn=Recipients,ou=siboneylg,o=Sibo neyLG Error code=0x800004005. The user will be skipped. I am trying to migrate users from an NT 4 server with Exchange 5.5 to a Win 2003 server, with Exchange 2003. I have followed all the steps on the CD in Exchange Deployment Tools and Deployment the first 2003 Exchange server and then the post install steps. I did not receive any errors until I tried to migrage a user. Any ideals? ...

Cell formatting #17
Periodically, all my formulas show the formula rather than the result. I've tried changing the cell format but it doesn't revert. I have to close the file without saving and then reopen. What causes this and how can I revers it? Thanks Try Ctrl ~ http://www.rondebruin.nl/print.htm#Formulas -- Regards Ron de Bruin http://www.rondebruin.nl "Wizzer" <anonymous@discussions.microsoft.com> wrote in message news:674c01c52805$2576fe30$a501280a@phx.gbl... > Periodically, all my formulas show the formula rather than > the result. I've tried changing th...

Option Explicit
Dear all, I'm experiencing an issue that I think may be a bug. I have a form with a command button, btnNewProcess. In the Code behind the Form, I have the following routine: Private Sub btnNewProcess_Click() On Error GoTo Err_btnNewProcess_Click DoCmd.SetWarnings False Dim stDocName As String stDocName = "qdmQuoteHeadersWeWant" DoCmd.OpenQuery stDocName, acNormal, acEdit stDocName = "qdmQuoteDataExtract" DoCmd.OpenQuery stDocName, acNormal, acEdit MsgBox "Extract Completed", vbInformation, "Succe...

cannot drop schema for 'user'
I am having trouble with a login for GP. I tried running the MassDropUserSchemas.sql script per tk 919723. I get the error for that user Cannot drop schema 'user' because it is being referenced by object 'amAutoGrantsys'. What is the next step? Thanks -- Crystal Roach ...

Conditional Format four top four cells
Using this for conditional formating, however if I have a blank cell it still changes the cell format according to the rule. =A1>=LARGE($A$1:$A$5,4) Data 2 3 5 7 8 Would like the 3,5,7,8 to change cell color based on formula in coditional Format Thanks Rob Rob, Your 'blank' cell is not blank - it has a space or a null string ("") in it. Try =AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4)) HTH, Bernie MS Excel MVP "Rob" <Rob@discussions.microsoft.com> wrote in message news:F382DA31-1889-4C1C-8178-3923A9BDB953@micro...

How do I split cells in excel when making a table?
I am making a table. The first column is my primary column. I want the second column to contain multiple choices and I want he user to pick multiple choices, how do I do that should I split the cells, if so how? Debra Dalgleish has a web site where you can learn about creating drop down lists using "Data Validation". http://www.contextures.com/xlDataVal01.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -----------------------------------------------------------...

keeping named cell references.
Spreadsheet File B references by name a cell value in Spreadsheet File A. When File A is opened, the references in B convert from the range name to the cell location (i.e from "Value" to B2) and stay that way even when File A is closed. How do I maintain range name references when the referenced file is opened? ...