Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments
..Left = Application.Width - ((Application.Width - UserForm1.Width) / 2)
If this post helps click Yes --------------- Jacob Skaria "kirkm" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments
On Wed, 25 Mar 2009 21:38:00 -0700, Jacob Skaria
wrote: .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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000 how to format the comments font all comments | Excel Discussion (Misc queries) | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
Comments | Excel Programming | |||
Comments Only | Excel Discussion (Misc queries) | |||
Comments | Excel Programming |