VBA Macro Not Working When Workbook Is Protected

Apr 23, 2008

I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View 14 Replies


ADVERTISEMENT

Macro Not Working When Sheet Protected?

Sep 19, 2013

I have certain cells which I want the formulas hidden to the end user and protected so they can't update them.

I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.

Protect the sheet for the user interface only. This has to be done in VBA:

- Activate the sheet.

- Activate the Visual Basic Editor (Alt+F11).

- Activate the Immediate window (Ctrl+G).

- Type

ActiveSheet.Unprotect

- Press Enter.
- Type

ActiveSheet.Protect UserInterfaceOnly:=True

- Press Enter.

The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:

protect/unprotect all sheets in one go and have the macros still work?

When I protect pages individually I want the following to be allowed:

Select unlocked/locked cells
Format
Sort
Use autofilter
use pivottable

I will first save which cells are un/locked & hidden.

View 9 Replies View Related

Work Around Running Macro On Protected Workbook?

Jun 5, 2014

I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?

View 1 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

Excel 2007 :: Running Macro From Protected Workbook

Mar 13, 2008

I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.

View 9 Replies View Related

VBA Not Working On Another Worksheet When Protected

May 20, 2012

I have the following code which works fine when Sheet1 is unprotected (The code is placed in Sheet3)

however if I protect sheet1 then the code does not work

I have tried it with

Code:
ActiveSheet.Unprotect

Code:
ActiveSheet.Protect
it still will not work on Sheet1

here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo Handler
With Sheet1.Range("B6:B10045")

[Code] ....

View 2 Replies View Related

Hyperlink Not Working If Sheet Protected

Jun 1, 2009

I have a excel worksheet with hyperlink. If the sheet is protected I am unable to use the hyperlink even though I have unblocked the protected cells.

View 9 Replies View Related

Me.Rows.Autofit With A Protected Sheet Not Working.

Jul 23, 2009

Me.Rows.Autofit with a Protected Sheet not working.
Basically I want to use vb code which is

View 2 Replies View Related

AutoFilter And Sort Not Working On Protected Sheet

Dec 20, 2012

When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:

Code:

With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With

I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.

View 1 Replies View Related

Macros Not Working When Sheet Is Password Protected

Jan 9, 2007

I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?

View 9 Replies View Related

Odd One, Up And Down Arrows Stop Working When Sheet Protected

Jan 10, 2008

The sheet was working fine, then all of a sudden (i dont know what i have done) the up and down arrows (on my kayboard) wont work when the sheet is protected, when i unprotect the sheet they work fine, i can use left and right. i have checked the scroll lock isnt pressed/illuminated on my keyboard.

View 9 Replies View Related

Arrow Keys Not Working On Protected Worksheet

May 4, 2008

Have a file that I have been coding and find when I open to a worksheet with limited access cells, the Up and Down arrows will not move vertically though Lrft and Right arrows OK though not raping arround. This problem may have been on for sometime and I haven't been back to it. Other Excel files all OK.

View 4 Replies View Related

Excel Macro Stops Working In Copied Spreadsheet Within Same Workbook

Jan 4, 2012

I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.

My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.

View 1 Replies View Related

Dynamic Ranges (tables) Not Working On Protected Sheet?

Sep 10, 2013

I have a data set that is defined as a table (Ctrl + T). There are formulas so I locked those cells and Protected the sheet. If I am at the bottom right cell, and press Tab to add a new row, I get an error message: "Cannot use this table functionality on a protected sheet"

I really need to protect the cells with formulas so that the are not accidentally deleted. But I also want to retain the Table format.

Is there a way around this without using VBA?

View 1 Replies View Related

Check If Workbook Is Protected

May 1, 2007

I just noticed that if you already have a workbook protected and then run code that will protect it, it will become unprotected! (I have a line of code to protect the workbook in the workbook_open event; incase the last user unprotected the book on the last use). Apparently it doesn't work like protected worksheets, where if the sheet is password protected and you have code that tries to reprotect it with a different password nothing will happen. I can not find a way to check if the workbook is protected (and if so skip, but if not then protect). Worksheets have "activesheet.protectcontents" to tell you True/False.

View 3 Replies View Related

Insert Existing Working Code From Another Workbook Into Own Workbook

Jan 14, 2014

The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.

It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.

This is Jafaar's file [URL]

And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:

[URL]

And finally this is the file after I tried to copy the code over : [URL]

Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.

I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!

I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)

View 9 Replies View Related

Change Row Height In Protected Workbook?

Jan 21, 2011

I'm looking to enable changing row height in a protected workbook and am scratching my head as to if there is a combination of checkboxes I need to 'allow', or if there is some other approach.

I'm using a vba macro to change the height of merged cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single

[Code].....

View 2 Replies View Related

How To Unprotect A Workbook And Sheets That Already Protected

Mar 10, 2014

I have a workbook that is protected by password. I have to unprotect sheets.

How can I unprotect sheets and workbooks while I am updating or changing?

View 13 Replies View Related

How To Open Another Workbook That Is Password Protected

Nov 7, 2011

Code:
Sub Open_Test_File()
Dim wb As Workbook
Set wb = Application.Workbooks.Open("DesktopTest File.xls")
End Sub

This very simple little code opens a new workbook.

But if I add a password to the file that I'm going to open the password box appears.

What do I need to add to this code to automatically input this password?

I have tried putting this at the end:

Code:
Password:="123"
but this does not work?

View 2 Replies View Related

Link To Password Protected Workbook

Jan 24, 2012

I have a master for each manager that pulls a few values from each of their employees personal sheets.

The personal sheets (about 30) are individually password protected so only the owners can open them.

This is creating a security issues as the links in the master require the passwords for each of the "source" workbooks.

Is there any way to add some code so that the passwords do not need to be entered each time?

View 9 Replies View Related

Linking To A Password Protected Workbook

Apr 18, 2009

My question is as follows:

I have two workbooks in the same folder on a shared drive. Workbook2 is linked to workbook1. I have protected Workbook1 with a password protected to limit access.

When my collegues who use workbook2 try to open it, it asks for the password for workbook1 before opening. When they ignore that and go a head to open the file. The new information from workbook1 is not updated into workbook2.

How can i mentain my password in workbook1, and keep the files linked such that they can be able to see the new information in workbook2 without knowing the password for workbook1.

View 9 Replies View Related

2007 - Protected Workbook - Can't Unprotect It

Jun 15, 2009

I have a workbook that many moons ago I protected. Now I want the workbook to be an unprotected workbook so many users can view and update if necessary.

Typically I know you are supposed to go to: Review, Changes, Unprotect Workbook. But all that is available is to protect the workbook, but it is already protected. I know this because every time I open the workbook, I am prompted to enter a password.

Is there another way to remove this workbook protection I do not know about?

View 9 Replies View Related

Paste To Password Protected Workbook

Aug 25, 2006

I've got two files: the one that is the source file and the second one which is the target file. The target file is password protected (let us take the number 1 as the password in this case). I have a macro that would copy the value of an example cell to the target file:

Sub iso()

Dim wBook As Workbook

Range("A2").Activate

ActiveCell.Copy

On Error Resume Next
Set wBook = Workbooks("Test2.xls")

If wBook Is Nothing Then
ChDir "D:"
Set oWB = Workbooks.Open(Filename:="D: est2.xls")

to add a line (or two) to this code, that would open and unprotect the target file and then at the and protect the target file with the same password.

View 8 Replies View Related

Delete Special Cells And Protected Workbook

Mar 28, 2014

I have a code which deletes all contents of cell in range except formulas, from sheet 2 to 13. Code works, but I have included toggle button on each sheet to lock/unlock sheets.

If I lock sheets, code doesn't erase cells as needed.

I have figured out code that works for me, but looks like It's not performing quite correct. Although code executes quite quick...

This is my current code :

[Code] .....

How could I rearrange this code to work in proper order ?

View 4 Replies View Related

Userform As Printer Interface For Protected Workbook

Apr 21, 2014

I'm closing in on the final stage of a large project and I've run into a road block. The following line of code is preventing my users from being able to select specific page ranges when using the Print Entire Workbook option in the print menu.

[Code] .....

I've attached the latest version of my form (with significant support by jaslake) to explain what I'm attempting here. The attached form has several macros that activate upon open; including a user form. The password for the workbook/worksheets/VBE is "j".

Attached File : HCT-RCS ITP V1.89.xlsm

View 14 Replies View Related

Enable Outlining In A Shared/protected Workbook

Apr 22, 2009

I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.

Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

After enabling 'Share Workbook' and re-opening the file it presents the following error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

View 2 Replies View Related

Recover Or Break Password From Protected Workbook?

Mar 1, 2012

I need something to recover or break a password from a protected workbook.

View 1 Replies View Related

Group/Ungroup In A Protected Shared Workbook

Aug 20, 2008

I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?

Question: How can I group/ungroup columns in a shared (and protected) workbook?

View 6 Replies View Related

Updating Workbook From Password Protected Workbooks Automatically

Jul 7, 2014

I've 10 workbooks (which represent different areas around the factory) that populate a master workbook (belonging to HR).

We've now started to password protect the 10 workbooks. When i open the master workbook and click update - i get prompted to enter the passwords of the 10 workbooks

My question - is it possbile to automatically have the passwords entered so that the master can get updated?

View 1 Replies View Related

Sheet Tabs Disappear When Workbook Window Protected?

Nov 21, 2013

I have a workbook with multiple sheets. I would like to protect both the structure and window, but when I do this the sheet tabs are no longer visible when I reopen the workbook.

View 4 Replies View Related







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