worksheet change
 

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

worksheet change
 

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

worksheet change
 

Hi Jim

I have a syntax error in the following line of code

if target.value < '' then .Undo

I tried using double quotes and single quotes, neither corrected the error.

Do you have any suggestion on how I can correct this error?

Thanks for any help

"Jim Thomlinson" wrote:

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

worksheet change
 

This didn't work:

if target.value < "" then .Undo



ram wrote:

Hi Jim

I have a syntax error in the following line of code

if target.value < '' then .Undo

I tried using double quotes and single quotes, neither corrected the error.

Do you have any suggestion on how I can correct this error?

Thanks for any help

"Jim Thomlinson" wrote:

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

--

Dave Peterson

worksheet change
 

Hi Dave,

When I use "" I still can't enter data in a cell that is blank, it will
undo the entry and the cell will remain blank

Thanks for any help

"Dave Peterson" wrote:

This didn't work:

if target.value < "" then .Undo



ram wrote:

Hi Jim

I have a syntax error in the following line of code

if target.value < '' then .Undo

I tried using double quotes and single quotes, neither corrected the error.

Do you have any suggestion on how I can correct this error?

Thanks for any help

"Jim Thomlinson" wrote:

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

--

Dave Peterson

worksheet change
 

Maybe...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

This allows you to clear a cell.

Remember target.value is what's there after the change--not before.



ram wrote:

Hi Dave,

When I use "" I still can't enter data in a cell that is blank, it will
undo the entry and the cell will remain blank

Thanks for any help

"Dave Peterson" wrote:

This didn't work:

if target.value < "" then .Undo



ram wrote:

Hi Jim

I have a syntax error in the following line of code

if target.value < '' then .Undo

I tried using double quotes and single quotes, neither corrected the error.

Do you have any suggestion on how I can correct this error?

Thanks for any help

"Jim Thomlinson" wrote:

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

--

Dave Peterson


--

Dave Peterson

worksheet change
 

The following code answered my question, Thanks to all

Dim Oldvalue As Variant


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Oldvalue = Target(1).Formula

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 1 And Oldvalue < "" Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
End With
End If
End Sub

"ram" wrote:

Hi Dave,

When I use "" I still can't enter data in a cell that is blank, it will
undo the entry and the cell will remain blank

Thanks for any help

"Dave Peterson" wrote:

This didn't work:

if target.value < "" then .Undo



ram wrote:

Hi Jim

I have a syntax error in the following line of code

if target.value < '' then .Undo

I tried using double quotes and single quotes, neither corrected the error.

Do you have any suggestion on how I can correct this error?

Thanks for any help

"Jim Thomlinson" wrote:

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
with Application
.EnableEvents = False
if target.value < '' then .Undo
.EnableEvents = True
.Speech.Speak "Locked Cell"
end with
End If
End Sub

One thing to keep in mind is that this code will not execute if the user
pastes into this column...
--
HTH...

Jim Thomlinson


"ram" wrote:

I have the following code to protect cells in a shared workbook. What I'm
trying to do is tun off the undo if the cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.Speech.Speak "Locked Cell"
End If
End Sub

Thanks for any help.

Thanks for all the help today

--

Dave Peterson