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 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
each sheet is naturally named different
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.
View 14 Replies View Relatedwhen 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.
View 2 Replies View RelatedI 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.
View 2 Replies View RelatedMy 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....
View 9 Replies View RelatedWhile 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 .....
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 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.
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
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".
View 3 Replies View RelatedI have a macro that copies the contents of a cell, and pastes it into the the first blank cell of a range. Its important that the entire sheet is protected, but the macro won't allow the paste function because of the protection.
Is there a VBA code to unprotect the sheet, run the copy/paste macro, then protect the sheet again. THe problem is I would prefer the protection to use a password, as I don't want the user to simply unprotect the sheet from the menu bar.
I tried the following code and it doesn't work:
Sub Macro2()
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Range(Range("A" & Rows.Count).End(xlUp).Row & "A300").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("AE11:AG300").Select
Selection.Locked = False
Selection.FormulaHidden = False
End Sub
I want to be able to unlock all cells after the last cell that has data in column A down to row 300. Also need to unlock cells AE11:AG300. What's wrong with my code?
I have a worksheet where there are a few columns. The columns involved in my problem is Column A and B. So the users open the worksheet and they change the values of column A. Column B has a vlookup formula and if the value of column A is changed than column B automatically changes its value as well (vlookup).
My problem is that the users of this file are not experienced computer peoples so, sometimes (by accident) they change the value of column B (deleting the formula). I tried to set the protection for column B.... but then it will not allow any change (vlookup will not work) to the cells in column B. So my question is that how can I allow the users to see the values in column B but not to edit it..and also let excel to let the formula to change the values of column B (if column A value is changed)?
is there a way that i can stop people being able to edit certain cell in a sheet but still allow them to type in other cells as when i have tried diffrent ways it locks the whole sheet
View 5 Replies View RelatedI have set up a workbook containing 15 sheets. 12 of them are named Jan to Dec. I KNOW how to protect each one, but is it possible to protect all twelve in one go?
View 9 Replies View RelatedI have a worksheet that I want to protect, but when I use the protect worksheet command on the tools menu, it renders all of my VBA useless.
View 11 Replies View RelatedI'm trying to set a check during the WorkBook_BeforeSave event.
View 4 Replies View RelatedI'm trying to protect my project so that others can't unhide a sheet I have tagged as veryhidden within VB.
I followed these steps:
1. Select project in the projects window
2. choose Tools
3. Project Properties
4. Protection tab
5. checked lock porject for viewing
6. entered passwords twice
7. clicked ok and saved
On another file this has worked perfectly as I wanted it to.
However on another very large file with multiple VB projects it is not "taking" on the project I need it to.
I can open the file, the VB project and change the setting on any sheet I need to without entering a password.
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.
On my worksheet i am using advanced filters to view the data in the sheet.
But when I protect the sheet they do not work, I have unlocked those particular cells (Row 1). But it still does not allow the use of the advanced filter when the sheet protection is on.
I use the following piece of code to show/hide certain worksheets in a workbook. To access the hidden sheets, a command button runs the code. It works very well, except that the password is openly displayed in the message box (as opposed to returning asterisks for the typed characters).
Sub togglesheets()
Dim Ws As Worksheet
Dim strPassword As String
strPassword = InputBox("Enter Password")
If strPassword "Password" Then MsgBox "Wrong Password": Exit Sub
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVisible Then
Ws.Visible = xlSheetVeryHidden
ElseIf Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
End If..............
This is something someone asked me and told me it's possible without VBA. I don't know if it is. I'm sure if it is possible, someone here would definitely know!
I have workbook, which I can not protect. It's sort of a template, so is used again and again with different data. It's accessible to many users. However, I don't want any changes in that workbook once it is closed.
For example, a user opens the workbook, he makes changes in the data, takes the outputs and uses it somewhere else. Now, when he closes it, it should revert back to the same as it was when it was opened. Even if the user saves it and closes, it should remain the same.
Is there a function of formula in Excel that will show me which cells are protected. I have a worksheet that needs certain cells to be protected. I don't know how i can get on good look to see if the are all protected.
Is there a conditional formatting formula possible.
Is there any way I can protect my sheets properly..? I know you can use Tools > Protection.. but I've found (and used, for good, not evil!) macros on the web that will crack these in seconds. Is there any way I can disable the 'Tools' menu so that other users can't load these password crackers?
View 9 Replies View RelatedI 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.
I have a workbook and i have spent time protecting all the formula cells and allowing user to only be able to select unlocked cells (so they can quickly tab to where they need to input info).
I have lots of codes running and always use Activesheet=unprotect If i am modifying protected cells and then at the end of the code i put back the activesheet=protect.
HOWEVER
When i transfer this book to another machine or to someone else to run it on their machine - ALL of my protection GOES OUT THE WINDOW
Also - is there a way to hide or protect all my code so that users cannot modify it?
If so - how do i get it back to modify state when i want to edit it?