 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006procedure continuously being called
I am using "application.volatile" in a function procedure in order for the procedure to be called. The function does not refer to a cell value and therefore won't be called unless .volatile is used. Basically, what I am trying to do is to set the worksheet background to a particular .gif file when the workbook is changed to/from "read-only". Since my workbook uses DDE links, it calculates continuously. Therefore, the function is called continuously, slows the workbook down and makes the screen flicker as it continuously sets the background picture. I have separated this task into the following two functions to try to keep the function that sets the background (function #2) to only run when the read-only status (function #1) changes, but it still does it continuously: #1 Function read_only_status() As Byte Application.Volatile True If ActiveWorkbook.ReadOnly = True Then read_only_status = 1 End If If ActiveWorkbook.ReadOnly = False Then read_only_status = 2 End If End Function #2 Function set_background(status As Byte) If status = 1 Then Worksheets("a.book").SetBackgroundPicture "c:watermark.gif" End If If status = 2 Then Worksheets("a.book").SetBackgroundPicture Delete End If End Function Any suggestions as to how I can get this procedure to NOT run continuously and still actually work?? Thank you. Since a workbook cannot switch status to/from read-only by magic, why test its status continuously? So, maybe you should start by answering the question "when/how does the status of the workbook change?"  SignatureRegards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions > I am using "application.volatile" in a function procedure in order for the > procedure to be called. The function does not refer to a cell value and [quoted text clipped - 38 lines] > Any suggestions as to how I can get this procedure to NOT run continuously > and still actually work?? Thank you. Kind of a long story and the result of not using proper methods I guess... Multiple users have open and make changes to a single workbook throughout the day. File sharing is not used. So, all but one user is viewing in "read-only". If a read-only user wants to make changes he/she has to switch to "read-write". Often, someone will not realize they are in "read-only" and do a bunch of work only to realize they can't save the changes. So, we are looking for a way to set up an obvious reminder, flag or indicator (other than the text "read-only" on the header bar) that displays when the workbook is in read-only mode. Changing the background is an idea we came up with. Thanks. > Since a workbook cannot switch status to/from read-only by magic, why > test its status continuously? So, maybe you should start by answering [quoted text clipped - 42 lines] > > Any suggestions as to how I can get this procedure to NOT run continuously > > and still actually work?? Thank you. If someone were to ignore the dialog box that forces one to explicitly open a workbook as read-only and then ignore the caption that indicates the read-only status, are you sure they would not ignore a watermark? An option to consider is to use the Workbook_Open event procedure to check the workbook's ReadOnly status. If it is true protect all the sheets so that none can be changed. And, if you still want to use a background image, use the same event procedure to do the needful. Bottom line. If you must do something, do it just once when the workbook is opened. SignatureRegards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions > Kind of a long story and the result of not using proper methods I guess... > [quoted text clipped - 55 lines] > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > and still actually work?? Thank you. Thanks for your response. I have previously tried the Workbook_Open option and...of course...it only works when the workbook is opened. It doesn't work when the workbook is already open in read/write and is toggled to read-only (which in practice is what takes place 95% of the time). However, that still may the best option, for lack of a better. > If someone were to ignore the dialog box that forces one to explicitly > open a workbook as read-only and then ignore the caption that indicates [quoted text clipped - 67 lines] > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > and still actually work?? Thank you. I am curious. How does one "toggle" a workbook that is in read-write mode into read-only mode? SignatureRegards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions > Thanks for your response. I have previously tried the Workbook_Open option > and...of course...it only works when the workbook is opened. It doesn't work [quoted text clipped - 73 lines] > > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > > and still actually work?? Thank you. ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, Notify:=True I sometimes open workbooks that I don't want changed in read/write mode. So I've added the "Toggle Read Only" icon to a toolbar. It's in the File category in xl2003. > I am curious. How does one "toggle" a workbook that is in read-write > mode into read-only mode? [quoted text clipped - 100 lines] > > > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > > > and still actually work?? Thank you. SignatureDave Peterson Interesting. As you may suspect, I had searched both XL and VBA help for 'readonly' and 'toggle readonly' before posting. So, it appears the OP has four choices -- other than the current one. 1) Do nothing if the readonly status is toggled by the user. That person is explicitly opening the file as readonly or toggling it into readonly status and then has a 'read only' indicator in the caption. 2) If the change (or opening of the file) is done programmatically, that would provide the OP with the perfect opportunity to add the desired watermark to the file. Of course, by doing so, a 'readonly' file has been modified. 3) Use a OnTime routine that runs every so often and checks the status of the workbook. If readonly add the desired watermark -- of course, that will cause the book to be marked as 'changed.' 4) Hook the 'Toggle readonly' button to custom code possibly with a technique along the lines of 'Hooking command bar control events" (pp. 237-?) in Professional Excel Development by Bullen, Bovey, and Green (http://www.amazon.com/exec/obidos/ASIN/0321262506/tusharmehtaco- 20/103-9755819-5239853). SignatureRegards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions > ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly > ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, Notify:=True [quoted text clipped - 107 lines] > > > > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > > > > and still actually work?? Thank you. I think the key to this is to find the right combination of conditional code that will only change the background when it needs to be changed, instead of continuously setting it (the setting of it is what boggs down the workbook). i.e. the procedure checks the read-only status, if it is already in read-only and the read-only background is already set, don't set it again. If it is not already set, then set it. I can't come up with the right statemens to do this. Thanks for all your help. > Interesting. As you may suspect, I had searched both XL and VBA help > for 'readonly' and 'toggle readonly' before posting. [quoted text clipped - 131 lines] > > > > > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > > > > > and still actually work?? Thank you. Turns out the answer is to put the code in the worksheet object event "worksheet_calculate". Then the necessary procedures, that exist outside of this procedure, can be called...unlike a Function procedure. > Interesting. As you may suspect, I had searched both XL and VBA help > for 'readonly' and 'toggle readonly' before posting. [quoted text clipped - 131 lines] > > > > > > > > Any suggestions as to how I can get this procedure to NOT run continuously > > > > > > > > and still actually work?? Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.