M
mikebres
After much searching I found a solution to a common question. How can I have
a tooltip on my worksheet for each cell. This method uses the Validation
Object. I created the UDF on the worksheet code section using the Selection
Change event. My goal was to click in a cell that had an induction date,
then show a calculated Expected Delivery date using the date under the cursor
plus an standard number of days it would take to travel to a destination.
The number of days, named SS here, was a couple of cells over on the same
worksheet. The EDD function takes into account weekends and holidays to give
me my result. I found that the .delete was necessary to prevent errors when
a cell that was previously used was selected.
So here is my solution...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim InDate As Date, SS As Integer, ExpDel As Date
If Not Intersect(Target, Range("D
")) Is Nothing Then
InDate = DateValue(Trim(Target.Value))
SS = Target.Offset(0, 3).Value
ExpDel = EDD(InDate, SS)
With Target.Validation
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.IgnoreBlank = True
.InputTitle = "Expected Delivery:"
.InputMessage = Format(ExpDel, "mm/dd/yy ddd")
.Delete
End With
End If
End Sub
a tooltip on my worksheet for each cell. This method uses the Validation
Object. I created the UDF on the worksheet code section using the Selection
Change event. My goal was to click in a cell that had an induction date,
then show a calculated Expected Delivery date using the date under the cursor
plus an standard number of days it would take to travel to a destination.
The number of days, named SS here, was a couple of cells over on the same
worksheet. The EDD function takes into account weekends and holidays to give
me my result. I found that the .delete was necessary to prevent errors when
a cell that was previously used was selected.
So here is my solution...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim InDate As Date, SS As Integer, ExpDel As Date
If Not Intersect(Target, Range("D
")) Is Nothing ThenInDate = DateValue(Trim(Target.Value))
SS = Target.Offset(0, 3).Value
ExpDel = EDD(InDate, SS)
With Target.Validation
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.IgnoreBlank = True
.InputTitle = "Expected Delivery:"
.InputMessage = Format(ExpDel, "mm/dd/yy ddd")
.Delete
End With
End If
End Sub