Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting user cancel out of "File Open" input box | Excel Programming | |||
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? | Excel Programming | |||
Wkst2 w/all ABSdata computed from wkst1-can't "Find" data pt in#2 | Excel Discussion (Misc queries) | |||
Wkst2 w/all ABSdata computed from wkst1-can't "Find" data pt in#2 | Excel Discussion (Misc queries) | |||
Creating Subdirectory in "Default User" App Data | Excel Programming |