Workbook Protection In Macros

Oct 27, 2006

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...................

View 5 Replies


ADVERTISEMENT

Workbook Protection Code: When Macros Are Not Enabled

Oct 23, 2006

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"?

Sub workbook_open()
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Application.AskToUpdateLinks = False
Windows("rechenfile.xls").Activate
Sheets("Daten").Visible = xlVeryHidden
Sheets("Koeffizient").Visible = xlVeryHidden
Sheets("Investitionen").Visible = xlVeryHidden
Sheets("Konsum").Visible = xlVeryHidden
Sheets("Zinssatz_permanent").Visible = xlVeryHidden
Sheets("Preise").Visible = xlVeryHidden
Sheets("Nettotransfers").Visible = xlVeryHidden
Sheets("Beschäftigung").Visible = xlVeryHidden
Sheets("Löhne").Visible = xlVeryHidden
Sheets("Nettoexporte").Visible = xlVeryHidden
End Sub

View 4 Replies View Related

Macros And Protection ..

May 22, 2006

I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
the first six of which are hidden to start with and I have put macros in the
adjoining column so that when they are clicked, the full 7 rows open up and
the table of scores can be entered, When entry is complete for that
contestant, a further macro when clicked will close up the 6 rows, leaving
just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
in adjoining columns.

The sheet works fine, but as many people will use this programme, I need to
protect the sheets against mistaken entries etc., and as soon as I protect
it, the macros wont work and throw up a “unable to set the property of a
hidden range class, run time error 1004. I don’t want to leave the sheet
unprotected, can anyone advise me where I am going wrong.

I am also trying to find a way to validate “time taken” entries so that they
can only be input as minutes and seconds in the format of 09.56, within a
range of 00.01 – 10.00. Not having any success with this as it keeps
converting the data into something like a date.

View 9 Replies View Related

Macros And Protection

Jul 3, 2008

I have a excel Workbook, 6 sheets and many calculations with formulas and macros.

Is there a way to protect this workbook to be able to insert data only in the correct cells, I tried but the macros does not work, they are essentially copy and paste.

there will be 3 sheets with reports to be printed too.

View 9 Replies View Related

Macros Enabled Spreadsheet With Protection

Feb 4, 2014

I would like to be able to password protect each sheet in a workbook and still be able to use the macros/form control (button). Below is the code that I'm using and the code that I've attempted to add to it to protect it.

Current Code:

[Code] ....

Code added for protection but not working:

[Code] ....

I've added this code to both the beginning and the end of my code; however, I'm getting a "400" error when I try to use the form control (button) when the sheet is protected. Is there something that I'm doing incorrectly?

View 6 Replies View Related

Adding Worksheet Protection But Allowing Form Controls - Macros

Jun 5, 2014

I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.

I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.

View 2 Replies View Related

Locking Workbook :: Tools >protection > Protect Workbook

Jan 21, 2008

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?

View 9 Replies View Related

Workbook Password Protection

Jun 5, 2014

I am trying to protect my file with a password which does not contain capital letters and contains less than 12 characters.

How can I do that?

View 4 Replies View Related

Setting Protection On Workbook To Be Read Only

Dec 7, 2012

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.

View 8 Replies View Related

Remove Password Protection On Workbook?

Sep 29, 2011

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.

View 1 Replies View Related

Prevent Workbook Protection In VB Code

Dec 1, 2011

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?

View 7 Replies View Related

Save Duplicate Of Workbook Without Protection

Nov 21, 2006

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 )

View 2 Replies View Related

How To Delete Another WorkBook Macros Using Macros

Jul 23, 2005

What is the commands or script for deleting a macro automatically using
another workbook macro.

View 9 Replies View Related

Pass Protection From A Master To A Slave Workbook

May 7, 2009

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.

View 2 Replies View Related

Remove Sheet Protection From Every Workbook In Folder

Dec 28, 2009

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.

View 9 Replies View Related

Hide Toolbars When Workbook Protection Is Enabled

Jun 26, 2006

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)

View 3 Replies View Related

Password Protected Macro Workbook - Remove Protection

Jun 6, 2014

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.

How to open it?

locked one.xlsm

View 2 Replies View Related

Apply Password Protection For Different Worksheets In Single Excel Workbook

Apr 2, 2012

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.

View 9 Replies View Related

Workbook Protection - Unable To Set Visible Property Of Worksheet Class

Dec 26, 2013

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'

The code I am trying is:

Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"

End Sub

How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?

View 4 Replies View Related

Copy Macros From Personal Macro Workbook To New Workbook?

Mar 28, 2014

I need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.

View 5 Replies View Related

Make Workbook Code/Macros Unique To Workbook

Sep 12, 2006

How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.

View 5 Replies View Related

Copy Macros From Workbook To Workbook

Mar 26, 2009

I have several workbooks that contain the same worksheets. The worksheets within the several workbooks are structurally the same but have a different number of elements within the structure of the worksheets. I've written Macros that accommodate the difference in the number of elements in the worksheets and will perform the same appropriate tasks within all workbooks.

I use one of the workbooks for development and as I make changes in the Macros within this development workbook, I wish to update all the macros in the other workbooks.

I've been Exporting and Importing to accomplish the update. Is there a utility available that will accomplish this task? Something like "Copy all Modules from "Workbook1.xls"

View 10 Replies View Related

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 Replies View Related

Run Other Workbook Macros

Mar 14, 2007

I need to create a macros to open up 4 other workbooks and run each individual workbooks macro. The work books can be called book 1 to 4 and the macros is call run in each sheet.

When I have tried it manages to open the sheet but not run the macro.

View 8 Replies View Related

Protecting A Workbook But Still Able To Run Macros

Mar 7, 2008

is it possible to protect a workbook but still run various macros, i have tried this and the macros wont run

View 10 Replies View Related

Delete All Macros In Workbook?

Jan 4, 2011

I want to delete all macros from my workbook (so it will work well in Mac 2008). I went to Alt-F11 and deleted all Macros there and check every sheet and found no references. I did all that and when I try to open it in Excel for Mac it says there are still Macros.

How can I ensure my workbook has no macros?

View 9 Replies View Related

Copy Macros To Another Workbook With VBA

Nov 22, 2012

I want to copy some macro's from one workbook to another one. But I don't want to copy all macro's to the new workbook. For example: I have 20 macro's, and only want to copy 2 of them every time.

View 7 Replies View Related

How To Run Different Macros At Once In All Sheets Of Workbook

May 31, 2013

I have made 7 macros for inserting a blank row above certain text, which are located in single column but in different row. 3 macros are for one sheet and 4 are for other. I am able to run them at once but the changes are getting done in only one sheet (which is active when I run macro)

Below is the code of macro:
Sub Space1()
Dim c As Range
For Each c In Range("D1:D60")

[Code]......

View 6 Replies View Related

Running Macros On Whole Workbook

Mar 23, 2007

I want to run a recorded macro on the whole workbook at once, not just on the active worksheet.

View 3 Replies View Related

Don't Open Or Use Workbook Unless Macros Are Turned On?

Dec 9, 2008

Is there a way to prevent a workbook from opening unless macros are allowed?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved