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
521 Views

Similar Articles

[PageSpeed] 49

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 (12005)
3/22/2006 12:07:09 PM
Reply:

Similar Artilces:

Insertion point in selected text
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I had to reinstall Office 2008 and since then, Word is behaving differently when I try to click inside a portion of text that is selected. Instead of dismissing the selection and giving me an insertion point, the selection background briefly blinks and stays there, and a smart tag appears. I have to click outside the selected text and then put the insertion point where I want it.� <br><br>What is even more intriguing is if I happen to be tracking the changes, I can see that Word does something when I...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

Remove file Protection from an Excel workbook file from others
I have an older excel file created by someone who is no longer with my company. I want to use that file as a starting point to created another file. The file is set up as "read only". If I try to change any of the cells, I get the message that the "cell or chart is protected and therefore read only" and further tells me to unprotect before attempting the change. When I got to tools/protection/Unprotect Sheet I get the request for a password. I don't know the password as the file was created by another. I have tried "save as", from tools making sur...

Can't send message when selecting frm contact list
Hi, I ahve soe a couple of weeks now been having problems sending emails. They go into my out box but do not ever get sent, eve when forcing a send and receive. I have finaly noticed a pattern When I create an email and type a name into teh To field and press Ctrl+K to resolve the name the email will not send. When U double click on teh address and copy and paste just the email adress into the to field teh mails sends OK. It would appear that my contacts list that I have managed to keep migrating to new computers for almost 8 yeas now has managed to get corrupt. I seem to be able to ...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Function doesn't run
In my spreadsheet, I have the following function =VLookup(K16, zips, 2) However, instead of returning a result, the function remains in the cell. How do I fix this problem? Format the cell as General and re-enter the formula (F2, ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Justin" <jmeyer@incrementaladvantage.com> wrote in message news:1165596899.059148.31580@80g2000cwy.googlegroups.com... | In my spreadsheet, I have the following function | =VLookup(K16, zips, 2) | However, instead of returning a result, the function remains in the | cell. How do I fix th...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Show only free IP addresses when selecting address to certain device?
There is one-to-one relationship between IP table and Device table. IP table has ID (primary key) and four ip fields. Device table has ipID field (foreign key) and other fields which are not relevant to this matter. The probles is that in the device form the ip dropdown now only shows the reserved IPs, not the free IPs from IP table. The query in dropdown is as follows: SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP- number], tblDevice.ipID FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID...

Uncheck "always use the selected program to open this kind of file" by default
http://www.vistax64.com/newreply.php?do=newreply&p=1136177 Andrew;1136177 Wrote: > If I right click on a file there is usually the option to"Open with..". > Selecting this option list possible programs with which to open the > application and, below this, there is a check box labelled "always use > the > selected program to open this kind of file". The check box is always > checked, so clicking on OK permanently associates the selected program > with > the file type in question. > > However, I usually use this option to op...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Today Function
how is this function called in the code? I want to use in in an update query that is coded to a button. thanks Hey Dave, I hope I'm understanding what your asking for but I think this is what you are looking for: Today() HTH, Shane Dave wrote: >how is this function called in the code? >I want to use in in an update query that is coded to a button. > >thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 Well I think it is either Today() or Date() not sure which and not sure how to use it in the code. I ...

how can I asign printscreen to a function key or key combination?
My key board has no printscreen key. What can I do? That seems unlikely, but it is hardly a Word issue. The PrtScn button is usually a dual function button somewhere on the top row of your keyboard. If it is not then you need to ask the keyboard manufacturer. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< ...

Autofilter on protected work book?
Autofilter works on a protected workbook, but not when it is a line in a macro, e.g.: Selection.AutoFilter Field:=1, Criteria1:="YES" It cause a macro error. This is true even if I enable 'Allow all user of the workbook to Use Autofilter'. I'm sure I've done this before and it worked, but not now. Does anyone know if this is a bug in 2003? Or a way around it? Or another way of selecting a number of rows by a single criterion (in a macro)? Good morning Muppet Does this article help? http://www.contextures.com/xlautofilter03.html#Protect HTH DominicB -- ...