Prev: How do I create a hyperlink with a macro?
Next: Compare two lists and get differences
From: Max on 28 Apr 2010 22:10
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
From: OssieMac on 29 Apr 2010 01:55
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
From: Jacob Skaria on 29 Apr 2010 03:21
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
From: Max on 29 Apr 2010 04:51
Many thanks, OssieMac. Appreciate the learnings as well.

From: Max on 29 Apr 2010 04:53
Many thanks, Jacob. Congrats on your MVP attainment!
 |  Next  |  Last
Pages: 1 2
Prev: How do I create a hyperlink with a macro?
Next: Compare two lists and get differences