I have a form that will be populated with different numbers of records. I need the records to be locked, but the remaining rows (if the user needs to add new records) to be unlocked. Can I use some sort of range.end function for this?
View 9 Replies
I have a Workbook named "Master Archive" that contain data from 9 workbooks. This is all done with a macro upon opening each workbook and using a command button to send the data to it. Question is I want everyone to be able to view the Master Archive Workbook in a readonly fashion and the orginal to always remain closed. Reason being is I cant run the macro to archive the data if someone has it open, which then would cause debug issues with the code.
Are there settings were I can be the only one to open the workbook and be able to Edit.
Also Everyone has a shortcut to this Master Archive on there Desktop.
I have 0's and 1's in Column AT5:AT370 on my sheet. I need to keep the last 1 in the Column, but change the rest to Zero's.
This will be activated on the Worksheet with a button.
I have a spreadsheet that was given to me, which I used as a template. It already had password protection in it for each worksheet, and the macros are also written to unprotect before performing the actions, the re-protect at the end. Everything works fine except I cannot change the password. The thing is is that the password in the macro code is not the same as the actual password which you have to type in when unprotecting the worksheets.
For example the password in the code is "IPP" and the password you have to actually enter into the unprotect sheet box is "AAAABBBABBB&", yet all the macros still work like this. When I attempt to unprotect the sheet (with the AAAA... code) and re-write the password codes in the macros to say "###" then re-protect the sheet with the new password and run the macros it'll work fine, but when I save and close and then re-open the password required to unprotect the sheet reverts back to the "AAAABBB...." and then the macros stop working because the passwords don't match (even though they didn't match before!!!)
There is no encryption password or workbook password as far as I can tell, only the worksheet protection. Why cannot I no get the password changed?
Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.
View 3 Replies View RelatedI want to utilise an artificial protection by keeping a cells value constant even though it can be selected, deleted, edited etc.
Is this possible? I want to make a sheet where the cursor isnt visible except on certain sets of 4 cells.
My method being leave the 4 cell sets unlocked and lock everything else and then protect the sheet.
This means only the 4 cell sets are selectable/editable but I only want them to be selected (as this triggers events) but I dont want their values to be changeable. The values will always be 0,1,2,3
I wrote a very simple macro in an Excel file a few months ago. Worked great. Worked on my computer, and worked on a dozen other people's computers perfectly. Now, months later, ONE person is getting the error message 'Code execution has been interrupted' every time she runs the macro. I've tested it on my computer with Excel 2007, and it worked. Tested it on another computer with Excel 2003 (offending computer has Excel 2003), and it worked again. So I tested a third computer on Excel 2003, and it worked again. I'm 100% positive the macro code isn't causing this error. This makes me think that it's related to an application setting that was changed somehow.
View 5 Replies View RelatedI have a vast amount of data entries in a pivot table (over 100). I want to create a pivot table but when I click on it it says 'count' and not 'sum'. It will take me a long time to change them all, how do I do it quickly? And possibly, without the 'sum of ....' in the label of it?
View 3 Replies View RelatedWhen I protect the entire sheet, I can click in a cell and see what's in it, when I protect just a portion of the sheet, I can only click in the unprotected cells. Is this an Excel thing, or am I doing something wrong when protecting? I would like to see what the formula is in the cell when protected, but I can't click on the cell, what's the deal?
View 8 Replies View RelatedIs it true that the sheet protection in excel is practicaly none ?
Not even excel 2002 (XP) prevents simple cracking software from removing sheet protection.
Are there any master keys that cause that ?
Is there a way to protect a sheet with strong encryption ?
Is there a way to password protect a sheet so that you have to enter a password just to access it. I dont want most users to be even able to view one sheet unless they have the password.
View 2 Replies View RelatedI got this form RoyUK and was wondering if anyone know why it comes up with a debug here
Option Explicit
Dim sPw As String
Dim i As Long
Dim r As Long
Dim iChk As Integer
For Each rCl In .Range(Cells(r, 3), Cells(r, Columns.Count).End(xlToLeft))
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 have an excel workbook I'm working on, and it needs to be protected. I have some groups that can be expanded or hidden, but once the sheet is protected you can no longer use the little button to hide or expand the group.
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 a piece of code with a couple of errors. The code selects a certain row and then copies and inserts this row on the last line with data, the number of rows is decided by the user via a form. The line of code with the error is highlighted, think it is a syntax/grammar error.
Code:
Sub InsertRows()
'
' Inserting Rows
ActiveSheet.Unprotect
FormRowInsert.Show
Application.ScreenUpdating = False
[code]....
I have a workbook with about 70 sheets, some are specific for data entry, others for data review.
I share the workbook with my data entry team, and lock the review sheets so that no one can modify the data on them or change the formating. End users review the final protected sheets for the information that is appropriate to them.
I need to track the status of the protection of each sheet, such that I can edit the sheets as needed, then protect / lock them once I am done.
Is there a way to indicate, in a cell within the indvidual sheets, the status of protection for that sheet?
For example, if the sheet is protected, the Cell would read "LOCKED" and if the sheet is not protected, the cell would read "EDIT".
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 have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?
View 4 Replies View RelatedI have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
I am trying to Protect a Sheet to lock the width of just one column. Does the lock apply to all the columns in the worksheet or can it apply to a single column only. I can't seem to get this to lock just one column-if it is possible.
View 1 Replies View RelatedI have tried to cobble together a macro to remove the sheet protection from every sheet in every workbook in a folder. Not surprisingly, it is not working. Unfortunately, I have reached the limits of my VBA abilities.
View 9 Replies View RelatedI have hidden columns on a sheet.
I protect the sheet with a password before I send out the file to a 3rd party. However, it seems that all a user has to do is select the sheet cells, copy and paste onto another sheet or file. The protection is nonexistant and the user is able to unhide the columns.
Am I missing something?
Is there a way to "really" protect a sheet so that a user is unable to unhide confidiential columns? Preferably a method without VBA code.
I found one of your old VBA entries that allowed me to automatically refresh an autofilter function, and it works great.... with protection off.
As soon as I protect the sheet so users can't enter data into the pages showing the autofiltered data, the autofilter kicks off.
i have set the protection to allow autofiltering, so it's something that is preventing the macro (for automatically updating/refreshing the autofilter).
I'd love to either fix the macro to "autorefresh autofilter" with protection on, or just enter another code that will disable all functionality on the worksheet. (workbook is set up with multiple sheets, first sheet is for all data entry, subsequent sheets are filtered data-trying to make them viewable only).
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?
How do I set a variable to the active sheet name in VB.
Hypothetical example here:
Dim Sht as Name
Sht = Activesheet.Name
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 Relatedhow to allow formating a text and cell in protect sheet ?
View 4 Replies View RelatedWhen turning on sheet protection, I get an error with the following code that I'm using to make sure rows adjust their height when lines wrap.
Rows.EntireRow.AutoFit
What can be done to eliminate this conflict? All of the cells I want available for editing are unlocked.
