Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I protect cell numbering format ($) but allow editing?
Can I protect a cell's numbering format so that the cell can be edited but
the $ cannot be changed? |
#2
|
|||
|
|||
Hi
no, not possible AFAIK with Excel's protection function. -- Regards Frank Kabel Frankfurt, Germany "Deb22" schrieb im Newsbeitrag ... Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? |
#3
|
|||
|
|||
Thank you for your answer.
I spent two years in Aschaffenbury, Germany with the U.S. Army in the early 70's. I loved Germany. You have a beautiful country. Take care, Debbie "Frank Kabel" wrote: Hi no, not possible AFAIK with Excel's protection function. -- Regards Frank Kabel Frankfurt, Germany "Deb22" schrieb im Newsbeitrag ... Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? |
#4
|
|||
|
|||
Maybe you could just change the format back to what you want after the change:
If that sounds ok, then rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window. I used all of column A, but you could limit it to whatever range you want. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myFormat As String myFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Target, Me.Range("a:a")) On Error GoTo 0 If myRng Is Nothing Then Exit Sub End If myRng.NumberFormat = myFormat End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Deb22 wrote: Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect cell numbering format ($) but allow editing?
"Deb22" wrote: Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? I had a similar problem -- I could protect a column so the format could not be changed, but if the user pasted a value from another cell, the formatting changed. Microsoft has acknowledged that this is a bug in Excel and I was the first to report it. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect cell numbering format ($) but allow editing?
HOw can i protect the format of a column?
I would liek to fix the format of a date column so that user must enter date in a certain format THanks "Sam Hills" wrote: "Deb22" wrote: Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? I had a similar problem -- I could protect a column so the format could not be changed, but if the user pasted a value from another cell, the formatting changed. Microsoft has acknowledged that this is a bug in Excel and I was the first to report it. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I protect cell numbering format ($) but allow editing?
I think that you may be confused about what a cell format does. The format
affects only the DISPLAY of a number in a column. It does NOT affect how a number being inserted is interpreted. The interpretation of dates being entered is governed by Windows Regional Options (in Control Panel). Data Validation would allow you to constrain a cell's input value to a number (not text) or even to a number within in specified range, but it couldn't stop someone as entering a number as mm/dd/yyyy when you want it entering as dd/mm/yyyy. You may be better including a text instruction telling users to insert dates in an appropriate unambiguous format (perhaps as dd mmm yyyy), and then using data validation to check that a valid number is the result. -- David Biddulph "lily121" wrote in message ... HOw can i protect the format of a column? I would liek to fix the format of a date column so that user must enter date in a certain format THanks "Sam Hills" wrote: "Deb22" wrote: Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed? I had a similar problem -- I could protect a column so the format could not be changed, but if the user pasted a value from another cell, the formatting changed. Microsoft has acknowledged that this is a bug in Excel and I was the first to report it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I format a cell to blink | Excel Discussion (Misc queries) | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) | |||
prevent 4/5 in a cell automatically transfer to a date format | Excel Discussion (Misc queries) | |||
Date on two lines using a custom cell format possible? | Excel Discussion (Misc queries) | |||
protect a cell | Excel Discussion (Misc queries) |