SheetName (Application.Caller.Parent.Name) Forces Excel to Save File

I have a VBA function in Excel that I have been using for years that will 
display the sheet name in a cell. The VBA function is as follows:

Function SheetName() As String
   Application.Volatile
   SheetName = Application.Caller.Parent.Name
End Function

This works great. However, I have found that if I am using this function if 
I open a worksheet and not change anything Excel will prompt me if I want to 
save the worksheet if I have not made any changes. Why is this happening and 
is there a way to prevent this from happening?

Thanks,

Mike 


0
11/21/2006 11:14:34 AM
excel 39880 articles. 2 followers. Follow

6 Replies
598 Views

Similar Articles

[PageSpeed] 42

The problem may be Application.Volitile

If the function re-calculates, Excel may suspect that the worksheet has 
changed.
-- 
Gary's Student


"Mike McCollister" wrote:

> I have a VBA function in Excel that I have been using for years that will 
> display the sheet name in a cell. The VBA function is as follows:
> 
> Function SheetName() As String
>    Application.Volatile
>    SheetName = Application.Caller.Parent.Name
> End Function
> 
> This works great. However, I have found that if I am using this function if 
> I open a worksheet and not change anything Excel will prompt me if I want to 
> save the worksheet if I have not made any changes. Why is this happening and 
> is there a way to prevent this from happening?
> 
> Thanks,
> 
> Mike 
> 
> 
> 
0
GarysStudent (1572)
11/21/2006 11:39:02 AM
You could just use a worksheet function

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mike McCollister" <Mike_McCollister@msn.com> wrote in message
news:e8a#64VDHHA.1012@TK2MSFTNGP04.phx.gbl...
> I have a VBA function in Excel that I have been using for years that will
> display the sheet name in a cell. The VBA function is as follows:
>
> Function SheetName() As String
>    Application.Volatile
>    SheetName = Application.Caller.Parent.Name
> End Function
>
> This works great. However, I have found that if I am using this function
if
> I open a worksheet and not change anything Excel will prompt me if I want
to
> save the worksheet if I have not made any changes. Why is this happening
and
> is there a way to prevent this from happening?
>
> Thanks,
>
> Mike
>
>


0
bob.NGs (282)
11/21/2006 1:12:43 PM
It will always recalculate upon opening, so it is definitely the
Application.Volatile.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:B5DCC785-515D-4322-8F2A-DBA45F150587@microsoft.com...
> The problem may be Application.Volitile
>
> If the function re-calculates, Excel may suspect that the worksheet has
> changed.
> --
> Gary's Student
>
>
> "Mike McCollister" wrote:
>
> > I have a VBA function in Excel that I have been using for years that
will
> > display the sheet name in a cell. The VBA function is as follows:
> >
> > Function SheetName() As String
> >    Application.Volatile
> >    SheetName = Application.Caller.Parent.Name
> > End Function
> >
> > This works great. However, I have found that if I am using this function
if
> > I open a worksheet and not change anything Excel will prompt me if I
want to
> > save the worksheet if I have not made any changes. Why is this happening
and
> > is there a way to prevent this from happening?
> >
> > Thanks,
> >
> > Mike
> >
> >
> >


0
bob.NGs (282)
11/21/2006 1:13:17 PM
Won't this suffer the same volatility problem?

Bob Phillips wrote:
> 
> You could just use a worksheet function
> 
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
> 
> --
> 
> HTH
> 
> Bob Phillips
> 
> (replace xxxx in the email address with gmail if mailing direct)
> 
> "Mike McCollister" <Mike_McCollister@msn.com> wrote in message
> news:e8a#64VDHHA.1012@TK2MSFTNGP04.phx.gbl...
> > I have a VBA function in Excel that I have been using for years that will
> > display the sheet name in a cell. The VBA function is as follows:
> >
> > Function SheetName() As String
> >    Application.Volatile
> >    SheetName = Application.Caller.Parent.Name
> > End Function
> >
> > This works great. However, I have found that if I am using this function
> if
> > I open a worksheet and not change anything Excel will prompt me if I want
> to
> > save the worksheet if I have not made any changes. Why is this happening
> and
> > is there a way to prevent this from happening?
> >
> > Thanks,
> >
> > Mike
> >
> >

-- 

Dave Peterson
0
petersod (12003)
11/21/2006 1:36:02 PM
Dang. If I remove that applicaiton.volatile it is not updating. I guess that 
I will stay with the function.

Mike

"Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
news:O5YrQ7WDHHA.3228@TK2MSFTNGP03.phx.gbl...
> It will always recalculate upon opening, so it is definitely the
> Application.Volatile.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in 
> message
> news:B5DCC785-515D-4322-8F2A-DBA45F150587@microsoft.com...
>> The problem may be Application.Volitile
>>
>> If the function re-calculates, Excel may suspect that the worksheet has
>> changed.
>> --
>> Gary's Student
>>
>>
>> "Mike McCollister" wrote:
>>
>> > I have a VBA function in Excel that I have been using for years that
> will
>> > display the sheet name in a cell. The VBA function is as follows:
>> >
>> > Function SheetName() As String
>> >    Application.Volatile
>> >    SheetName = Application.Caller.Parent.Name
>> > End Function
>> >
>> > This works great. However, I have found that if I am using this 
>> > function
> if
>> > I open a worksheet and not change anything Excel will prompt me if I
> want to
>> > save the worksheet if I have not made any changes. Why is this 
>> > happening
> and
>> > is there a way to prevent this from happening?
>> >
>> > Thanks,
>> >
>> > Mike
>> >
>> >
>> >
>
> 


0
11/22/2006 12:33:37 AM
I'll consider that. It is just not as friendly as the SheetName function 
that I have written up.

Mike

"Bob Phillips" <bob.NGs@xxxx.com> wrote in message 
news:Op$H76WDHHA.4952@TK2MSFTNGP06.phx.gbl...
> You could just use a worksheet function
>
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Mike McCollister" <Mike_McCollister@msn.com> wrote in message
> news:e8a#64VDHHA.1012@TK2MSFTNGP04.phx.gbl...
>> I have a VBA function in Excel that I have been using for years that will
>> display the sheet name in a cell. The VBA function is as follows:
>>
>> Function SheetName() As String
>>    Application.Volatile
>>    SheetName = Application.Caller.Parent.Name
>> End Function
>>
>> This works great. However, I have found that if I am using this function
> if
>> I open a worksheet and not change anything Excel will prompt me if I want
> to
>> save the worksheet if I have not made any changes. Why is this happening
> and
>> is there a way to prevent this from happening?
>>
>> Thanks,
>>
>> Mike
>>
>>
>
> 


0
11/22/2006 12:34:24 AM
Reply:

Similar Artilces:

Cannot open excel file on doubleclick ... HELP
Hello I have this strange problem on 3 computers: When I doubleclick on an excel file (in windows explorer, or a shortcut) excel opens, but it only shows the top and bottom bar, the middle shows the desktop of the PC. - I get this when opening a file from the network, and from the C drive - I get this with ALL excel files - there is nothing in the startup folder, nor macro's, .... So I think the problem is excel.... btw, al users are using excel 2002 (xp) I already tried a repair/reinstall, didn't help I tried the /regserver trick: didn't help I tried to import .XLS from HKCR, ...

Sheetname
Hi, could any one help me with the following: I want to change a sheetname with a formula. Is it possible and if yes how? Thanks a lot -- Message posted from http://www.ExcelForum.com Not with a formula - you'll need to use VBA. This should do it: Sub a Sheets("sheet1").Name="AnotherSheetname" end sub -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "maxximuss" <maxximuss.yhppz@excelforum-nospam.com> wrote in message news:maxximuss.yhppz@excelforu...

Creating Macros in Excel
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am sharing this with all of you.Please add on to this discussion and let me know if i am correct or not. <br> 1. Click on Start>Programs>Microsoft office>Microsoft Excel to open the application. <br><br>2. Go to Tools>Macro>Record New Macro. <br><br>3. A pop up window named Record Macro will appear. Enter a name for the macro under Macro <br><br>4. Select the location of the macro under Store macro in saving this macro in the Personal Macro Workbook or...

Silent Failure of Applications
I am making a serious attempt to use Win7-x64 although something happens nearly every day to make me want to go back to XP Pro x64 :-( The latest event is the silent failure of an application to run. It has been working for weeks but last night it just didn't run (it's kicked off by the task scheduler). I then discovered it wouldn't run manually either. I re-booted and all is well again, for how long I don't know. There is nothing in the event log as far as I can see - although that's almost impossible to find your way around. I also found this morning ...

export from excel to word #2
looking for possibilityfor export single cell from excel spredsheet to the Word Can anybody help -- Marek With both the spreadsheet and Word document open, click on the cell you wish to export and: Edit > Copy Then click in the appropriate place in the Word Document and: CNTRL-v Note that if you want the destination to "look like" a cell, then create a 1 x 1 table in the Word doc and paste there. -- Gary''s Student - gsnu200812 "lermar" wrote: > looking for possibilityfor export single cell from excel spredsheet to the Word > Can anybody help &g...

2004 trial file won't open
I tried the trial 2004 version of Money and didn't bite. It saved my file but now won't recognize my password from 2002. Is MS trying to force those who use the free trial to buy 2004 by saving to an unknown password? Please help me recover my file! When the trial upgraded your data file it warned you that Money files, once upgraded, were not backward compatible. See http://umpmfaq.info/faqdb.php?q=84 for the recovery paths. "jmkosloski" <jmkosloski@peopelpc.com> wrote in message news:71ba01c42fe4$3bdd14d0$a101280a@phx.gbl... > I tried the trial 2004 vers...

Maximising a SDI application at startup
I'm guessing this is a really easy question. I want my application to be maximised when it is executed. At present in the PreCreateWindow(), i'm setting cs.cx and cs.cy to appropriately large values. However, I'm sure there must be a better way. What's wrong with setting cx and cy in PrecreateWindow ? Anyway, this works BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs) { RECT screen; SystemParametersInfo ( SPI_GETWORKAREA, 0, & screen, 0 ); cs.cx = screen.right; cs.cy = screen.bottom; cs.y = screen.top; cs.x = screen.left; return CFrameWnd...

Mail Server Name on RPC over HTTPS Clients?
Hi! I have the following network: Internet >> NetscreenFirewall_1 >> ISA 2004 >> NetscreenFirewall_2 >> Internal Network with two Exchange 2003 FE and BE servers and one global catalog. The external clients can access Exchange over OWA/POP3/IMAP4/SMPT without any problem. The same clients can access Exchange 2003 by using RPC over HTTPS only when they have the name of Back-End Exchange name as mail server name on Outlook client. Back-End Exchange name is NOT published by using ISA. Only FE is published. Also the external DNS server has NO RECORD for Back-End Exc...

How create a manifest for a VC++ 5 application?
I have an application developed under VC++ 5 that I want to install properly under Vista (i.e. not rely on virtualisation etc). I understand that I need to create a manifest for my program that will specify: (a) the execution level it requires (which is 'asInvoker') (b) What code dependencies it has (DLLs it calls into?) Actually do I even need to bother with (b)? Is this required for Vista? Is it just (a) that I need? If I do need, (b): the application (call it XYZ.EXE) is built using the MFC42.DLL and also the MSVCRT.DLL. These are installed (using innosetup) in the syste...

Excel Reports
I have created a dynamic Excel spreadsheet and uploaded it into the Reports area in CRM 3.0. The report is based on Opportunity records. When I add a new Opportunity and then run the report, after prompting me to open the Excel spreadsheet with the usual dialog box, sometimes all that is launched on the screen is the small toolbar to refresh the data. The report does not open. Pressing F5 to refresh does not work, and the only way around the problem is to shut down CRM and then open up again to run the report. Sometimes it works fine and other times it doesn't. Does anyone els...

unicode application
hi, Priyanka here, this is a question related to VB. i want to make the VB application unicode based. i am not able to do it right now. if anybody knows please let me know about this. ...

.dbx files
For some reason my outlook express just cleaned up files and place them in .dbx format. I would like to restore them because i didn't ask for them to be cleaned up. Can anybody help me to get my mail back either into outlook XP or outlook express? Tammie wrote: > For some reason my outlook express just cleaned up files > and place them in .dbx format. I would like to restore > them because i didn't ask for them to be cleaned up. Can > anybody help me to get my mail back either into outlook > XP or outlook express? *.mbx is used bu Outlook Express 5 ->, s� this ...

Is it possible to store .pdf files in an access table
I am using ACCESS 2000 and checking to see if I can store .pdf file in the same table where I store my test results. If so how I can I do that? If not in ACCESS 2000 is it possible with ACCESS 2007? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 Why would you want to? Why not store the PDF file in a folder and just store the hyperlink to the files location in the database. Storing other files in an Access database, even if it was possible, would quickly lead to a huge and inefficient database file. ...

Application defined or object defined error
Hi All Here is a strange one I have a command button that envokes code "mnuExit" - this code does a bunch of different things and then closes the excel file. I have more code in the BeforeClose and Workbook_Deactivate events. Everything runs properly if there are no other Excel Files open - however - IF there are other excel files open once all the code has finished running I get a dialogue box that says: "Applicatiob-defined or object-defined error" there is no 1004 or anything else - and there is nothing in my code that is causing this error - I have s...

How do I control the preferences on inserting a word file
When I past one word file into another word file the font and spacing changes and it doesn't appear to be consistent with either document. By default, the formatting of the target document determines the formatting of pasted text (unless the pasted text has direct formatting applied to it). However, in recent versions of Word, you could try experimenting with the Paste Options (which show as you paste). For more on paste options, see http://www.shaunakelly.com/word/styles/HowPasteOptionsWorks.html. -- Stefan Blom Microsoft Word MVP "Clay W. Oberhausen" <...

Excel Help Menu shortcut
Can I place a shortcut icon to the Excel Help menu on my desktop? If so, how? (Perhaps, Excel must be opened for this to work?) Thanks, FLKulchar Just browse to the Excel help file. Its name and location will vary depending on your version but in Excel 2003 its name is XLMAIN11.CHM and it's in the OFFICE11\1033 folder (for English language versions). -- Jim "F. Lawrence Kulchar" <FLawrenceKulchar@discussions.microsoft.com> wrote in message news:A3498927-FBD4-4BED-864B-86690CDA99AA@microsoft.com... > Can I place a shortcut icon to the Excel Help menu on my desk...

Excel 2007: Missing drop-down buttons in Pivot Charts?
Dear all, when creating a pivot chart from a pivot table, there appears only a very basic chart. What is basically missing are the drop-down buttons in which one can select the variables to display. In addition drag-and-drop within the table does not work any longer (not surprising, as the buttons are missing), but it does not work any longer as well from the variable list. All configuration is now done via the "PivotCart Filter Pane". However, this filter pane is very unfortunate, since it does not display the names of the variables in the diagram any longer. So, if you have...

multiple users with same domain logon name
We have 2 active users with the same domain logon name (not sure how we were able to do this as I can't repeat in non-production environments). I can't disable one and leave the other active as when I do that and the users tries to logon they get access denied. I can't change the domain logon name as that is not an editable field. Any ideas on how I can get these 2 user to just one active user? Each "account" object in AD has a unique set of object attributes. Even though it has the same "Name". If you create an account and then delete it and create the...

Start Application rule with arguments?
I would like to start an application in a rule but I need to pass either the mail's subject or body as the argument to the application. Is there any way to do that without using a third-party component? ...

How do I show Latitude and Longitude in Excel Table
I need to show latitude and longitude in my Merchant Navy Log abstract. can anybody please tell me how to show degrees, minutes and seconds Thanks have a look at: http://www.cpearson.com/Excel/latlong.aspx On 30 Lis, 08:11, sea_eagle <sea_ea...@discussions.microsoft.com> wrote: > I need to show latitude and longitude in my Merchant Navy Log abstract. can > anybody please tell me how to show degrees, minutes and seconds > > Thanks ...

Calling Excel from Word with parameters
Hi. I'm posting this here after not having had a reply in the Office_Developer>Automation forum for 4 days. I need to call Excel from within Word with parameters. Can you help with the syntax for the call and where (and how) I should declare and therefore "receive" and handle these parameters in Excel??? Is there a default auto start procedure which accepts parameters? Please specify the call with 3 parameters : 1 text/string, 1 numeric (integer) and 1 date. Thanks in advance I saw Ann Troy's response further down in the list, so I guess that the Workbook_Open pr...

return file name
What do I put in a cell to get it to return the file name? Is there a way, inversely, to get the file to adopt a default name equal to the content of a cell when saving? chuck wrote: > What do I put in a cell to get it to return the file name? Is there a way, > inversely, to get the file to adopt a default name equal to the content of a > cell when saving? To get the file name... =MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1), FIND("[",CELL("filename",A1))+1,255) File name and path... =SUBSTIT...

saving a record
I'm aware of two ways to save a record when you want to force a save with VBA: 1. If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord and 2. If Me.Dirty Then Me.Dirty = False Are they both equivalent, or is one better than the other? Thanks in advance, Paul RunCommand acCmdSaveRecord saves the record in the active form (whichever that is.) Me.Dirty = False is explicit about which form's record to save, so it's more reliable if multiple forms happen to be open. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - htt...

Find Worksheet Name without On Error Resume Next
Is there a way to ensure a worksheet is in a workbook without using the On Error Resume Next statement? I have the name of the worksheet, now I want to search the workbook for that sheet, how do I do that? -- Cheers, Ryan Hi Ryan It can be done like this: Dim IsThere As Boolean TargetSh = "Sheet1" For Each sh In ThisWorkbook.Sheets If sh.Name = TargetSh Then IsThere = True Exit For End If Next If IsThere Then MsgBox ("Is in the workbook") Else MsgBox ("Is not in the workbook") End If Regards, Per ...

Problem with IRM created word/excel documents created before 18/11
I can't open any of my word and excel files that have IRM protection applied - problem apparently widespread - I believe microsoft are aware of this problem, can it be fixed from their end. Allowing me to then open affected files. Note: I have created a test file applied IRM since 18/11/2009 and can open the file without a problem - still unable to open files with IRM created prior to 18/11/2009 -- gwaxiom ...