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:="".........................
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 RelatedOn 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 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!!
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?
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
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.
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.
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?
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 RelatedI'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
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.
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?
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.
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 RelatedCan you passwrod protect a tab or a range of cells within a spreadsheet
View 9 Replies View RelatedI 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.
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies View RelatedI care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Compile Error:
Expected: list separator or )
[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")
[Code].....
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
code to take jobs that are done on one spreadsheet and send them to the next available row of a specified range on another worksheet. I am also looking to incorporate a way to delete the empty rows that are left behind.
I have already made code to select the completed jobs, but am still unsure how to approach the next part. I can't even paste the selected range to another sheet without errors from range sizes.
The following code is done up to the point where I cut my selection.
Sub Update()
Worksheets("OpenGen").Activate
Dim c As Object
Dim rngA As Range
[Code]....
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value
I 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 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)?
