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 have a form that will be populated with different numbers of records. I need the records to be locked, but the remaining rows (if the user needs to add new records) to be unlocked. Can I use some sort of range.end function for this?
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?
I've got several workbooks that users will be using to keep track of individual projects. These workbooks will talk back to a master summary sheet that has the status of all projects on it. The possibility exists that someone else on the network will have the summary book open when the program tries to update it, which would cause an error when it opens as read only. So I want to do something like this.
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 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'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 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"?
For a couple of reasons I have had to take a workbook off of a local machine and place it on the network so others can access it. I cannot share the workbook because of the macro's and protection on it. At any one time only one person should have full control of workbook. The others only need read only access. I won't go into the whole deal but I was hoping there was a way on startup to detect if the workbook is in read only mode or not (Other than the basic notifications and indications that excel gives as many users either ignore or do not understand them). If so I would like a big message to popup stating this fact to the user and also if possible show them who currently has it open. Lastly, they would have a cancel workbook open option until they get the other user to exit.
I have an excel workbook with multiple worksheets(tabbed across the bottom of the workbook). How can I set up a menu of the worksheets on the left hand side of the workbook that enables me to jump to that worksheet?
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 am running a call centre with 20 users at peak time. I have 1 user who will always have a master workbook open and saving throughout the day. The remainder of the users during calls will need to open this file as read-only to simply view the latest saved version.
This works however sometimes is takes a over a minute for the file to download from the server for the read only box to appear. I was wondering if there was a way for the other users to view it in a simpler and quicker way?
Also issues with the 1 user saving the file which others have it open as read only?
I have created an Add-in for use across the business, the add in calculates financial impact based on figures that are stored on the shared drive.
The addin currently opens the workbook, and sets the variables to figures contained in the workbook - and then closes the workbook.
To roll this out to the business - I need to get the add-in to open the workbook as read Only, so that it will allow multiple users to access at once. I have set to open as read only in the past - however this prompts me to save the workbook as something else.
Is there any way I can get this to just open as read only, do what it needs to - and then close; without having the option to save as something else?
I was wondering if there is a way to set a macro to run on an event only if the workbook is read-only. The macro I have set up right now reformats the toolbars to a custom user friendly one, but I want to be able to open the document with a password and not have the toolbars rearange on me.
I have a file that's only instruction is to open another workbook in Read Only; this is due to not having network privileges to make the entire folder Read Only.
I'm getting one user on the network where the Workbook.Open script doesn't fire up automatically and I can't see why. Then ocassionally i'm getting users where the file hasn't closed itself after the file has been opened.
Is there any obvious reason for both of these happening?
I have a shared Excel Workbook, and I am trying to prevent a non-permitted user from making any changes to it. I have managed to hide all of the worksheets bar one - The Warning Page - if the user is not one of my chosen users, but I am having a little trouble setting the worksheet to open as "read-only" if the user is not one that I have listed.
My macro code looks a bit like this:
If Application.UserName = "Vikki" Then Sheets("Warning!").Visible = False Sheets("Total").Visible = True Else Sheets("Warning!").Visible = True Sheets("Total").Visible = False ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly End If
But when I am using this, a non-user will get a msg box asking if they want to save the workbook before changing to read only. Can I prevent this msg box from appearing?
I have a workbook which has a macro to loop round refreshing its data every half an hour and each time the refresh is performed the file gets saved. This file is opened by one user in the morning, and left open with the macro running to carry out the refreshing throughout the day. This workbook is then also opened by a number of users as read only who interrogate (but do not update) the data in the spreadsheet as part of their work. Periodically, they close and re-open their copy of the workbook to ensure they have the most up-to- date version open.
To let them know when a new version is available (i.e. when the refresh has been performed and the file re-saved), I have some code which compares the last saved date/time of the file they are using with the date and time of the file they loaded the file from, and displays a message when they values are different. I have, however, been unable to develop a macro which properly 're-loads' the workbook. I have been able to get the file closed and the latest version opened (through a 'ThisWorkbook.Open' statement). However, when the new version susbsequently opens, none of the open events are triggered, nor is the Auto_Open macro executed. This is a major problem, as the spreadsheet relies on this processing being performed for the file to properly work. (I know that macros are enabled (I get the 'enable macros' prompt in the course of the updated version being opened, and can run macros once the file has been opened).)
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 completely forgotten what I need to enable to get this to work
I have one spreadsheet indexed to another. When one user opens their s/s and changes a value and then closes it I need the other s/s to update once they open and refresh.
I have created a Hlookup to read a workbook that has several tabs. How do I write the formula to read every tab. So if my tabs are labelled like this... FY 2012, FY 2012-2, FY 2012-3 - each spreadsheet is formatted the same, same number of rows and columns but I need the formula to look on every sheet and then bring back the information.
I am trying to create a "macro" that will read values from one workbook into an array based on a key field. If the key isn't present the cells data is not to be stored. After the records are stored i want to test them agains a range in a differnt workbook. If the data compared matches a stored value, leave it alone. If there is no match, a cell needs to be added with a value stating a differnce.
Like i stated i am new at this and have no clue how to compare an data array to a field. Let alone read the values into the array.
