private sub

[Guest]

I have the following private sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell = Range("H1") Or ActiveCell = Range("J1") Or ActiveCell =
Range("H2") Or ActiveCell = Range("J2") Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If

End Sub

The intent is for the formula bar to be displayed unless the activecell is
in H1, H2, J1, or J2.

Right now the formula bar is only displayed if there is data in a cell. If
there is no data, the formalbar is hidden.

Any help would be appreciated......Thanks

 

[Guest]

Try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Union(Range("H1:H2"), Range("J1:J2"))
With Application
.DisplayFormulaBar = (Intersect(Target, rng) Is Nothing)
End With
End Sub

Regards,
Greg

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("H1,H2,J1,J2")) Is Nothing Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[George Nicholson]

The default property of a cell object is Value. Since you haven't specified
otherwise, you are currently comparing the Value of ActiveCell to the Values
of the 4 other cells. I gather what you really want to do is compare
Addresses. Here is one approach:

Select Case ActiveCell.Address
Case "$H$1", "$J$1", "$H$2", "$J$2"
Application.DisplayFormulaBar = False
Case Else
Application.DisplayFormulaBar = True
End Select

HTH,