Worksheet Protection And User Rights
Aug 11, 2008
I have a worksheet that has been designed to allow the user to enter data, in certain cells of the worksheet are formula to do automatic calculations as the user enters data. Here is my dilemia
a) I need the cells with the formula's to be locked so the user cannot delete or alter them.
b) In cases there may be a case where rows will have to be deleted or inserted, depending on the data entered.
c) The sheet needs to be protected to avoid the user from altering the sheet i.e. design etc.
I have found the problem is that i can lock out all the cells that i do not want the user to alter, and i can protect the worksheet and allow users to have certain edit right. But the problem i am facing is that when you have locked or protected cells on a wprksheet and you protect the sheet, then you can not delete or insert rows.
Is there a way that allows the sheet and formula to be locked, but also allow the user to delete or insert rows if they need.
View 14 Replies
ADVERTISEMENT
May 1, 2007
I have an Excel VBA routine which opens another Excel file as a part of the routing. For example:
' Beginning of the code.
Workbooks.OpenText Filename:= Range("log_file").Value, Origin:=xlWindows
' Rest of the code.
The problem I run into is when the user does not have access (via Windows Active Directory) to the file located in the "log_file" range. They usually will get the old runtime error 1004. I can easily trap that error but err 1004 is used so many other times, I don't want to mistakenly trap it as something else. Does anyone know of a way to verify and/or check if the user has read/write access to a file (or folder) before opening it?
View 4 Replies
View Related
Feb 26, 2007
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.
View 9 Replies
View Related
Jun 1, 2006
how to allow formating a text and cell in protect sheet ?
View 4 Replies
View Related
May 2, 2007
I 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 Related
Jul 23, 2007
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.
View 9 Replies
View Related
Mar 7, 2002
I'm trying to protect a certain range of cells in a worksheet from within Access.
My code is below:
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Worksheet
Dim intColumn As Integer
Dim intRow As Integer
Dim intRecords As Integer
Set objExcel = Excel.Application
objWS.Unprotect
objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:="".........................
View 9 Replies
View Related
Jan 13, 2007
I have a workbook where I need to protect the sheets with a password. However, for certain macros contained in the workbook to run the sheets need to be unprotected. I know the code to unprotect the sheet but the password is required. I do not want other users to know this password.
What I need is the code to protect a worksheet with a password. Then, when a macro runs, to unprotect without user having to enter the password and reprotect with the original password when the macro has completed its run. note: I am NOT looking for any form of password breaking code!!
View 9 Replies
View Related
Aug 18, 2007
I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:
Sheet1.Protect Password="abc" UserInterfaceOnly:=True
However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection?
That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?
View 2 Replies
View Related
Jun 19, 2009
I have a spreadsheet which has extra worksheets at the end that the user can move around, if they need them.
Then i found out that users were adding there own sheets, so i protected the workbook, but i found out that this stops the user from moving the sheet.
Whats the easiest way probably using code to disable the worksheet functions, ie the ones that appear if you click on the tab (insert,delete,rename,move+copy,select all sheets etc) but still allow the user to move the sheet within the workbook
View 7 Replies
View Related
Jan 24, 2010
All my macros in Module 1 work OK when I password protect my Excel 2003 worksheet named: Data. The only code that does not work when I password protect my worksheet is the Private Sub shown below. If I do not use a password, it works OK. As soon as I enter a password to protect my worksheet, the code below no longer works.
In ThisWorkbook, the following code shows the password to be password. In reality, there is a different password.
View 10 Replies
View Related
Nov 29, 2011
Any way to transfer the sheet protection from one worksheet to another worksheet via VBA.
Currently, the user selects a name from a drop down list, it unhides a workbook of the same name, copies from that workbook, and places the copied information in an area of the sheet with the drop down list. I would like the protection settings (in this case, selectivly unlocked cells to allow users to easily tab through and not make changes to data that should not be changed) to be copied with this information. Is this possible?
View 2 Replies
View Related
Dec 12, 2011
I have created a worksheet and password protected, I know the password but now want to send to someone and remove the password protection option instead of them having to use a password to open it.
View 5 Replies
View Related
Sep 12, 2006
I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
View 9 Replies
View Related
Jun 5, 2014
I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
View 2 Replies
View Related
Dec 26, 2013
I am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
The code I am trying is:
Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"
End Sub
How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?
View 4 Replies
View Related
Nov 5, 2012
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
View 3 Replies
View Related
Mar 16, 2007
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Aug 18, 2004
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.
View 9 Replies
View Related
Sep 13, 2007
I'm trying to write a macro attached to a button which will add another sheet to the workbook with a specified name. I'm running into problems when I try and reference this new sheet, since when a sheet is added it's sheet number is not necessarily the next available number. Like if I inserted two sheets, Sheet1 and Sheet2 and deleted one the next sheet I inserted would not be Sheet2 but sheet3. This is causing me some problems in trying to make my code error proof. Here is what I have.
Sub newSheet() ..
View 3 Replies
View Related
Feb 11, 2009
I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?
View 4 Replies
View Related
Oct 19, 2009
I've only just starting using Excel for anything other than basic calculations and have got a little stuck with a user form. On my attached spreadsheet I am trying to set up a form for staff incident reporting. There is a Contents worksheet and then each incident has it's own detailed worksheet. The user will click on 'create new incident' on the Contents page.
This opens a user form. From this form I want to populate the contents worksheet. I then also want it to populate the relevant incident worksheet. I can populate the contents page but I need help getting the correct Incident worksheet populated at the same time. Hopefully all will become clear running the file.
View 5 Replies
View Related
Dec 6, 2007
I am creating a spreadsheet for work - I have a column that is all the formulas projected sales minus actual sales.
I need to lock this column so that no one BUT MYSELF can change the codes. In other words "idiot proof" the worksheet. there are only 18 sections that I want anyone else to be able to type in.
is there a way to do this without adding users? half the ppl have personal emails and half have company emails.
View 5 Replies
View Related
Jan 7, 2009
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
View 9 Replies
View Related
Mar 22, 2009
VBA code to get userform input from form to "Data" worksheet. Data starts at columns A3..AG3. I need the code for the "Input" button to put Month, Date, Type, Comments under their corresponding columns and the "Amount" to go under the appropriate column that is selected by the Option Button. I've included a picture with in "red" comments.
View 8 Replies
View Related
Aug 17, 2011
I have a macro that I would like to let a user edit the worksheet and make changes at a certain point. I have tried creating a modeless userform, but the userform only pops up and the code continues running without waiting for input (or running any of the code in the userform). Any way I can accomplish this or what I would be doing wrong with the userform?
View 7 Replies
View Related
May 31, 2006
I have a workbook which contains multiple worksheets of employees' information. I'm hoping to share this workbook out. Each employee only able to view and update their own worksheet and their manager, being able to view/update everything within the workbook.
View 9 Replies
View Related
Jun 16, 2006
I am trying to figure out how to delete a row on a worksheet via a VBA User Form. I currently have a User Form with a Combo Box that is populated by cells in a named range ("PickCategory...") on a worksheet... I am populating thsi box using this
Private Sub UserForm_Activate()
ComboBox1.List = Worksheets("data").Range("PickCategory...").Value
End Sub
First off, I want to include a lable that will display the contents of the cell to the right of the currently selected entry of the combobox on a label (or textbox). So, whatever entry you select in the combo box, the corredponding value (to the right of it on the worksheet) will appear in the text/label
box. Is there something similar to the VLOOKUP that will work in VBA?
Also, what I want to be able to do is to allow the user to select one of the entries from the combo box (which is already working) and then be able to delete the row of that entry on the worksheet. So, the user selects an entry from the combo box and then clicks a "Delete" command button to delete the row of that entry on the worksheet...
View 6 Replies
View Related
Aug 30, 2006
Is it possible to show a timer on a worksheet as a user inputs to it? I can get the timer to appear, using Application .OnTime Now() + TimeValue ("00:00:01"), "AutoRecalcWorkbook", but it's kind of modal -- that is, the user can't input at the same time it's running.
View 2 Replies
View Related
