Protect Worksheet And Data Validation
I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?
View Complete Thread with Replies
Related Forum Messages:
Password Protect: Unlock The Worksheet, Autofilter It, Print It, Unfilter It, And Password Protect The Sheet Again?
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click()
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Data Validation List From Another Worksheet
I'm using data validation to create a drop down list ina spreadsheet so that users can select from a pre-defined list. I want to be able to maintain this list in a separate worksheet, but when I try to create the list it tells me that "You may not use references to other worksheets or workbooks for Data Validation Criteria". I don't want to maintain the list in the same worksheet as there's a risk of deletion. Is there a way to create a dropdown list in a second worksheet?
Using A Cell On Another Worksheet As Input For Data Validation
Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
Reference Data Validation List On Another Worksheet
I have a spreadsheet in Excel 2003 with many sheets all of which need to have for some columns the data entry restricted to a specific list.
To do this we have created lists defined and held in a separate sheet and then attempted to reference these from all the other sheets using Data -> Validation and then entering the relevant list in the Validation criteria.
The 1st sheet we set the Data Validation up in all is fine. The 2nd sheet however I get an error pop-up stating 'You may not use references to other worksheets or workbooks for Data Validation criteria'.
Data Validation Using List In Another Worksheet - Works For All But One Sheet!
I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.
On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).
Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
Data Validation List To Select Worksheet In Macro
I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.
2003 Data Validation Doesn't Trigger Worksheet Change
I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.
Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.
This was apparently an issue in Excel '97, but supposedly fixed in '03?
Nested Data Validation: Figure A To Enforce Dual Data Validation On A Single Cell?
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
Protect Worksheet On Paste
I want to copy a protected cell from one file to another, and still preserve the protected properties. Currently, the copied cell will be unprotected.
I have a lead that I might need to override ctrl-c function to my own VB script.
How do we override ctrl-c function? What lines to put in VB file?
Protect The Worksheet With Password
I had the thought that maybe it would be possible to have a worksheet that is not protected when saved but when it is opened there would be a sub-routine under Sub_Workbook Open that would step through each sheet and assign a random password. And when the workbook is saved it is saved without passwords again.
In this way there would be nothing for a password cracker to pick at. The only flaw I see would be that the password to open the VBA module would need to be static and this would open up the programming to change and remove. But wouldn't this be effective against most users who would only know about the utilities to open the passwords and not the programming behind excel.
Protect Worksheet But Allow Some Features
I am trying to use protection in order to prevent users from only inserting a row or a column, sort, and autofilter. I don't think Userform is a good choice for this situation. I've tried using the following
The problem I'm running into is, when I protect the sheet from the main screen (allowing for everything but those listed above), then run MyMacro, the things that were previously allowed under protection are no longer allowed. Indeed, upon re-protection it prevents the user from accessing anything other then the very basics (selecting cells). How can I make it so that the protection works the same as before the macro?
Password Protect To Open Worksheet
I know of the password protect feature to open a excel file or workbook, but does anyone know if this can be done for opening a worksheet.
I want to be able to view a worksheet after a password is entered, so therefore different worksheets have different passwords.
Hide / Protect Worksheet Code
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current
Private Sub CommandButton2_Click()
fName = Application.GetSaveAsFilename
Loop Until fName <> False
Private Sub ProtectRobin()
The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.
Protect Worksheet But Allow Selection Of Locked Cells
I am fairly new to Macros and such so please bare with me. I work with workbooks that have 30-50 worksheets in them. They are fairly complex and we send them out to our agents to use and need them protected so they cant change anything. I found how I can protect all the worksheets with one macro. There is one more part that I would like to add to this macro that I cant seem to find an answer for on these forums (Yes I have spent the better part of the day looking).
When you manually go to protect a page. It asks you to type in your password for the first time and below that there are a bunch of boxes that can be checked or unchecked. I need the first box "Select locked cells" to be unchecked. It is checked by default. Is there something I can add to my macro to uncheck this box?
This is the topic I used to create my Protect/Unprotect macro.
Macros to Protect/Unprotect Worksheets and Workbooks
When Protect A Worksheet Then Only UNLOCKED Select CELLS
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
Data Validation :: Selected From 4 Validation Lists
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
Protect Original Data From Changes
I have a tracking log, stored on a network drive, that tracks open purchase orders on about 13 different buyers. Each buyer has access to this data, and would be interested in looking at it in different ways. I have an idea for protecting my original data in this workbook, but allowing others to view and edit the data anyway they want. I thought I'd run it past the pros first, to see if they knew a better way, before I started trying to code it.
My idea was to put a macro in the workbook open event that opens an input box, "Enter password or select okay." If they enter the correct password, the macro exits, and they are in the workbook. Any changes made after entering the correct password will be made to the original data. If they enter anything OTHER than the correct password, the code would save the workbook AS another name, so now any changes made would be only made to a COPY of the original data.
This way, when I do my daily updates, I can open the file using the password, and save the file after editing. Anybody else would only get to edit a copy of my original. The other question is..., is it possible to put a macro in the workbook exit event that requires a password to overwrite the original? Otherwise, the user could edit the data in the "saved as" version, then just overwrite the original file, either accidentally or maliciously. I know they could still overwrite it by editing the macro, but I don't think they're that savvy.
Data List / Protect Sheet
I'd like to protect Excel Worsheet and use the function from Menu Data -> List. The problem is that when I protect the Worksheet the option for new row is not appearing. Although I select all CheckBoxes and allow Sort, Insert Row, Insert Column, Select locked cells, Select unlocked cells etc...
p.s. Excel 2003
Use Vba To Query Data From A Password Protect Access Database
I have an excel file that imports data from an access database. Now the user wants the database password protected I edited my code to include the line "user ID=admin;password=risk;" but I'm getting an error message stating: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
I'm bothered because I did not create a workgroup information file. I just want a password on the database I did not create users and groups.
Note this code works fine without the password protection and the added line.
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C: est.mdb;" & _
szSQL = "SELECT * from data"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Data Validation List: Ignore Blanks Between Data
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:
My validation list will return those names but will also give me the blanks in between the names.
Is there any way to ignore the blanks?
Allow User To Automatically Add Data To Data Validation List
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
Data Validation List With Changing Data
I would like to utilize data validation to create a drop down list from a list of 100 rows that changes monthly. Currently the list is set to draw data from A1:A100, however depending on the month, different cells will have data in them. What I don't like is currently the blank cells create gaps in the data validation drop down list and make it longer than it needs to be. Is there any way for excel to automatically just display cells with data in them in the drop down?
Data Validation :: Limit The Data Available On The List
have created a list obx using data validation. My question is, is there a way to limit the data available on the list.
Example: sheet 1
A1 A2 A3
address s Date
has data validation list that consists of all of A1 a named range called "address". Everything works great.
I now want to be able to limit the available choices in the list depending on what cell the user is in
example: if user is in sheet2 a1, i only want them to be able to pick address that have an "A" or "p" in cell A2 on sheet 1
I tried to acomplish this using conditional formatting, I format my named range with different colors, but the colors do not carry over to the data validation list on sheet2.
Data Validation Multiple Columns Data
about data validation drop downs.
I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.
Column A Column B Column C
1 Name 1 Skill 1 Phone #
This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)
Column D Column E Column F
1 Name 1 7 555-1111
2 Name 2 5 555-2222
3 Name 3 3 555-3333
4 Name 4 4 555-4444
So when I select say name 3...
Column A Column B Column C
1 Name 3 3 555-3333
Creating A Data Form With Data Validation
I have limited ability with Excel, and I'm trying to create a database using the Data/Form menu choices. My problem is I only want specific data in two of the 20 or so fields. When I use Data/ Validation and direct data entry, I get exactly what I want, EXCEPT that the choices don't come up when I use the Data/Form. Users can enter anything they want, which defeats the purpose. I tried to build a user form to use for data entry, but I don't know how to get it to add items to a database or lookup items like the data/form process does. I also want to add a few macro buttons to the form to do other things.
I am pretty sure of a few things: 1) This should be pretty simple. Anyone knowing VBA would probably know how to do this in minutes. I found some code that did a simliar function, but couldn't interpret it to fit my needs, which brings me to 2) I'll never use VBA or complicated macros again. I don't have the need for them. This is just an isolated incident. Therefore, signing up for a class would be useless to me. I tried to locate some advanced Excel/VBA classes, hoping to find an instructor or even a student who would welcome a simple challenge, but there are none available.
Merits Of VBA Protect Vs Non-Protect
I would like to protect the intellectual property of an application and have read differing opinions of the merits of protecting VBA code. What are the opinions of the experts on this forum? Are the password breaker programs so advanced that protecting code is a waste of time and only a hinderance to debugging and troubleshooting?
I am using MS Excel 2003 (11.5612) on XP.
I apologize if this topic has already been covered on this forum. I searched but did not find exactly what I was looking for. I am happy to review an old post if one exists.
Data Validation ..
I want to add a validation list to a spread sheet using a named ranged. Easy enough so far, but if the range only has two values which are repeated several (hundred) times - Yes & No - how can i stop the value appearing on the list more than once?
I'm open to other suggestions with this such as using list boxes etc, just so long as i can get around it without having to maintain a seperate spreadsheet of the values i would like to appear in my list.
Is Data Validation The Way To Go
I have 2 sheets in a workbook. The first sheet is 'working loads' and the second is 'completed loads'. I enter data into the 'working loads' sheet. When that row of data is completed, I mark a x into the 'completed' column, when I put the 'x' into that column it sends that row of data over to the 'completed loads' sheet.
This is my problem, when I enter data into the 'working loads' sheet into the 'Bill to" column, because it is not restricted it can be entered any way the user decides to enter it. When it goes to the 'completed' sheet, the 'bill to' is entered in 2 or 3 ways depending on how the user entered it in the beginning on the 'working loads' sheet.
I want the user to be restricted on how the data is entered on the 'working loads' sheet so that I don't end up with the 2 or 3 ways it was entered for the same client on the 'completed loads' sheet. I also need to add to the the list if it has not been used in the 'bill to' info as we get new clients weekly.
I currently am physically changing the 'bill to' info on the 'completed loads' sheet manually so that the client is entered only once, but as the year goes on the list grows as I might have 5000 rows of data with the same client entered 2 or 3 different ways.
Data Validation With Filter
I have two tabs in one spreadsheet and I want to have the results chosen in one tab to filter in the other tab. So based on my drop down box result chosen in the first tab, I want the data in the other tab automatically filter only the result that was chosen.
For example, if I have the word "apple" in the drop down then I want all my data in the second tab to filter by "apple" in column B.
Is this something that would be possible?
I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.
Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702".
List Data Validation
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..
On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.
Hope what i did so far is understandable ^.^
Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.
I attached a screen of the inventory sheet so that you can see how it is structured.