| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
| change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
| Change to Worksheet Change Event | Excel Programming | |||
| Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
Linear Mode
