Run VBA Macro Code On Protected Worksheet

May 18, 2009

I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?

'Unprotect Sheet
ActiveSheet.Unprotect

'Print preview & cell formatting code

'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

View 2 Replies


ADVERTISEMENT

How To Run Macro In Protected Worksheet

Mar 25, 2014

I want to run a macro in a worksheet which is proteced.

It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.

View 4 Replies View Related

Macro On Protected Worksheet

Aug 16, 2007

I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:

Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells

Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

View 4 Replies View Related

Sort Macro On Protected Worksheet

Jun 8, 2008

I'm not fond with macros, but this is what i need help with.

I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).

This is what the macro to do. Unprotect, sort selected column, protect.

View 7 Replies View Related

Copy & Paste To Protected Sheet Macro Code

Apr 1, 2008

how to unprotect the sheets in order to be able copying datas to to certain cells.

The unprotection works but only when I start the macro the second time. I do not understand why.

After I did the updates I want to reprotect the sheets but that seems not to work in my case.

ActiveSheet.Unprotect Password:="my_password_here"
'unprotect the sheet

ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="my_password_here"
' Reprotect the Sheet

View 7 Replies View Related

Protected Worksheet Macro To Insert Rows?

Feb 20, 2007

I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!

View 9 Replies View Related

Cracked Vb Code: Prevent People From Accesing The Code I Protected The Code Blocking It From Visualization

Feb 8, 2007

I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.

View 8 Replies View Related

Add A Worksheet To An Existing Spreadsheet Which Has VBA Behind It, But The Worksheet Is Password Protected

Aug 23, 2006

One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?

View 2 Replies View Related

Worksheet Function In VBA Macro Code

Apr 24, 2009

I am trying to write a function in excel to use the worksheet function "small" In the vba immediate window it doesn't return anything and in the spreadshhet I get a # NAME? error when called in a cell the code is below.

Sub UseFunction()
Dim myRange As Range
Dim answer As Integer

Set myRange = Worksheets("Sheet1").Range("A1:F6")
answer = Application.WorksheetFunction.Small((myRange), 1)
MsgBox answer
End Sub

View 3 Replies View Related

Macro To Write VBA Code In A Worksheet

Jul 13, 2006

how to write a macro, which in turn write some VBA code in a worksheet?

View 4 Replies View Related

Code For A Password Protected Database

Sep 16, 2009

I use the follwing code to update an access database from excel.

View 2 Replies View Related

Code To Prevent Calendar Pop Up When Protected

Aug 15, 2012

Some code that would prevent my user form (calendar) from popping up when the worksheet is protected?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$G$8" Or Target.Address = "$G$14" Or Target.Address = "$G$17" Then
CalendarFrm.Show
End If
If Target.NumberFormat = "m/d/yy;@" Then

[Code] ........

View 7 Replies View Related

Sharing Add-in With Password-protected Code?

Apr 10, 2013

I have an add-in which I want to share with others, but I want to protect the code from modification.

I know how to apply a password to a VBA project (Tools -> Project Properties -> Protection) but the problem is this: when a colleague installs the add-in, the subroutines within the add-in are not available in the Macros list to be added to the ribbon (in the Customize Ribbon window). The subroutines only appear when I remove the password.

View 1 Replies View Related

Color Code Protected Cells

Oct 23, 2007

Well I am back to the colour coding of protected cells

I am using excell 2003

Many cells in the worksheet are locked

My problem is that when I am debugging I forget to turn sheet protection on

when I am finished And then users can overtype the cell formulae

How can I set excel up so that when the sheet is protected all the Locked cells have a green background , but when the protection on the worksheet is turned off
then the locked cells must be a red

View 4 Replies View Related

Macro Code To Remove All The Hyperlinks From A Worksheet.

Apr 2, 2009

What I’m after is the macro code to remove all the Hyperlinks from the data in the cells in a worksheet.

View 2 Replies View Related

Macro Code To Copy Worksheet From One Workbook To Another

Dec 30, 2011

Here is the code I have:

Windows("2.xlsm").Activate
Sheets("Report_P").Select
Sheets("Report_P").Copy After:=Workbooks("New_report.xlsx").Sheets(9)

2.xlsm is open. it contains a sheet called Report_P New_report.xlsx is open and has 9 existing sheets

Every time this tries to execute I get a "Run time error 1004 Copy Method of Worksheet Failed".

What is wrong with this code? I have an identical line in another macro, the only difference is that there are 5 sheets pre-existing, and that works fine.

View 6 Replies View Related

Macro Code To Change Active Worksheet

May 12, 2006

I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed.

View 4 Replies View Related

Reference Cell For Worksheet Name Macro Code

Apr 11, 2008

I've got 3 sheets, A, B, and C. In sheet C, I have a cell named "element". That cell can have either the letter A or the letter B in it. In my VB code, I want to reference the sheet which correlates with the "element" cell. So,

Worksheets("element").Select

when the user clicks my "go" button, it calls this logic, and I want the worksheet that correleates to the letter in the cell in worksheet C - to open.

The reason for my posting - this is not working, and may not even be possible. Perhaps someone has experience in this and knows how to do it - or knows a different way to do this.

View 4 Replies View Related

Macro Code To Add Reference On Sheet To Next Worksheet

Jun 29, 2008

how to use VBA to write a formula in one sheet that refers to a fixed cell on the sheet to the right. The referenced sheet may have different names and it may be in a different order (i.e. not Sheet(2)) but it will always be one sheet to the right. I just want to have cell A1=(cell B2 one sheet to the right).

View 7 Replies View Related

Refreshing Protected Worksheet

Jan 5, 2013

I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.

In turn, the current information is displayed on a summary page so we can all see who's doing what.

I want to protect that summary page and its formulas from accidental amendment.

However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.

Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?

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

Filtering In A Protected Worksheet

Dec 12, 2007

I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?

View 9 Replies View Related

Combo Box On Protected Worksheet

Nov 16, 2006

I've built a workbook using Excel 2000 that uses several combo boxes. When I try to protect the workbook the combo boxes become locked, even when I have done FORMAT CONTROL / PROTECTION and unticked the LOCKED box. Is this normal?. I also have data validation cells and they work fine if I unlock them and Protect the workbook. As a result, I am thinking of converting the combo boxes to data validation cells, but should I even need to do this

View 3 Replies View Related

Button Code Does Not Activate When The Sheet Is Protected?

Aug 18, 2006

I have a button on a sheet which needs to remain protected.

However, the button code does not activate when the sheet is protected...

Any hints on how to "unprotect" just the button?

View 9 Replies View Related

Macro Code Only Works If Specific Worksheet Active

Apr 30, 2008

I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:

Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................

View 2 Replies View Related

Data Form On Protected Worksheet.

Dec 15, 2008

I have a data list which needs to be updated by others on a shared drive. I want to protect the worksheet as there is other info on it (advancefilter from the main list). The problem is that the >>Data>>Form tool will open, but all the boxes necessary to update the list are greyed out (New, Delete etc.)

My other option is to create a macro to advance filter out the other data to another worksheet.

View 3 Replies View Related

How To Unlock Worksheet (Password Protected)

Sep 13, 2013

What if you forgot the password in a protected sheet? is there a way to unlock it?

View 1 Replies View Related

Insert New Rows Into Protected Worksheet?

Oct 13, 2013

I have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.

I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)

I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.

View 3 Replies View Related

Hidden Rows In Protected Worksheet?

Jul 21, 2014

I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.

Macro command used:

Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"

View 3 Replies View Related

Allow Adding New Row To A Table When Worksheet Protected

Feb 10, 2014

I have A TABLE (Named Table1) and some columns filled by formula. (Like E,F,G,H columns) When I protect the worksheet E,F,G,H columns are locked, but A,B,C,D columns are UNLOCKED. When is sheet PROTECTED , If I add a row bottom of Table1, does NOT automatically fill the E,F,G,H columns and Table1 range does not expanding. But, if UNPROTECTED, it's OK. when add a row bottom of Table1, automatically fill all columns with formula and table range is expanding aotomatically with new row.

When sheet is PROTECTED , How can I allow, to add a row and aouto fill all columns like unprotected.

And I want to protect sheet in any case, after allowing to add row.

I need a macro code to do this.

View 5 Replies View Related







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