Comments

[kirkm]

I'm really hoping someone can assist - I've spent ages
trying to sort what should be an easy task. Even asked a couple
of questions here, but never got anywhere.

The problem is positioning a Comment. Here's what I have (the 2
remmed lines show some of the methods I've tried).

--

Sub Showc(ByVal r)

Dim rng As Range
Dim cTop
Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2
With Worksheets("Sheet1").Range(r)
If Not .Comment Is Nothing Then
With .Comment
With .Shape
.Top = cTop - 215
'.Left = Range("M11").Left + 47
'.Left = Form1.Left + Form1.Width + 90
.Left = 739
Application.StatusBar = .Left
End With
.Visible = True
End With
End If
End With
Set rng = Nothing

End Sub

--
The Top postion is perfect, but the Left varies. Even using constant
739 (as a test) the status bar shows the actual left is 739.5, or
sometimes 738.75. The higher number places the Comment closer to
screen left. Ideally I'd like it to sit beside Form1 on the right hand
side, intending this to allow for different screen resolutions
as the Form would always be centered.

Does anyone know why it's changing, and if there's any way to
use the Forms position to calculate where Comment left should go?

Thanks - Kirk

 

[Jacob Skaria]

..Left = Application.Width - ((Application.Width - UserForm1.Width) / 2)

If this post helps click Yes

 

[kirkm]

.Left = Application.Width - ((Application.Width - UserForm1.Width) / 2)

Thanks Jacob.

Can you explain this -

With .Shape

tmp = 89 + (Application.Width -
((Application.Width - frmSerial.Width) / 2))
.Left = tmp
Application.StatusBar = .Left & " " & tmp
End With

What shows in the status bar us

739.5 738.875

Proving that the value .Left is given, isn't the resultant value.
(So no wonder my Comment won't stay put !)

Isn't this a bit odd - Excel not doing what it's told??!!
Be nice to know why.

Cheers - Kirk

 

[kirkm]

Actually I think I have found why.

The line numbers - as they pass from 999 to 1000
or 9999 to 10000 the comment is put that far further to the right.