Macro For Unchecking Protection Properties While Protected
Dec 4, 2006
While my excel file is protected, I want to have a functionality that allows user to modify on certain areas (of the file), such as below:
1. After the user select number of years at the drop-down list (Main sheet), rows and columns at other related sheets will shift and hide certain values which corresponded from the drop-down list (e.g. if user selects 3 years, then at other sheets, rows from year 1 till 3 will be displayed, and rows for year 4 and year 5 will be hidden).
2. Users can add rows for entering new data at a new line and delete rows for deleting data at the correspond line.
This PC is using the Excel version 2000. At Tools > Protection > Protect Sheet, the available options are 'Content's, 'Objects' and 'Scenarios'. In order to allow users modifying rows/columns (while the file is protected), options such as 'Format Rows' and 'Format Columns' must be checked. But these options are only available in the later versions I believed.
After I have done macro recording for the protection properties at Excel version 2003 (my sister's PC), this is what I got for the .....
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 need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.
I have a workbook containing many different sheets. What I want to accomplish is when I click on a check box on one sheet, I want the check box in a different worksheet to be unchecked. I have form control checkboxes in my workbook, so I used xlOff as the value.
Code that will make it so that when a checkbox is UNCHECKED, the sheet that it originally opened re-hides itself? In other words, I have a mcor that works great that allows for a checkbox when checked to unhide a worksheet and take the user to that sheet. Now I need it si that when it is UNCHECKED, the sheet goes back to it's hidden state. This is what I currently have that UNHIDES it. Obviously somehow I need it to REHIDE it after being unchecked:
How would the corrected code look AFTER being added to this one:
Sub CheckBox615_Click() Sheets("FedEx Freight Opp Form").Visible = True Sheets("FedEx Freight Opp Form").Select Range("B16").Select End Sub
I have a Multi Select ListBox with values from a range of cells.
I wish to automate the selection of one or more existing value(s) via VBA code so when User views the List, The check mark is visible. This will alert them to Active values.
i.e ListBox:
North South East West.
If the active region is West, The check box beside West is Active, i.e contains an "X"
I have a button wich will add data entered on a form to a spreadsheet if the data meets certain criteria. If it does not it will not let you add anything. I would like to add a second button that will add the data even if it does not meet the criteria but I need the second button to only be enabled with a password. Maybe a macro to change the button properties from locked to unlocked (true to false).
I know how to protect VB code (e.g. Protection tab of VBAProjectProperties), however I would like to know how to embed code to stop the user from accessing the Macro (via Run Macro).
I understand you can do this by adding "Private" to the subroutine, however, is there some code I could add into a macro/connecting to a button, that would enable the user to protect the sheet (without needing to manually type private within the Module subroutine)?
I recorded one that does a very simple task, converts formulas to values. I saved it in my personal workbook, all is well. I noticed that excel names them Module1, Module2 etc. I thought it would be better to name them, so in the properties window next to "name" i changed Module1 to ConvertFormulas.
However, when i go to a worksheet, the keyboard shortcut no longer works. Reverting back to the old name Module1 solves this. Clicking Tools -> Macro -> Macros, selecting ConvertFormulas and then Options and modifying the shortcut.
The macro im using copys a table from excel into a email. Unfortunatly the email is not readable for blackberry users due to the table properties.
I need to change a few properies. This is what i do manually.....
In outlook i then hover over the table right click then select "Distribute rows evenly" then i right click again but this time i select "Table properties" i then go to the "Row" Tab
i then untick the "allow row to break across pages"
then tick "specify height" and change the value to 0.4 cm and finally change "row height is" to "exactly"
Below is the whole code im using so far....
Code: Sub Mail_Selection_Range_Outlook_Body() Dim rng As Range Dim OutApp As Object
I have a bunch of Option Buttons in a worksheet whose properties i need to change by running a macro. For example, I created an OptionButton1 in a worksheet using control toolbox. Now I want to run a macro to change it's name, groupname, linkedcell and caption. I did this macro but it doesn't work:
Sub Label() Dim Code1 As String Dim Link1 As String Dim Form1 As String Dim GroupName1 As String Dim Caption1 As String Worksheets("Section1").Select Form1 = "OptionButton1" Code1 = "FButton1" Link1 = "FLink1" 'Defined in worksheet GroupName1 = "FGroup1" Caption1 = "Choose Function 1" With Worksheets("Section1").Form1 .Name = Code1 .LinkedCell = Link1 .GroupName = GroupName1 .Caption = Caption1 End With End Sub
i have a spreadsheet with about forty different sheets and all of these sheets are protected with the same password, and wheni come round to doing an amendment to the spreadsheet i have to unprotect and then re protect each sheet in turn, very time consuming
when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.
I have a button that executes a macro which basically activates a worksheet. I wish to put a password which would allow only certain users to access this worksheet through this button.
My macros are set up with hard-coded passwords to protect and unprotect the sheets where they run.
But I need a way for the macros to refer to a user-provided password without having the user edit the macos themselves. The simplist approach (I suppose) would be to have a worksheet for this with a cell for the user to enter the password he/she wants. The macro protect/unprotect code would then look at the cell to get the password.
I've been using the attached spreadsheet to record daily sales for a while now, however, we've recently intorduced a new product and so I've had to add a new column to record daily sales of it.
On the spreadsheet is a 'New Week' button. Pressing this deletes all entries and moves the week numbers on by 1, so giving me a blank template each week. However, after the addition of the new column I'm now getting a bug error message. I've adjusted the code to take account of the new column but I'm still getting a bug error. When I press 'debug' it's highlighting something I have changed so I'm a little confused why I'm getting the bug error. Would someone have a look for me. I only seem to get the bug when there is protection on the sheet, I need protection. The password is PHUKET38
I was wondering if it is possible to protect a macro to be used only if a *.txt (for example) file wit some certain text is available. like a key to run hidden in a text. The file is situated in a company network, and i want to protect it from taking the file out of the company. I can do it to check for presence of a file - if the file is not available - the macro exits. but if someone break the password that is protecting the code will see the blocking point immediately. this is why i want to use a key....
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save: 1. Cell values 2. Cell formats incl merged cells, borders, colour, font etc. 3. The row and column sizes 4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls" The tab/sheet to save is "Front Sheet" (There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
I am using the macro below to import every spreadsheet into a folder. I would like to modify it to check the sheets it is importing for sheet protection. If the sheet protection is turned on, I would like it to turn it off. The password for the sheet protection will be "PIR".
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
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:
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.
