Seeking a tweak to the sub below. Currently it displays "txt1" aligned with
the top edge of the selected cell, just to the right of it. Unfortunately
this position obscures immediate cols to the right of the selected cell,
which user occasionally needs to see. I want to "push" the display vertically
down a bit and align the top edge of "txt1" just below the bottom edge of the
selected cell so that user can see the contents in the adjacent cells. Thanks
----------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A31:A200")) Is Nothing Then
Me.Shapes("txt1").Left = Target.Left + Target.Width
Me.Shapes("txt1").Top = Target.Top
ActiveSheet.Shapes("txt1").Visible = True
Else
ActiveSheet.Shapes("txt1").Visible = False
End If

End Sub

Hi Max,

As well as answering your question, the following method shows an
alternative method instead of adding the cell width for the Left position.
See comment for adjusting the top of txt1 if you don't want it exactly at the
bottom of the Target.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A31:A200")) Is Nothing Then

'Can use use Offset as alternative method to adding width
Me.Shapes("txt1").Left = Target.Offset(0, 1).Left

'Adjust + 2 by experimenting for how far below
'the bottom of the cell txt1 is required.
Me.Shapes("txt1").Top = Target.Offset(1, 0).Top + 2

ActiveSheet.Shapes("txt1").Visible = True
Else
ActiveSheet.Shapes("txt1").Visible = False
End If

End Sub

--
Regards,

OssieMac


"Max" wrote:

Seeking a tweak to the sub below. Currently it displays "txt1" aligned with
the top edge of the selected cell, just to the right of it. Unfortunately
this position obscures immediate cols to the right of the selected cell,
which user occasionally needs to see. I want to "push" the display vertically
down a bit and align the top edge of "txt1" just below the bottom edge of the
selected cell so that user can see the contents in the adjacent cells. Thanks
----------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A31:A200")) Is Nothing Then
Me.Shapes("txt1").Left = Target.Left + Target.Width
Me.Shapes("txt1").Top = Target.Top
ActiveSheet.Shapes("txt1").Visible = True
Else
ActiveSheet.Shapes("txt1").Visible = False
End If

End Sub

Hi Max

Add the target height as below..

Me.Shapes("txt1").Top = Target.Top + Target.Height

--
Jacob (MVP - Excel)


"Max" wrote:

Seeking a tweak to the sub below. Currently it displays "txt1" aligned with
the top edge of the selected cell, just to the right of it. Unfortunately
this position obscures immediate cols to the right of the selected cell,
which user occasionally needs to see. I want to "push" the display vertically
down a bit and align the top edge of "txt1" just below the bottom edge of the
selected cell so that user can see the contents in the adjacent cells. Thanks
----------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A31:A200")) Is Nothing Then
Me.Shapes("txt1").Left = Target.Left + Target.Width
Me.Shapes("txt1").Top = Target.Top
ActiveSheet.Shapes("txt1").Visible = True
Else
ActiveSheet.Shapes("txt1").Visible = False
End If

End Sub

Many thanks, OssieMac. Appreciate the learnings as well.

Many thanks, Jacob. Congrats on your MVP attainment!

Thanks Max...

--
Jacob (MVP - Excel)


"Max" wrote:

Many thanks, Jacob. Congrats on your MVP attainment!