Protect Worksheets With UserInterFaceOnly On Mac

Jun 15, 2009

I have used for some time the technique of having the worksheets protected and the (through buttons) call macros that Applicatition.Unprotect the sheet do something not allowed while sheet protected and then Application.Protect. They work. Then I read about UserInterfaceOnly and I wanted to use it. I tried unsuccessfully a sort of testing

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect "aPass", UserInterfaceOnly:=True
Next wSheet
End Sub

Code for three buttons on the same sheet. Very simple code to try whether previous code behaves as expected.

Sub FillValueActive()
Dim activeNum As Integer
activeNum = ActiveCell.Row
Range("A1").Offset(activeNum - 1, 0).Value = "This text shows filled"
End Sub .........................................

View 9 Replies


ADVERTISEMENT

UserInterFaceOnly

Aug 10, 2007

In the Workbook_open I've got the following:

Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="test", UserInterFaceOnly:=True
Next ws

As I understand it this should allow macros to work. What is happening is that on firing the first macro (which involves a sheet change), a small message box comes up asking for a password. However, if you hit 'cancel' in this box it carries out the macro anyway. What is going on here?

View 9 Replies View Related

UserInterFaceOnly In Shared Workbook

Nov 16, 2006

I have a workbook that i am trying to make a shared workbook.

Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.

Once a workbook is shared it's protection cannot be changed.

I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).

Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.

Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.

View 6 Replies View Related

Password Protect Worksheets

Feb 5, 2009

I am seeking a method of password protecting worksheets in a workbook. The users would all start on a sheet that has their names (A1 - A10) next to their names would be where they type in their password (B1 - B10). If their password is correct their assigned worksheet (Worksheet2) would be unlocked for editing.

View 11 Replies View Related

Using Macro To Protect And Unprotect Worksheets

Oct 23, 2008

I am looking for a way to protect and unprotect all worksheets with a password through a macro. I have excel 2007.

View 2 Replies View Related

Protect Worksheets & Set View Settings

Oct 18, 2006

I have a battery sizing workbook, which has various tables and active cell VBA routines, that enable the user to input a number and then select a cell from the table presented on the front worksheet. This will then display the solution including other data. I have now been requested to share the workbook and all I seem to do is lock it up and therefore stop and calculations. Therefore your assistance is required in two area's:-

1. How to protect the workbook and allow the user to select a cell on the front worksheet, without all the all the VBA routines appearing to grind to a halt! I can lock an hide all the subsequent worksheets without freezing the front worksheet.
2. I can hide all the toolbars, row and column headings under my Tools/Options which will display the worksheet neatly...... Therefore can I some how instruct the users Excel to turn these off when loading?

View 2 Replies View Related

Password Protect Worksheets From Viewing

Jan 3, 2007

Here is what I try to achieve:

- I have a "Menu" worksheet with basic Hyperlink to Sheet1, Sheet2 etc.

- When I click on a link (or on the tab), I want a prompt asking for a password (different for each tab). I do not anyone who doesn't have the correct password to see the content of the worksheet.

(I don't know VBA and I couldn't modify the code to fit my needs)

Here is the code posted

Dim sLast As ObjectPrivate Sub Workbook_Open() 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1. Name = ActiveSheet.Name Then Sheet2.SelectEnd

View 9 Replies View Related

UserInterfaceOnly Settings (macro To Run Even When The Sheet Is Protected)

Jan 14, 2010

I tried to use the code that allows macro to run even when the sheet is protected.

View 3 Replies View Related

Macros To Protect / Unprotect Worksheets And Workbooks

Jul 20, 2005

The first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.

VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object

[Code].....

View 9 Replies View Related

Auto Hide And Protect Worksheets Before Closing

Nov 15, 2011

I wrote the following code to hide & password protect an excel sheet :

[Code] .....

How could I alter it so that it hides and protects this sheet automatically before closing the workbook ?

View 3 Replies View Related

Password Protect Viewing Of Individual Worksheets?

Dec 1, 2009

I would like to adjust the code found in the attached thread so that i can allow one person access to more than 1 sheet

Password Protect Viewing of Individual Worksheets?

View 9 Replies View Related

Password Protect Viewing Of Individual Worksheets

Nov 6, 2009

I have a file w/ individuals monthly sales information (per worksheet) that needs to be distributed in one mass email to the entire sales force. I want everyone to have access to the "Summary" tab, but individuals should only have access to their own "Details" tab. I know there has to be a way to accomplish via VBA. However, I'm not proficient in writing code.

View 9 Replies View Related

Macro Paste Special, Protect, Delete Worksheets

Jul 19, 2007

I am looking for a macro that can do one of two things. I am not sure which way I want to go with this yet and how automated it should be. Either scenario will result in a significant time save.

Scenario One
1. Copy & Paste Special Values on the selected/active worksheet
2. Protect Selected Worksheet with password "1234"
3. Delete ALL worksheets except the selected/active worksheet from the file. Names of other sheets can be various and are not static

For example:
A workbook has the following: sheets1, sheet2, sheet3, sheet_unknown_name
If you are working in sheet1 the macro would copy paste special values the entire sheet, protect sheet1 with the password "1234" and delete any and all other sheets.

Scenario Two
1. Copy & Paste Special Values on the selected/active worksheet into a new workbook
2. Protect the new workbook with password "1234"
3. Rename the new workbook with the original workbook name adding either "em_" to the front or "_em" to the end of the filename
4. File, Send To, Mail Recipient as attachment.

View 4 Replies View Related

Protect Certain Locked Cells From Editing And Allow Certain Unlocked Cells To Be Changed On Multiple Worksheets?

Jan 31, 2014

1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.

2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)

3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .

View 1 Replies View Related

Password Protect Columns - Hide And Protect Ability To Unhide

Jan 17, 2013

Some sensitive data is held on worksheets that are used by people that do not have access to see the data.

It would be most convenient to keep the data in these worksheets but hide it from view of the users.

Is there a way to password protect a users ability to unhide a column?

View 2 Replies View Related

Password Protect: Unlock The Worksheet, Autofilter It, Print It, Unfilter It, And Password Protect The Sheet Again?

Jan 12, 2007

I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Columns("O:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

View 3 Replies View Related

Merits Of VBA Protect Vs Non-Protect

Jul 26, 2006

I would like to protect the intellectual property of an application and have read differing opinions of the merits of protecting VBA code. What are the opinions of the experts on this forum? Are the password breaker programs so advanced that protecting code is a waste of time and only a hinderance to debugging and troubleshooting?

I am using MS Excel 2003 (11.5612) on XP.

I apologize if this topic has already been covered on this forum. I searched but did not find exactly what I was looking for. I am happy to review an old post if one exists.

View 7 Replies View Related

Excel 2010 :: Sort Worksheets Alphabetically And Keep The Data In Worksheets

May 15, 2013

I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.

View 2 Replies View Related

Loop Through Worksheets Not Working (delete Some Hyperlinks In Column A On 50+ Worksheets)

Jan 16, 2009

Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?

View 2 Replies View Related

Copy Cells / Range From Worksheets Positioned Between Two Worksheets

Jul 7, 2014

Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)

What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).

But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.

Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".

View 4 Replies View Related

Loop Across Worksheets: Perform The Same Process To All The Worksheets In My Workbook

Aug 10, 2009

I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:

View 2 Replies View Related

Selecting Worksheets Array When #of Worksheets Will Vary

Apr 26, 2006

How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...

View 3 Replies View Related

How To Protect Sheet

Jul 10, 2014

I make excel file that contains certain formulas and graphs. Now I wonder is that a way for protect that file so it can be used only on specific cumputer, so if someone copy that file and paste it on their computer file will not work properly

View 1 Replies View Related

Protect The Range

Apr 5, 2007

i have a sheet that i write the date in Column D ,

A B c D
code name amount date
1 123 nasser 12.333 01-04-2007
2 123 nasser 122.333 02-04-2007
3 123 nasser 122.333 03-04-2007
4 123 nasser 122.333 04-04-2007
5 123 nasser 122.333 05-04-2007

what i try to do is to protect the range from A1 to D4 , which means to not edit any data in the day that before today , and to do it automaticaly every time i open the sheet
how to make that micro

View 13 Replies View Related

Protect An Addin

Nov 18, 2008

I am trying to protect an addin I have. I can protect the shared woorkbook, but when I save it as an addin the code is visible to the addin.

View 2 Replies View Related

Protect Call

Dec 8, 2008

I can protect my all sheet in excel but How to protect single cell or column, row? Is there any code for protect.

View 14 Replies View Related

Protect Workbook

Jan 19, 2009

Is there a button to protect a workbook in the same way that you can protect sheets? For example, I don't want a user to be able to touch any locked cells and there's multiple worksheets in this workbook. Do I have to go to each worksheet and protect them or is there a button to protect all worksheets at once?

View 3 Replies View Related

Protect The VBA Of Workbook

Mar 27, 2009

I have created an Excel workbook and protected the sheet. However, it still allows me go into the VBA section (Alt+F11) and edit away to their heart's content.

How can I protect my spreadsheet, as well as the VBA associated with it?

View 4 Replies View Related

Protect A Worksheet From Changes

Apr 24, 2009

how to protect a worksheet from changes so that I am still able to change one cell on the worksheet but not any other cells.

View 2 Replies View Related

Protect By PC Serial

Dec 5, 2007

is there a VBA that can protect the workbook by the computer serial Number .. so it won't work on another pc than this?

View 14 Replies View Related







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