I have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code: Sub SaveSheetAsNewBook() Dim wb As Workbook Dim InitFileName As String Dim fileSaveName As String Dim wshape As Shape InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1
The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3
Would it be possible to modify this code so it saves the entire workbook?
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False Dim bk1 As Workbook Dim bk As Workbook Dim myfilename As String myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls" Set bk = ActiveWorkbook Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet) bk1.Worksheets(1).Name = "Sheet1" bk.Worksheets("Sheet1").Copy _ bk1.Worksheets("Sheet1") On Error GoTo 0 bk1.SaveAs Filename:=myfilename, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close
is it possible to use code to save a workbook? I know this is pretty basic, but for the life of me, I cannot answer this question searching the entire website and forums.
I'm currently creating what could be called a "yearly timesheet" for a small company and trying to automate it as much as possible. I've created several buttons along with input boxes to create the data, now I would like to make two more, one to save the worksheet (preferrably using data from one cell as the workbook name) and a second to send the workbook to the local, default printer. I've come across the topics on stopping Excel from automatically asking the user to save it, etc etc but none along the lines of "this is how you create a button that if clicked, saves the workbook."
I want is a Macro that will save the file as .csv, with the same filename and location as the original (just in csv format). A hotkey would be quite handy for this. I tried using the macro recorder and fiddled around with the code a bit but my programming knowledge is quite limited and I ended up with something that doesn't quite work as i'd like. I'm sure this would only take a few seconds to code for an experienced user, which is why I post here. I would post my attempt at doing it myself but i'm on a computer that doesn't have excel at the moment.
I need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.
What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.
This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.
I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.
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.
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 written a sub to open a txt file, but I want to then save it as Excel, with a filename (a variable). The variable is public, and is set by other subs. I have added the standard "save as..." button to my custom toolbar to use for this and the following code (taken from previous thread)
Public Sub SaveFile(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = True Then Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show MyVariable, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Cancel = True Application.EnableEvents = True
End If End Sub
But it comes up with the original filename in the save as dialogue and txt file type. Any help would be great, this is used by inexperienced users and I don't want the original file saved as it's prevoius name and type
I have a user form designed and now I would like to be able to program a Command Button to "SAVE" and when it is clicked, I would like to save the results of my fields to another worksheet. I have a defined range that I would like to have the inserted row into?
i try to write a code to pop up input box with browse button to select the file path the code without browse button is workin corectly
Private Sub CommandButton3_Click() Dim N As Integer Dim Fname As String Fname = InputBox("Please enter the file name and path to save the output", "enter file name") Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy ActiveWorkbook.SaveAs Filename:=Fname ActiveWorkbook.Close End Sub
Need to add browse button to make it easier than writting the file path
i have this code to run autofilter in protected sheet2, assigned to a check Box, but it gives me this Msg when i run the code by checking the check Box: File in use. The file is locked for editing by the (my name). However, when i excute the code by F5 in the VBE window, it works fine also when i excute the code from Worksheet_Activate , it works fine
Sub t() With Sheet2 . Protect "0", , , , userinterfaceonly:=True, Allowautofilter:=True .AutoFilterMode = False .Range("A5:DT5").AutoFilter End With End Sub
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)" The macro records this string as mydate:
Sub Start() Dim AnyString Dim MyStr Dim DirString Dim mynum 'As String Dim resp As Long Dim get_mynum Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored. get_mynum: mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel) If mynum = False Then msgbox ("You do not want to continue? Ok, programm stopped") Exit Sub ElseIf mynum = "" Then..............
I want to combine the following two procedures, but I can’t figure out how . The purpose of the procedures is to warn the user to save data before importing and overwriting data that was already there.
Sub Is_sheet_empty() If Range("your range") = vbNullString Then Call Add_New_Data Else: Call Sheet_Not_Empty End If End Sub
Sub Sheet_Not_Empty () Dim Response As VbMsgBoxResult Response = MsgBox("Do you want to replace this data?", vbYesNo + vbQuestion) If Response = vbYes Then Call Add_New_Data Else: Response = vbNo ‘Do Nothing End If End Sub
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I've recorded this code and am looking to include it in a button. I would like for when a user clicks the button assinged macro that the active sheet is cloned and saved as a CSV file. The user should be prompted before saving on where (file location) they'd like to save the file.
Sub CloneWorksheet() ' ' CloneWorksheet Macro ' ' Sheets("SDW&Customer Workshop scheduled").Select Sheets("SDW&Customer Workshop scheduled").Copy ActiveWorkbook.SaveAs Filename:= _ "C:Documents and Settings1167916My DocumentsTestBook1.csv", FileFormat:= _ xlCSV, CreateBackup:=False End Sub
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file() Dim tDate As String Dim FileSaveName As String Dim fPath As String tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy") 'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?