Discussion Groups Home

Can I protect cell numbering format ($) but allow editing? in Excel General Questions

12/29/2004 8:27 AM PST

Question

	Can I protect a cell's numbering format so that the cell can be edited but the $ cannot be changed?

Was this post helpful to you?

Top

Frank Kabel

12/29/2004 9:44 AM PST

Answer

	Hi no, not possible AFAIK with Excel's protection function. -- Regards Frank Kabel Frankfurt, Germany "Deb22" <Deb22@discussions.microsoft.com> schrieb im Newsbeitrag news:DC64EFAD-1D28-44C6-A54F-DA96E286CD4C@microsoft.com... > Can I protect a cell's numbering format so that the cell can be edited but > the $ cannot be changed?

Was this post helpful to you?

Top

Deb22

12/29/2004 9:51 AM PST

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" <Deb22@discussions.microsoft.com> schrieb im Newsbeitrag
> news:DC64EFAD-1D28-44C6-A54F-DA96E286CD4C@microsoft.com...
> > Can I protect a cell's numbering format so that the cell can be edited but
> > the $ cannot be changed?
>
>
>

Was this post helpful to you?

Top

Dave Peterson

12/29/2004 10:34 AM PST

Answer

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

Was this post helpful to you?

Top

Sam Hills

8/11/2008 9:47 AM PST

	"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.

Was this post helpful to you?

Top

lily121

9/21/2009 8:21 PM PST

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.
>
>

Was this post helpful to you?

Top

David Biddulph

9/21/2009 11:41 PM PST

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" <lily121@discussions.microsoft.com> wrote in message
news:1F71B3C3-EF5F-41F3-A70C-ED48BD639B21@microsoft.com...
> 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.
>>
>>

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies