Protecting Selected Cells and Functions

I have a worksheet. In Cell B2 is a Data validation box Listing a range
of colleagues names( DRop Down Menu). On selection of a name in B2, the
contents of the whole worksheet changes.

I like to Protect the worksheet for:

1) Hiding the formulaes
2) And most importantly preventing editing of the contents of any other
cell (except B2).

and yet be permiitted to:

3) Select contents in Cell B2  (Data Validation Box)
4) Select Auto filters in Row 4

I've tried using the the Tools/ Protect worksheet menu, ticking Select
Lock Cells, Select Unlock cell, use auto filters. And in in
Format/Cells/Protection, Tick in Lock and Hedden boxes.

All the above work except No 3. I am unable to make a selction from the
drop down menu in Cell B2.

Please Any suggestions.

If it is to be done in Macro Please provide me a detailed Idiot Guide
as I have NEVER PERFORMED/USED a Macro, and would not know where to
start.

THX
Gunjani

0
gunjani786 (11)
3/22/2006 11:53:02 AM
excel 39879 articles. 2 followers. Follow

1 Replies
316 Views

Similar Articles

[PageSpeed] 23

Try unlocking B2 (Format|cells|protection tab)

Gunjani wrote:
> 
> I have a worksheet. In Cell B2 is a Data validation box Listing a range
> of colleagues names( DRop Down Menu). On selection of a name in B2, the
> contents of the whole worksheet changes.
> 
> I like to Protect the worksheet for:
> 
> 1) Hiding the formulaes
> 2) And most importantly preventing editing of the contents of any other
> cell (except B2).
> 
> and yet be permiitted to:
> 
> 3) Select contents in Cell B2  (Data Validation Box)
> 4) Select Auto filters in Row 4
> 
> I've tried using the the Tools/ Protect worksheet menu, ticking Select
> Lock Cells, Select Unlock cell, use auto filters. And in in
> Format/Cells/Protection, Tick in Lock and Hedden boxes.
> 
> All the above work except No 3. I am unable to make a selction from the
> drop down menu in Cell B2.
> 
> Please Any suggestions.
> 
> If it is to be done in Macro Please provide me a detailed Idiot Guide
> as I have NEVER PERFORMED/USED a Macro, and would not know where to
> start.
> 
> THX
> Gunjani

-- 

Dave Peterson
0
petersod (12004)
3/22/2006 12:07:09 PM
Reply:

Similar Artilces:

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

linking cells in a column (result) to cells in a row (source)
MS XP Pro / Excel 2007: Good day all. I have data in two consecutive rows. The 1st one is text and the second contains formulae which results in values. (Call these the "source") On another sheet in the same workbook I have two columns (Call these the destination) which need to be linked to the source rows if value (other than zero) is present in the 2nd source row. There could be zero value or blank cells within the source rows. I need code to sequentially seek values (other than zero) in the cells in the 2nd row of the source and when found, the next available cell ...

Subquery select field from previous record
I have the following table setup Table Name - tblInventoryCounts Field Names - Store Number, Count Date, Item name, Count Amount Sample Data Store Number Count date Item Name Count Amount 9 1/4/2010 Gear 10 9 1/11/2010 Gear 18 9 1/18/2010 Gear 18 I have a parameter query where the user is asked for the store number and Count Date. I need for the query to return the information for the date entered and the Count Amount from 7 ...

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

Opening revision mode when selection is locked?
Was sent my manuscript text by the publisher to revise, but my program is in Read-only compatability mode. Can't access Revision Mode. "Selection is locked." Updated to Microsoft Office, but still can't access. Am hanging 300 feet up by a small root, largely incomputerate, but desperate to be saved, if there are caring souls out there. Thank you! On 31/05/2010 3:17 AM, SM NONA wrote: > Was sent my manuscript text by the publisher to revise, but my program is in > Read-only compatability mode. Can't access Revision Mode. "Selection is > locked.&...

Move selection after Enter Direction
I have a macro that enables me to toggle between moving the selection down, or to the right, after Enter. Sub MoveAfterReturn() If Application.MoveAfterReturnDirection = xlDown Then Application.MoveAfterReturnDirection = xlToRight Else Application.MoveAfterReturnDirection = xlDown End If Application.ScreenUpdating = True End Sub This works good, but i would like to automate the application of the macro in a particaular workbook by having it move "right" if the active cell is in the range E4:M34. If not in that range, it would move "Down". How can i accomplish this...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

pivot table empty cell
I need to have an empty cell be in the pivot table. I have multiple pivot tables I have generated for a report. I have created a worksheet that reads the data from the pivot table (so it looks nicer). I have tried to check the box in options that says to put a 0 or another symbol (*) in an empty cell and it doesn't work. I want people to be able to input their own data and refresh the pivot table. If I have four response options (sometimes, always, never, usually) and the new data does not have data for one of the options then the table that reads the data gets messed up with its numb...

Cell ref is it possibile to calculate / change this in a formula
Any SUPER USERS uot there? I am trying to construct a formula where I need to copy it down 6500+ rows but I need one of the cell refs to be calculated in sted of having its row number to raise by one for each row copied. sample, Normel coping: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26) What I am trying to get: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16) For every line the for...

Adding cells with a particular comment
Hi all, is there any simple to add all cells in a row which have a particular comment without using macro. pls help. Regards, NC HI, Use the SumIF function -- anurag. ----------------------------------------------------------------------- anurag.d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=895 View this thread: http://www.excelforum.com/showthread.php?threadid=31476 well i can use it for particular values but how to use it with comments. My problem is a particular comment for e.g "reject" or "accept" ia added to that cell, & ...

limit no of characters in cell
How can I limit the number of charaters (including spaces) in a cell to 48? Additionally, I'm not sure if this is possible, say it applied to cell A1, once 48 characters are reached, it will then automatically drop down to the cell below to A2 and do the same again. thanks -- rocket0612 ------------------------------------------------------------------------ rocket0612's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19492 View this thread: http://www.excelforum.com/showthread.php?threadid=375572 Try validation from the Data menu, and select text length...

select for last record for each customer
I have table with many recodrs as this table: customerID Date Stock1 Stock2 Stock3 Stock4 112105222 22/01/2006 5 6 2 1 112105222 23/05/2006 4 8 6 7 112105222 01/02/2007 6 9 4 6 112101024 12/12/2007 7 8 9 1 112101024 15/02/2007 7 9 2 1 . . . . . . by select qury I want show just the last record for each customerID as...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

shortcut key for language selection doesnt work only in Excel
Machine Info: Toshiba laptop, satellite 1905-277, Pentium 4 CPU 1.60 GHz 256 MB of RAM OS: Windows XP Home Edition with SP1 When I am trying to shift from "EN" language to "FA" language in text service and input language, usually I do this by short cut keys (right Alt+hift and left Alt+shift). This shortcut keys doesnt operate in Excel but in other office family like Word and Access has no problem. I have to select languages manually from its box. Interestingly it doesnt elected in first time and I have to select the required language two times. TIA Rasoul Khoshravan Azar...

CANNOT PROTECT
Trying to password and read only protect a Microsoft Word docuement that is embedded in Excel. When I open the "protected" document, the read-only prompt does not appear...and I can still make changes and close the docuement. HELP! Try this: create the document directly in word, protect it and save it to disk. Then copy the text you want and past it special as an OLE object in excel Peter ...

Spreadsheet password protection
How do you password protect a spreadsheet. I can't find anything in the help section. You can prevent un authorised access by giving a password to open the workbook itself, so that no one else can open it (Go to Tools, Options and then Security Tab). You can do it there if you have already saved the file, or you can go to "save as" then "Tools" and "General Options" where you can give password. On the other hand you can protect work sheets and ranges for different people by using "Tools" and then "Protection" Tab -- M Imran Buha...

How to protect cells?
I would like to protect a range of cells (A2:D24) with a password for a group of users that would make changes to cells in this range. Then on the same sheet, also protect a range of cells (E2:E24) from everyone but me. Is this possible? Thanks. Mark, Please don't multipost. See the thread in .programming. John "Mark F." <m7829@yahoo.com> wrote in message news:FSzQb.10797$6o4.894@fe2.texas.rr.com... > I would like to protect a range of cells (A2:D24) with a password for a > group of users that would make changes to cells in this range. Then on > the same she...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

Auto Row Selection of ListView
How do I automatically highlight the first row in a list view on it being built? >How do I automatically highlight the first row in a list view on it being >built? William, Have you tried using something like: list_ctrl.SetItemState( 0, 0, LVIS_SELECTED|LVIS_FOCUSED); Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq Yes but that doesn't work. I want the first row in the list view to be automatically highlighted. "David Lowndes" <davidl@mvps.org> wrote in message news:967tmvsue34sr937h7vkuecd0i171u073m@4ax.com... > >How do I automatically highlight th...

Saving only updated cells or worksheets
(This is the first time I'm using this feature - hopefully I'm following the proper procedure!) Is there a way, in Excel 2002, to only save the cells or worksheets that have been updated, instead of saving the entire file each time? I have a large file, with VBA code, and is I could set it to only save the updated cells or worksheets, this would decrease the time needed to save the file. I read about Tools, Options and Allow Fast Saves, but when I follow that path, there is no such option to Allow Fast Saves. Any assistance would be greatly appreciated! Thank You, MWS I am ...

Bar chart fill color selection from reference to cell data
Is there a way to create a bar chart and have the colors be selected from data adjacent to the plotted data? Or is there a macro that can select cells which can change the color of the fill/texture? Darryl - You can set up formulas that split your data into columns based on the value in a reference column, then plot different series, each formatted to differentiate them from each other. I have a couple of conditional charting examples linked to this page: http://www.geocities.com/jonpeltier/Excel/Charts/format.html My examples use the values as the reference that splits the data ...

How to Activate "Show all" in a Protected Shared Workbook
Hi, I created a protected and shared workbook in Excel 2003 ,the default Filter Option is "Auto Filter" how can I use the "Show All" in Filtering.? even I unchecked the "Filter Settings" in "Tools-> Share Workbook -> Advanced -> Include Personal View " but it did not work. Thanks. You can't. If the workbook were not shared, then you could create your own macro that unprotects the worksheet, does the showall and reprotects the worksheet. But with the workbook shared, you can't change the protection of a shee...

Selecting every 3rd row ???
I have multiple consecutive rows that get information from every third row earlier in the spread sheet, is there a way to reference my 2nd row to the first row in a formula. Example: I have information in Column A Rows 1,4,7,10,13, ect...... I want row 300 to look @ A - Row 1, 301 @ 4, 302 @ 7, ect.... In A300 (=A1) In A301 can I add a function referencing (=A1+3) in any way so that it would add 3 to the reference cell A301 looks at to give the value of (=A4). Try... A300, copied down: =OFFSET($A$1,(ROW()-ROW($A$300)+1)*3-3,0) Hope this helps! In article <113k3vgmrcgsoec@corp...