Re: [ExcelVBA] VBA Position of Comments Box

Hi David and all

Having a little bit of fun to get this to work. Due to my
inexperience. It is in the area of the macro name!
Also I meant to say to the right hand side of visible screen!

I would like the box to appear on the right side when arrow over red
triangle (F5)and also when opened by a macro.

I would like to load the workbook to groups. I need moderators to let me.

Your comments would be appreciated.

Thanks

Charlie

David Grugeon wrote:
> Try
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> 'www.contextures.com/xlcomments03.html
> Dim rng As Range
> Dim cTop As Long
> Dim cWidth As Long
> Dim cmt As Comment
> Dim sh As Shape
>
> Application.DisplayCommentIndicator _
> = xlCommentIndicatorOnly
> Set rng = ActiveWindow.VisibleRange
> cTop = rng.Top + rng.Height / 2
> cWidth = rng.Left + rng.Width / 2
> If ActiveCell.Comment Is Nothing Then
> 'do nothing
> Else
> Set cmt = ActiveCell.Comment
> Set sh = cmt.Shape
> sh.AutoShapeType = msoShapeRoundedRectangle
> sh.Top = cTop - sh.Height / 2
> sh.Left = 0
> cmt.Visible = True
> End If
>
>
> End Sub
>
> This will show the comment at the left of the sheet, not in the margin. I
> do not think there is a way of showing a shape outside the sheet. The
> workaround would be to include a blank column at the left of the sheet and
> use the above method to make the comment appear in this column. You could
> print a border at the right of column A so it was separated from the rest of
> the sheet.
>
>
> Regards
>
> David Grugeon
> Moderator - Excel VBA
>
> Original message
>
>
>
>> -----Original Message-----
>> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
>>
> Of
>
>> Charlie
>> Sent: Sunday, June 14, 2009 10:37 AM
>> To: ExcelVBA@yahoogroups.com
>> Subject: [ExcelVBA] VBA Position of Comments Box
>>
>>
>> Hi
>>
>>
>> Courtesy of Contextures ave this code below to centre comments on
>> active page.
>>
>> I cannot ascertain which codes to change to have the Comments Box appear
>> on the left hand side of page. (left of centre to left outside margin)
>> Maybe the line to change is
>>
>> Also can shape be changed to round cornered rectangle.
>>
>> Thanks
>>
>> Charlie
>>
>>
>> *******************
>>
>>
>> Show Comments in Centre of Active Window* *
>> <http://www.contextures.com/xlcomments03.html#Top>
>>
>> Paste the following code onto a worksheet module. If a cell with a
>> comment is selected on that sheet, its comment is displayed in the
>> centre of the active window's visible range.
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> 'www.contextures.com/xlcomments03.html
>> Dim rng As Range
>> Dim cTop As Long
>> Dim cWidth As Long
>> Dim cmt As Comment
>> Dim sh As Shape
>>
>> Application.DisplayCommentIndicator _
>> = xlCommentIndicatorOnly
>>
>> Set rng = ActiveWindow.VisibleRange
>> cTop = rng.Top + rng.Height / 2
>> cWidth = rng.Left + rng.Width / 2
>> If ActiveCell.Comment Is Nothing Then
>> 'do nothing
>> Else
>> Set cmt = ActiveCell.Comment
>> Set sh = cmt.Shape
>> sh.Top = cTop - sh.Height / 2
>> sh.Left = cWidth - sh.Width / 2
>> cmt.Visible = True
>> End If
>>
>>
>> End Sub
>>
>>
>>
>>
>>
>> ------------------------------------
>>
>> ----------------------------------
>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>>
> here:
>
>> http://www.mousetrax.com/techtrax to enter the ezine, then search the
>>
> ARCHIVES for
>
>> EXCEL VBA.
>>
>> ----------------------------------
>> Visit our ExcelVBA group home page for more info and support files:
>> http://groups.yahoo.com/group/ExcelVBA
>>
>> ----------------------------------
>> More free tutorials and resources available at:
>> http://www.mousetrax.com
>>
>> ----------------------------------Yahoo! Groups Links
>>
>>
>>
>>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
mailto:ExcelVBA-digest@yahoogroups.com
mailto:ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/