Solved: Lose formula reference in active cell

[tammyl]

Hi,

I created a macro to Propercase text in an active cell when i double-click. Since then i have lost the ability to follow formulas when double-click in the cell.

How can i get this functionality back.

Following is my vba for propercase
[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Intersect(ActiveCell, Range("B11:B50"), Target) Is Nothing Then
'Exit Sub
'Cancel = True
Application.EnableEvents = True
Else
ActiveCell.Value = StrConv(ActiveCell.Value, vbProperCase)
Application.EnableEvents = True

End If
End Sub
[/VBA]

Hope someone can assist.

Thanks
Tammyl

[macropod]

Hi Tammyl,

Your macro replaces the formula with its result, so there's no formula left to trace! Try:
[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(ActiveCell, Range("B11:B50"), Target) Is Nothing Then
If ActiveCell.Value <> StrConv(ActiveCell.Value, vbProperCase) Then
Dim CellString As String
Application.EnableEvents = False
CellString = ActiveCell.Formula
If Left(CellString, 1) = "=" Then
ActiveCell.Value = "=PROPER(" & Right(CellString, Len(CellString) - 1) & ")"
Else
ActiveCell.Value = StrConv(CellString, vbProperCase)
End If
Application.EnableEvents = True
End If
End If
End Sub[/VBA]

[tammyl]

Thanks Paul,

This worked really well and saved the potential for errors.

Thanks again for your prompt reply.

Tammy