|
Returning Cell Value if someone deletes the contents of a cell
I am trying to control someone blanking out a cell value in a worksheet
The person is able to enter/change the value in the cell I have been able to control the cell with Data Validation so that the cell does have a number in it with a =ISNUMBER(B10) on a custom entry. But I want to stop the cell been blank out, if it does the old cell value is returned there. Something on the lines of If Range("B10") = Blank then msgbox ("You are not allowed to leave the cell blank") Range ("B10") = OldCellValue Else EndIF Hope this is clear this test will only take place if the cell value of B10 is cleared TIA mmc308 |
Returning Cell Value if someone deletes the contents of a cell
Uncheck the Ignore Blank box.
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... I am trying to control someone blanking out a cell value in a worksheet The person is able to enter/change the value in the cell I have been able to control the cell with Data Validation so that the cell does have a number in it with a =ISNUMBER(B10) on a custom entry. But I want to stop the cell been blank out, if it does the old cell value is returned there. Something on the lines of If Range("B10") = Blank then msgbox ("You are not allowed to leave the cell blank") Range ("B10") = OldCellValue Else EndIF Hope this is clear this test will only take place if the cell value of B10 is cleared TIA mmc308 |
Returning Cell Value if someone deletes the contents of a cell
Bob,
Thanks for your responce This made no difference :( I have controlled the worksheet from having text been entered with the =ISNUMBER(B10) on a custum Validation rule However I have tried the following and sure it just needs tweaking somewhere !!!! Private Sub Worksheet_Change(ByVal Target As Range) If Range("B10") < Len("") Then MsgBox ("Your not allowed to Clear the Cell It will be returned to it's Old Value "), vbDefaultButton2, "I'll cover up your Mistake" Application.Undo Else End If End Sub All works fine until, however when I enter a value of 0.00, it is invoking the first part of my If and I would like to maybe put in a 0.00 TIA "Bob Phillips" wrote in message ... Uncheck the Ignore Blank box. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... I am trying to control someone blanking out a cell value in a worksheet The person is able to enter/change the value in the cell I have been able to control the cell with Data Validation so that the cell does have a number in it with a =ISNUMBER(B10) on a custom entry. But I want to stop the cell been blank out, if it does the old cell value is returned there. Something on the lines of If Range("B10") = Blank then msgbox ("You are not allowed to leave the cell blank") Range ("B10") = OldCellValue Else EndIF Hope this is clear this test will only take place if the cell value of B10 is cleared TIA mmc308 |
Returning Cell Value if someone deletes the contents of a cell
This is the code you are trying to write
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$10" Then If Target.Value = "" Then MsgBox "Your not allowed to Clear the Cell" & vbNewLine & _ "It will be returned to it's Old Value ", _ vbDefaultButton2, "I'll cover up your Mistake" Application.Undo End If End If End Sub but the Ignore Blank should work. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... Bob, Thanks for your responce This made no difference :( I have controlled the worksheet from having text been entered with the =ISNUMBER(B10) on a custum Validation rule However I have tried the following and sure it just needs tweaking somewhere !!!! Private Sub Worksheet_Change(ByVal Target As Range) If Range("B10") < Len("") Then MsgBox ("Your not allowed to Clear the Cell It will be returned to it's Old Value "), vbDefaultButton2, "I'll cover up your Mistake" Application.Undo Else End If End Sub All works fine until, however when I enter a value of 0.00, it is invoking the first part of my If and I would like to maybe put in a 0.00 TIA "Bob Phillips" wrote in message ... Uncheck the Ignore Blank box. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... I am trying to control someone blanking out a cell value in a worksheet The person is able to enter/change the value in the cell I have been able to control the cell with Data Validation so that the cell does have a number in it with a =ISNUMBER(B10) on a custom entry. But I want to stop the cell been blank out, if it does the old cell value is returned there. Something on the lines of If Range("B10") = Blank then msgbox ("You are not allowed to leave the cell blank") Range ("B10") = OldCellValue Else EndIF Hope this is clear this test will only take place if the cell value of B10 is cleared TIA mmc308 |
Returning Cell Value if someone deletes the contents of a cell
It the business Many Thanks Michael "Bob Phillips" wrote in message ... This is the code you are trying to write Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$10" Then If Target.Value = "" Then MsgBox "Your not allowed to Clear the Cell" & vbNewLine & _ "It will be returned to it's Old Value ", _ vbDefaultButton2, "I'll cover up your Mistake" Application.Undo End If End If End Sub but the Ignore Blank should work. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... Bob, Thanks for your responce This made no difference :( I have controlled the worksheet from having text been entered with the =ISNUMBER(B10) on a custum Validation rule However I have tried the following and sure it just needs tweaking somewhere !!!! Private Sub Worksheet_Change(ByVal Target As Range) If Range("B10") < Len("") Then MsgBox ("Your not allowed to Clear the Cell It will be returned to it's Old Value "), vbDefaultButton2, "I'll cover up your Mistake" Application.Undo Else End If End Sub All works fine until, however when I enter a value of 0.00, it is invoking the first part of my If and I would like to maybe put in a 0.00 TIA "Bob Phillips" wrote in message ... Uncheck the Ignore Blank box. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mmc308" wrote in message ... I am trying to control someone blanking out a cell value in a worksheet The person is able to enter/change the value in the cell I have been able to control the cell with Data Validation so that the cell does have a number in it with a =ISNUMBER(B10) on a custom entry. But I want to stop the cell been blank out, if it does the old cell value is returned there. Something on the lines of If Range("B10") = Blank then msgbox ("You are not allowed to leave the cell blank") Range ("B10") = OldCellValue Else EndIF Hope this is clear this test will only take place if the cell value of B10 is cleared TIA mmc308 |
| All times are GMT +1. The time now is 07:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com