Detecting user input of "" and replacing with VBA computed default
 

I have a Cell in my workbook named "Test" Lets say I have VBA module that
involves that cell to do some calculations..as an example Increment that cell
by 1.

Sub Increment()
Test = Test + 1
End Sub

When a user doesn't know what value to input into that cell, I want to allow
him to input a blank in that cell to signal my software to create a default
responce based on other cell values. My first task is to trap the user
inputing a "" value into the cell of interest ("Test"). I did this by using
the Worksheet_Change event:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Value2 = "" Then
On Error Resume Next
m_Target.Name.Name = False 'm_Test in this case sets flag for
properties below (does not work)
End If
End Sub

Elsewhere in the workbook I have the following code to interface with my VBA
code:

Dim m_Test As Boolean
Public Property Get Test() As Double
If m_Test = False Then
Test = 12 'Default value for this cell
Else
Test = Range("Test").Value2
End If
End Property
Public Property Let Test(Value As Double)
If Value = vbNull Then
m_Test = False
Range("Test").Value2 = 12
Else
m_Test = True
Range("Test").Value2 = Value
End If
End Property

Does anybody out there have a better approach or know how to make this one
work?

Detecting user input of "" and replacing with VBA computed default
 

How do you delete this post? When I submitted it the system sent me a
message saying the system was unavailable...try again...so I did. Now there
are duplicate posts!

"MachAngle" wrote:

I have a Cell in my workbook named "Test" Lets say I have VBA module that
involves that cell to do some calculations..as an example Increment that cell
by 1.

Sub Increment()
Test = Test + 1
End Sub

When a user doesn't know what value to input into that cell, I want to allow
him to input a blank in that cell to signal my software to create a default
responce based on other cell values. My first task is to trap the user
inputing a "" value into the cell of interest ("Test"). I did this by using
the Worksheet_Change event:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Value2 = "" Then
On Error Resume Next
m_Target.Name.Name = False 'm_Test in this case sets flag for
properties below (does not work)
End If
End Sub

Elsewhere in the workbook I have the following code to interface with my VBA
code:

Dim m_Test As Boolean
Public Property Get Test() As Double
If m_Test = False Then
Test = 12 'Default value for this cell
Else
Test = Range("Test").Value2
End If
End Property
Public Property Let Test(Value As Double)
If Value = vbNull Then
m_Test = False
Range("Test").Value2 = 12
Else
m_Test = True
Range("Test").Value2 = Value
End If
End Property

Does anybody out there have a better approach or know how to make this one
work?