I've written some code which goes through a workbook, deletes any non-required information, and then saves it with a different name in another location. (It then re-opens the original workbook and looks for the next match and so on).
However, the workbook has read and write passwords on it, which I've put into the code to open it in the first place, but I want to save the 'new' sheets without the passwords being applied. Is there a simple piece of code that I can put in that will make the passwords null? (I'm a bit shiny and new to all this, so sorry if this has a glaringly simple solution! : D )
I have a workbook with 8 sheets. I have hidden two of the sheets and want to lock the rest from people editing, and stop them from unhiding the hidden sheets
I go to tools>protection > protect workbook
After typing in the password this doesnt protect the workbook as i am still able to edit the cells.
how to lock the all sheets and stop them from being edited?
In addition to the code below that hides data if macros not enabled, prevents copy and cut (I know its still possible) I would like to as well as protect the sheets (which works fine) also protect the workbook to prevent moves etc. Unfortunately while the protection may be easily applied, the veryhidden bit to ensure macros are selected then conks out. Does anyone out there know of a way where say the workbook protection may remain and at the same time allow the veryhidden etc bits do their stuff as normal as if the workbook protection was off?
Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = True Or bIsClosing = False Then Run "HideAll" If SaveAsUI = True Then Cancel = True
End Sub Private Sub Workbook_Activate() Dim oCtrl As Office.CommandBarControl 'Disable all Cut menus For Each oCtrl In Application. CommandBars.FindControls(ID:=21) oCtrl.Enabled = False 'oCtrl.Enabled = True Next oCtrl...................
I have a Workbook named "Master Archive" that contain data from 9 workbooks. This is all done with a macro upon opening each workbook and using a command button to send the data to it. Question is I want everyone to be able to view the Master Archive Workbook in a readonly fashion and the orginal to always remain closed. Reason being is I cant run the macro to archive the data if someone has it open, which then would cause debug issues with the code.
Are there settings were I can be the only one to open the workbook and be able to Edit.
Also Everyone has a shortcut to this Master Archive on there Desktop.
I am trying to change a password on a workbook. I have already gone to encrypt document and changed the password. However, when I open the workbook again, my new password works but another popup shows and asks me for another password for write access. That second password was my original password, and I don't know how to change that.
I have a workbook that contains macros. When the workbook is protected, the macro returns an error so I have unprotected the workbook but, if someone protects the workbook and saves it, it returns an error when closing and even if they choose cancel, it still screws up the macro and prevents it from working upon reopen.
Is there a way to prevent someone from protecting the workbook in vb code?
I have a master workbook with which I can produce slaves from but I am looking at keeping certain cells protected to stop deletion (dates etc). What I require is for the top 5 rows to be protected on each of the 4 week sheets where the dates etc are. I had played with protecting all the sheets and with unprotect then protect on the master but it did not carry over to the newly created slave, could someone point me in the right direction . SOLVED: The code is not pretty but functional.
I have tried to cobble together a macro to remove the sheet protection from every sheet in every workbook in a folder. Not surprisingly, it is not working. Unfortunately, I have reached the limits of my VBA abilities.
I would like to hide the standard and formatting toolbars in excel when i enable workbook protection, however I have no idea how to get excel to realise when workbook protection has been enabled, and how to hide the toolbars in VBA also. Also i would like to know how to do the reverse (how to show tooldbars when workbook protection has been disabled)
I prtected several sheets with the xlVeryHidden command I got from you. But when the user does not allow Macros to be executed, also these protections are not executed. Does there exist any solution to this problem? Something like "when macros are not allowed do NOT open file"?
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.
In my office, our colleague locked his macros before he left. Now, I would like to continue in that but I have no idea, how to get into macro - to study it and change it as needed now.
I used VBA codes to apply different passwords for different worksheets in a single excel workbook. They worked fine. Then i applied a password to the VBA code itself through the VBA project properties. Also worked.
But my problem is that, if any one enters a wrong password to open any of the worksheets, the VBA will open an error message window and when "end" option is clicked, the VBA code will open without any prompt for the password.Thus the entire purpose is defeated.
I am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
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 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 have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 16-02-2009 by ceng '
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 currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
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?
Need VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.
Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.
I snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.
What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.
Private Sub CommandButton4_Click() Dim sAnswer As String Dim i As Integer
When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
