The following code is not working for me in the "ThisWorkbook" section:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
Anytime a change is made it still asks if I want to save changes.
Also,
What code could I assign to a CommandButton that only allows a "Save As" function?
Something seems to have happened to my Excel. When I make changes and close the workbook or application, the pop-up box asking "Do you want to save the changes you made to [Book1]?" no longer shows up.
So far I've tried running a macro with Application.DisplayAlerts = True, but that only seems to work while the macro is running.
I close an existing excel file I need to automatically save it to another folder as the same name. I don't want any prompts or save as boxes to appear when this happens.
I need to do this as I use the file all the time, but need to save it on to a network where others can view it.
I was able to find the coding that I needed for being able to have a backup of my file. So when I save a workbook and close I also get a backup copy in another folder that is dated. But I am having problems getting the code to work. Probably because I do not understand it correctly. Here it is.
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.SaveCopyAs "C:Documents and SettingsTeacherDesktopTEST" & _
Format( Date, "yyyy-mm-dd") & " - " & ThisWorkbook. Name
End Sub
Where it has, ThisWorkbook.Name, do I put the name of the workbook? Not sure.
I am trying to fix the width of a column everytime I open it but I can't. After I have fixed the column width and saved it before I close the file, when i reopen it, the column width will expand. The data source of that column is a real time query. I tried to lock the cell but when i locked it, I can't refresh the data from the query as it is locked. So, is there any way for me to achieve that?
View 4 Replies View RelatedI want to close a tab delimited Txt file that is open in Excel, thru' a macro, without the save changes dialog box appearing.
View 2 Replies View RelatedI want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.
View 7 Replies View RelatedI am in the process of setting up an Excel invoice. So far I have managed to implement the following:
On open: Incrementing invoice number
Cells cleared ready for next use
Private Sub Workbook_Open()
Sheets("Sales Invoice").Range("H2").Value = Sheets("Sales Invoice").Range("H2").Value + 1 .....................
There is only one task left I need to automate. On closing the document I would like a copy to be saved in a specific folder and be given the file name of a cell (B9). Obviously I would not want the 'saved copy' to retain the Incrementing invoice number or the clear cells function. Even if the invoice copy was exported as a flat jpeg that would be fine. I am using Office Exce 2003 (Sp2) - I have attached my invoice should anyone be kind enough to take a look.
I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies View RelatedThough VBA is very similar to VB6 (which is what I took a course in a few years ago) there are some things I can't figure out. One of them is ending and closing a procedure (i.e. if an "if then" statement is not met). In VB6, if I remember correctly, you would use "Me.Close". What is a similar method in VBA for taking this action?
View 2 Replies View RelatedIs there any way to have a macro, that is started from the immediate window, close the immediate window when it's done. My macro closes the immediate window, but as soon as it ends, the immediate window reopens and my cursor is inside it.
View 14 Replies View RelatedOk so my code below works fine except for when the user selects yes to the prompt and cancel in the save box. When that happens excel attempts to close and crashes. I want it so if the user says yes initially and then hits cancel the sub will cancel and the workbook will stay open.
View 2 Replies View RelatedI have a macro that once it's run, it quits the application. The problem is that once it activates, it closes all the excel worksheets. What I want to happen is that if it is the only excel workbook open I want the application to quit, but if there is another workbook open I want it to just close itself only and leave the other ones alone.
I have a perfectly functioning piece of code. There is still one snag, however. In the process of running the macro, three new workbooks are opened. I would like to close all three of those workbooks at the end. I am using the following procedure for each of the sheets:
To open the worksheet
Code:
Dim wb As WorkbookMsgBox "Please select the Stakeholder File"SHFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Please select the Stakeholder Workbook")If SHFile "False" Then Set wb = Workbooks.Open(Filename:=SHFile)
To close the worksheet at the end:
Code:
If Not wb is Nothing Then wb.Close SaveChanges:=False
The only problem is that I am getting bugs when closing multiple workbooks (3). I have assigned the names wb, vb and ab to the three workbooks.
I recently added some code to close a file after a few minutes of inactivity. (Here's the sub code)
Sub time_out()
If Timer - LastEventTime
I have a macro in a workbook (WB1) that opens another workbook (WB2). After performing several other macros through user invocation with buttons, I close WB2 with the following statement:
Windows("WB2.xls").Activate
ActiveWorkbook.Close (False)
While the WB2 workbook closes okay, the WB2 project is still open in the VB editor. Does anybody know why this code isn't closing the project in the VBE?
The WB1 process can occur multiple times while Excel is open and each time a separate occurrence of the WB2 project is left open in VBE.
I have some VBA code which opens another workbook, copies data from the first sheet in there and pastes into the current sheet before closing the workbook it got the data from. Is there any way of suppressing any messages associated with closing the other workbook? i.e. do you want to save - or possibly automatically generating a no message to the prompts?
View 3 Replies View RelatedI have an error that just began occuring as I am developing a VBA application in Excel.
Whenever I close the workbook, an Error 91 occurs. The VBA editor opens and a line is highlighted within my combo box change event. It is stating that the combo box item no longer exists.
The combo box actually resides as an embedded control with a sheet.
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.
View 4 Replies View RelatedWhat 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 .
The macro itself is saved in personal.xlsx.
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.
View 2 Replies View RelatedI'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
A1 cell includes; Dividend Payment : TWITTER (National Market) %39,06 (Closing Price: 5,28 Dolar), Cash Dividend I want 5,28 result in B1 cell ?
A2 cell includes; Dividend Payment : FACEBOOK %5,00 (Closing Price: 53,18 Dolar), Stock Dividend I want 53,18 result in B2 cell ?
In order to understand my question open enclosed file.
Note1: "Closing Price:" text in the cells are stable.
Note2: "Dolar" text in the cells are stable.
I have 4 employees who at the end of their shifts have to email me an excel workbook. The issue is that some of them are forgetful and after placing numerous post-it notes on their monitors one particular employees just forgets.
What i want to do if possible is that when the workbook gets closed, a pop-up window shows up on their screen reminding them to send the workbook. Even better would be if their was a button that they could click that would email the workbook as an attachment in the pop-up box.
How can I get my workbook to automatically re-protect a single worksheet in a workbook at the save-then-close point? It doesn't need to be password protected.
View 5 Replies View RelatedI have a spreadsheet with one column for each day. I've grouped the days into months and now I would like to minimize (minimize each month) Is it possible to close all, without having to select each group?
View 1 Replies View RelatedUsing the following line of code, I am able to open a vnc session:
[eq1] = Shell("C:Program FilesRealVNCVNC Viewervncviewer.exe " & " -config " & """C:Program FilesRealVNCVNC Viewerzva.vnc""", 1)
How to close this connection?. If I send alt F4 using sendkeys method, it is going to the computer to which vncviewer is connected, not to the computer running vncviewer?
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.