Possible to make certain cells default to 0?
 

Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.

I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.

Any hints?

Possible to make certain cells default to 0?
 

The following Vba code copied and pasted into the sheet module should
do what you want. I assume you are using the same Excel version that I am.
Alter the range designation..."J1:N25" (two places) to reflect your specific area.
'----
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipTown
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("J1:N25")) Is Nothing Then
Me.Range("J1:N25").SpecialCells(xlCellTypeBlanks). Value = 0
End If
SkipTown:
Application.EnableEvents = True
End Sub
'----
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.
I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.
Any hints?

Possible to make certain cells default to 0?
 

Lock the cells you don't want data entered into then protect the worksheet.


Gord Dibben MS Excel MVP

On Wed, 20 Feb 2008 08:26:04 -0800, ktoth04
wrote:

Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.

I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.

Any hints?

Possible to make certain cells default to 0?
 

It's not that I don't want data entered, its that once they enter the data,
and then delete it, I'd like it to be reset to 0 instead of a blank cell.

"Gord Dibben" wrote:

Lock the cells you don't want data entered into then protect the worksheet.


Gord Dibben MS Excel MVP

On Wed, 20 Feb 2008 08:26:04 -0800, ktoth04
wrote:

Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.

I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.

Any hints?

Possible to make certain cells default to 0?
 

Can I ask a really silly question? What is the sheet module and how do I
access it? Sorry, I don't usually do things this complicated, so I've never
had to access it before...

"Jim Cone" wrote:


The following Vba code copied and pasted into the sheet module should
do what you want. I assume you are using the same Excel version that I am.
Alter the range designation..."J1:N25" (two places) to reflect your specific area.
'----
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipTown
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("J1:N25")) Is Nothing Then
Me.Range("J1:N25").SpecialCells(xlCellTypeBlanks). Value = 0
End If
SkipTown:
Application.EnableEvents = True
End Sub
'----
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.
I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.
Any hints?

Possible to make certain cells default to 0?
 

Right-click the worksheet tab, choose "View Code".
Paste the code into the big window on the right side.
On the menu bar click Debug | Compile
On the menu bar click File | Close and Return to Microsoft Excel
The code should now work automatically in the specified range.
Note: you will get a "enable macros?" notice each time the workbook is opened.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Can I ask a really silly question? What is the sheet module and how do I
access it? Sorry, I don't usually do things this complicated, so I've never
had to access it before...

"Jim Cone" wrote:
The following Vba code copied and pasted into the sheet module should
do what you want. I assume you are using the same Excel version that I am.
Alter the range designation..."J1:N25" (two places) to reflect your specific area.
'----
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipTown
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("J1:N25")) Is Nothing Then
Me.Range("J1:N25").SpecialCells(xlCellTypeBlanks). Value = 0
End If
SkipTown:
Application.EnableEvents = True
End Sub
'----
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.
I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.
Any hints?

Possible to make certain cells default to 0?
 

Thank you so much!

"Jim Cone" wrote:


Right-click the worksheet tab, choose "View Code".
Paste the code into the big window on the right side.
On the menu bar click Debug | Compile
On the menu bar click File | Close and Return to Microsoft Excel
The code should now work automatically in the specified range.
Note: you will get a "enable macros?" notice each time the workbook is opened.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Can I ask a really silly question? What is the sheet module and how do I
access it? Sorry, I don't usually do things this complicated, so I've never
had to access it before...

"Jim Cone" wrote:
The following Vba code copied and pasted into the sheet module should
do what you want. I assume you are using the same Excel version that I am.
Alter the range designation..."J1:N25" (two places) to reflect your specific area.
'----
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipTown
Application.EnableEvents = False
If Not Application.Intersect(Target, Me.Range("J1:N25")) Is Nothing Then
Me.Range("J1:N25").SpecialCells(xlCellTypeBlanks). Value = 0
End If
SkipTown:
Application.EnableEvents = True
End Sub
'----
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ktoth04"
wrote in message
Hi, so I have a spreadsheet that I use as a timecard, and what I'd like to
do is the following.
I already have data entered in these cells as 0 so that when an employee
does not edit the field, the value is 0. My problem comes when an employee
enters the value in the wrong field. Say an employee means to enter 7 hours
in field N14. He accidentally enters the data in N13, and then deletes the
value, and enters it in N14. However, now instead of 0 in cell N13, there is
no value. What I would like to happen is to have excel automatically make
cells within a specified range default to 0 if they're data is deleted.
Any hints?