I have an extensive application which I wrote using Excel 2002 (XP - Version 10). However it will likely be used on anything ranging from 2003 - 1997. When I tested it using Excel 97 it gave me the traditional [annoying] message asking if I was sure I wanted to save it under an earlier version than which it was created.
I want to get rid of this message (a lot of my users aren't the most computer literate and the more annoying messages I can avoid the better!)
I tried using:
Application.DisplayAlerts = False
but it doesn't affect this particular message. I need to know how to keep this message from popping up. Please Help.
Under 97 (NOT on my 2002) it also gives a "Do you want to save the changes" message upon closing even though the "DisplayAlerts" is set to false. I'm not sure why this is still coming up.
I have an accounting spreadsheet that is used by 25-30 people. This spreadsheet has a button the user can use to save this spreadsheet allowing the spreadsheet to save itself to a specific folder path base on the dates entered in the spreadsheet.
I have begun tracking certain items in the accounting spreadsheet and have added a section to the save macro that gathers data from the accounting spreadsheet and saves the data to a ‘Spreadsheet Stats’ spreadsheet. Since it was possible that more than one person might try saving the spreadsheet at the same time and try to open the ‘Spreadsheet Stats’ spreadsheet at the same time, I placed a loop in the macro that determines if the 'logging data' spreadsheet when opened is marked as "read-only". If the ‘Spreadsheet Stats’ spreadsheet is opened as "read-only", the macro closes the ‘Spreadsheet Stats’ spreadsheet then reopens the ‘Spreadsheet Stats’ spreadsheet again and again until opened without the "read-only" status. Code seen below.
Do Until Now() = start_time + 0.002082 Or Now() > start_time + 0.002082 Workbooks.Open Filename:= _ "G:EDCUSTSERV1_ACCTGIPL SpreadsheetsBilling SpreadsheetsData DropSpreadsheet Stats.xls" Set WB = ActiveWorkbook If WB.ReadOnly = False Then Exit Do End If WB.Close Loop
The macro works great, however, should a user try to save the spreadsheet and the loop happens to open and reopen the spreadsheet until the "read-only" status is gone, when the macro completes, a window pops up and alerts the user that the ‘Spreadsheet Stats’ spreadsheet is now available for editing.
My question: Is there any way to prevent that pop up window? I don’t want the user to think they need to edit the ‘Spreadsheet Stats’. Remember that the macro has completed when the popup window shows up, so Application.display alerts set to false
When I open an excel file it asks if Id like to update the links to other data sources. I would like to say no everytime, does anyone know the VB to avoid the popup?
I have a spreadsheet with a number of fields on it which must be filled in before the file can be saved, and if the user attempts to close the spreadsheet without filling in the required fields, I want a message box to appear asking for confirmation that the user wishes to quit, if yes, the sheet exits, if no, the sheet remains open
What is this error message that is popping up when I try to insert a blank row of cells?
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL + End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."
This excel document we have has LARGE amounts of data
am trying to create a workbook template which collects data from users with user forms and some macros. Users assign a project name when workbook opens and then they are able to save as the workbook with name of project name. But i don't want them to save on my template mistakenly. How do i prevent this? I tried workbook_before close sub but i can manage that it recommends to save workbook with project name and if you don't want, it still asks you if you want to save workbook before close
I want to make sure that the user of a file only saves from a button on a particular sheet. Can I ensure that if the user goes File Save at any time that they cannot use it?
I have a spreadsheet that containt a conditional formula to place the current date aka Today() into a cell based on if an entry in the previous cell.
I put in this formula: =IF(I131>="1",TODAY()," ")
My boss told me to update to this one: (same result) =IF(I130>="1",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))," ")
BOTH will change the saved sheet data if we open it tomorrow (due to using Today()). We want to retain the date that it was entered on (which will always be the date he puts entry into sheet).
Is there a way to save the sheet and update the code for Today() to the date value so it will appear as a date when it's opened in the future?
I have a list dropdown from cells A2 to A10. If only when a user selects "orange", he has to key in some description in column B.
Eg if he selects orange in A2, he has to key in some description in B2.
Is it possible to set some formula or macro which prevents user to save this file if he leave blank in B2 or B10, depending on which row he has select "orange"?
In my program I have prevented the user from saving the Workbook with the following code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim p As String Cancel = True Do While Cancel = True p = InputBox("Enter password to save file:", "Password Required To Save", "") If p = "Enter Password Here" Then Cancel = False Else yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password") If yn = vbNo Then Exit Sub End If End If Loop End Sub
I also need to prevent the user, when they use the save as, from saving certain worksheets. Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three. Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.
I've got a workbook that I want to keep intact in a central location on our local network, to make it easier for everybody to access, but don't want people to be able to save changes to the master workbook itself. I'd like to be able to disable the 'Save' feature while still allowing 'SaveAs'. The following code (installed in ThisWorkbook) results in the OPPOSITE of what I'm trying to accomplish (i.e., it allows 'Save' but disables 'SaveAs'):
Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
If SaveUI Then MsgBox "The 'Save' function for this workbook has " & Chr(10) & "been disabled. Please use 'Save As'.", vbOKOnly + vbInformation, "Save Disabled" Cancel = True End If
End Sub
Obviously I'd also like to check the filename they're performing the 'SaveAs' under and disallow it if it matches the master filename.
I've thought about making the workbook into a template, which would sort of accomplish the same thing, but it would be much easier to just keep it as a workbook.
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
I have a spreadsheet saved. Its a report with formulas in. I have saved this as a master copy and when opening you can only open in read only or enter a password to open to edit. The report is produced monthly, so each moth the master is opened and the user has to save the ss with a new file name.
I want to put VBA code so that when a they open the ss in read only. a popup box appears asking to them to "Save the Report Now" with a save as button. This would then open the Save As window
Application starts with a button on an otherwise empty spreadsheet. Then opens a user form. After I close using a button on the form. My close routine closes the worksheets and workbooks. I then quit the application and I get a message asking if I want save the original spreadsheet.
I have the following code that works fine (although i'm sure you'll think it's a little clumsy), but when I add a save workbook line at the end I get a message that says that an action (presumably the save) will interupt a refresh. My problem is that I cant understand why there is a refresh when the code ends. The data that is being refreshing is big and a number of calculations take place, so that may be something to do with it.
Sub RUN() Application.DisplayAlerts = False Application.DisplayStatusBar = True[code].....
I am using this code to display a message when a range is empty on workbook save. Case "A" gives an error when referring to a range, the second case works fine. How can I change it?
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Select Case Worksheets("Sheet1").Range("B6").ValueCase "A" If Worksheets("Sheet2").Range("D104:D109").Value = "" Then MsgBox "Cells cannot be empty!" Exit SubCase "B", "D", "F" If Worksheets("Sheet2").Range("D112").Value = "" Then MsgBox "Cells cannot be empty!"Case ""End SelectEnd Sub
I have several workbooks that use macros from my personal macro library (MyExcelAddIns.xlam).
Some of these, especially new ones, get an error message every time I save them: "Privacy warning: This document contains macros, ActiveX controls, ... These may contain information that cannot be removed by the Document Inspector."
Then after I save it once or twice, the message goes away. Why am I getting this message? Why only sometimes? What can I do to get rid of it entirely?
I have a spreadsheet that is emailed to someone to fill in. They then fill in some information and send it on to someone else.
Sometimes they don't fill in all the information so is it possible to have excel check that cells are filled in and to prevent someone from saving unless this is so? Or maybe a message stating that the cells need to be completed if Excel is being closed or saved?
I have Excel 2003 and 2007 on my computer and I want open excel from vb6 but they are both called Excel.exe. So it opens both of them when I run my code. I renamed one of the exe's and I was going to do it that way but then it was going to do a whole bunch of reinstalling.
Can I specify which version of excel I want to run, in vba or vb6 code ?
If I want to create an Excel file that other users would use, and want to create a specific version number of the file. Is there a place I can save that information outside of the worksheets?
For example, lets say I create a file that is version 1.0. Can I save that somewhere in "Properties" or somewhere else so that "1.0" can't be modified, and if I come out with a different version say 2.0, I can update it?
As per title, I have both Excel 2003 & 2007 installed - I need to set 2003 as the default for opening xls files when double-clicking but can't seem to override 2007 which wants to hog all the action. I've been through the File Types menu and selected the 2003 version of Excel as the default, but it doesn't make the slightest difference.
how to get version number of the .dll file (in the path C:program files....) using VBA code. The properties of that .dll file has a tab named "Version". so i need the version number displayed on that tab.
In a previous thread I asked for a solution to the following problem
Cells A1 = 1 A2 = 2 A3 = 3 A4 = 4 etc.
What I want to do is when I click on cell A1 the value is put in cell C1. When I click on cell A2, C1 changes to to A2 value and so on.
VERSION 2 The VBA I received is good for 1 range. Is there a way to use multiple ranges? In my example above column A is the 1st range and cell C1 changes accordingly. Now i would like to use column B as a 2nd range and have cell D1 change accordingly.
I have an excel based appliction that I distribute to about 150 people in the US, London, Hong Kong and Singapore. Distribution is currently done by email containing a link to a Sharepoint site (the email has the link, release notes, etc). The end users open the file and save it to their desktops, overwritting the previous version. I'm finding that many users are not updating their versions as they should be.
So what I'm wondering is if there's a way to have this process automated - so that when the user opens the file, it checks a repository (sharepoint or otherwise) for a more current version, then prompts the user to download it.
how I could amend the following code so that my target worksheet includes file versions? The worksheet range "Folder" contains a string specifying the target folder and the worksheet range "IncludeSubFolders" is either TRUE or FALSE. I can trap FilePath, FileName, FileSize and DateModified fine but not File Version (I'm working with DLL libraries).
Code:
Dim iRow As Long Sub ListFiles() iRow = Range("DatabaseStart").Offset(1, 0).Row Call ListMyFiles(Range("Folder"), Range("IncludeSubFolders")) End Sub
I need to create a multipage with tabs running down the left hand side. I cannot use the normal horizontal multipage control because this would result in an impossibly wide page. Is there a vertical multipage control? I could not find one? Or a way to convert the multipage control to show vertical tabs?
Please bare in mind that this vertical multipage will be shown in a form that already includes a normal horizontal multipage (I am using multipages within multipages too!).
