I use the following code to insert and delete a background picture upon opening and closing my workbook, but everytime it closes it asks me if I would like to save it even if I've already saved it just before closing. It does this because after the background is removed it senses a change. I would like to get this to stop, is it possible??
Private Sub Workbook_Open()
Sheets("Instructions").Select
Range("A1").Select
Sheets("instructions").SetBackgroundPicture Filename:= _
"H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif"
userform1.Show
Sheets("current inputs").SetBackgroundPicture Filename:= _
"H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("instructions").SetBackgroundPicture Filename:=""
Sheets("current inputs").SetBackgroundPicture Filename:=""
End Sub
I have a an Excel workbook (wb1) with 4 worksheets. I also have a personal.xls that contains 4 worksheets. wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc.. When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it. I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work. I know that the code will need to be copied to any existing workbooks. I have the code to do that. That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( ) Me.Saved = True End Sub
Private Sub Workbook_Open() ThisWorkbook.Saved = True End Sub
I have a spreadsheet that has hyperlinks in it and is mostly protected to prevent changes. Is there a way to prevent the "save changes" dialog box from appearing when this file is closed as I have to make this file available to others and don't want them to get this message or make save any changes.
I have to send some data on hourly basis i have a macro that extracts the data from the master sheet and creates a new workbook for the same and saves the same. Problem that i am facing is that i need to save the file with the same name. and on doing that it shows a pop up that do you want to replace existing file. Is it possible to stop this pop up. Code that i am using to save the workbook:
ActiveWorkbook.SaveAs Filename:= _ "C:Documents and SettingsAmritDesktop Training Scedule For Next Hour.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
I do have an excel file that I do not want to be moved to another location. I was wondering if it is possible to hardcode a location for the file and then check its current location in the drive and if both locations does not match delete its contents.
I tried something like below but I keep getting an obeject required error. I would really appreciate if someone can guide me to the right direction.
Private Sub Workbook_Activate()
Dim location location = "C:InetpubwwwrootLeadershipJC est.xls"
Dim currentloc currentloc = ActiveWorkbook.FullName
If location = currentloc Then MsgBox "Due to security settings of this file you can not save this file." Else Sheets("JobCosting"). Range(Selection, A2.SpecialCells(xlLastCell)).Delete End If End Sub
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.
When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.
Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.
The code I currently use for that purpose is as follows.
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls" Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload") Kill ThisWorkbook.Path & "" & "Rubbish.xls" End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
I am trying to disable the save and save as function using a macro, i know that this in its self is quite simple but i am making VBA create a new workbook and i want this (nameless) workbook to have the save feature disabled.
when i try to save data in other sheet. it is save but, if middle name is null in last record. then after i save other record the data is not save in same row.
i also want "sr.no" come automatically form data..
for.ex.
if in sheet2 last "sr.no" is "7" then it automatically display "sr.no" "8" in sheet1
I have a spreadsheet with 32 worksheet, I want to write a macro to "save the work, save as.. in a specific location and with a specific name and then reseting all data in specific cells.
I need my spreadsheet to require that the user save it by clicking a macro button, and not the Save or SaveAs options in the file menu. I have found out how to disable saving entirely:
Code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True If SaveAsUI Then SaveAsUI = False End Sub
But this of course prevents my macro button from saving too!
Is there any way to allow VBA code to save the spreadsheet while simultaneously preventing the user from saving through the menu?
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code: Private Sub cmdImportData_Click() Dim sFName As String 'On Error Resume Next PrepData CopyData FormatColumns 'prompt the user to save the file in "*.xls" format sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls") End Sub
I have a file that I have created that the users will use as a master template to create data that I need. Once they have finished, I want the file to define what the filename should be when they click save.
At the same time, I want to disable "Save As..." as some users will just use that and then will use a file name that another macro (that I will use) won't recognise.
i have a master workbook. it gets used daily as a template and saved as another file name. the file name is generated by 5 cells that each contain different data (date, street, town, state). is it possible when i hit the save or save as button/icon, for a file name to be automatically generated from the cells i have data in??
PostPosted: Wed Oct 15, 2003 5:12 pm Post subject: Re: change default saveas name for new file w/o saving Okay, so you want to create a workbook via code, prompt the user for a SaveAs name, then save this workbook?
Sub CreateAndSave() Dim wb As Workbook Dim SaveFileName As Variant
Set wb = Workbooks.Add
ChDrive "C" ChDir "C:VBA Code"
SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _ "Microsoft Excel Workbook (*.xls),*.xls")
On Error Resume Next If SaveFileName False Then wb.SaveAs FileName:=SaveFileName........................
I have a update/Save button on a spreadsheet. So when clicked it updates another sheet and saves the workbook. how can i have it run the macro when they don't click the button and do File -> Save.
I use to send reports to my seniors on daily basis. I want these reports to be with me and they can just open and check the reports, when I send them. If they try to save file through File-Save or File-Save As, they should not get these options at all in the File Menu.
My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday.
I have 120 sheets in my workbook and I only need 5 of them to save when there are changes, the other 115 are were data is pulled from. Is it possible to tell excel not to look at a sheet when it saves? I'm just after making it save faster. This is going to be a workbook that " moves" a round a lot, so I want to the 115 sheets in the same workbook.
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
I have an excel workbook that uses a .bmp file [a picture]. The master excel file and the .bmp file are in the same directory on the network. The excel file is "Read-Only", so the user would open it, customize it and save it on their own computer or somewhere else on the network.
They are only allowed to save it under "Save As". Once the user saves it, they would then be able to use their customized copy for future updates etc. The problem is that the .bmp file is missing from this new directory.
Is there a way to automatically save a copy of the .bmp file under the Directory specified by the user, so it's always available when the Excel workbook is opened? Or is there a way to attach the .bmp file to the excel file, so they are always saved together?
