try this

Sub reduce()
Dim a As Range

For Each a In Cells.SpecialCells(xlCellTypeComments)
a.Comment.Visible = False
a.Comment.Shape.TextFrame.AutoSize = True
Next a
End Sub





On Tue, Jul 19, 2011 at 9:56 AM, Cab Boose <swch...@gmail.com> wrote:

> Hi Daniel
>
> Thanks for a great result.  Not quite there yet.  When double click the
> comment box doubles it size.  When cursor is moved away from that cell and
> then back to that cell the double size is still there sometimes.  Sometimes
> it does go back to normal but not all the time.
>
> So when the comments has been double clicked and then closed by moving the
> cursor, when looking at that comments again it should be normal size.
>
> Hope I  have explained well.
>
> Thankyou
>
>
> Charlie
>
> On Mon, Jul 18, 2011 at 10:27 PM, Daniel <dcolarde...@free.fr> wrote:
>
>> Hi,****
>>
>> Have alook at the attached workbook.****
>>
>> You need a standard module where you paste :****
>>
>> ** **
>>
>> Public Res As String****
>>
>> ** **
>>
>> In the sheet module, paste :****
>>
>> ** **
>>
>> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
>> Boolean)****
>>
>> If Intersect(ActiveSheet.Cells.SpecialCells(xlCellTypeComments), Target) _
>> ****
>>
>>     Is Nothing Then Exit Sub****
>>
>> Cancel = True****
>>
>> If Res <> "" Then****
>>
>>     With Range(Res).Comment.Shape****
>>
>>         .Height = .Height / 2****
>>
>>         .Width = .Width / 2****
>>
>>     End With****
>>
>> End If****
>>
>> Res = Target.Address****
>>
>> With Target.Comment.Shape****
>>
>>     .Height = .Height * 2****
>>
>>     .Width = .Width * 2****
>>
>> End With****
>>
>> End Sub****
>>
>> ** **
>>
>> Regards.****
>>
>> Daniel****
>>
>> ** **
>>
>> *De :* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *De la part de* Cab Boose
>> *Envoyé :* lundi 18 juillet 2011 09:56
>> *À :* excel-macros@googlegroups.com
>> *Objet :* $$Excel-Macros$$ Comments Boxes - Temporary Zoom In****
>>
>> ** **
>>
>> Hi Ashish and all****
>>
>>  ****
>>
>> I have a spreadsheet (thanks to Ashish) that uses comments boxes.  The
>> size of the box is great.  But on some occassions the user will want to see
>> a larger box.  I know it can be increased thru edit and resize etc but is
>> there a way to say have a right click where the size  could double.  When
>> finished and user goes to another box the box size returns to standard.**
>> **
>>
>> Another option may be to have a shortcut key to double the size of active
>> comments box, return to standard size when finished.****
>>
>> Have Googled with no luck.****
>>
>>  ****
>>
>>  ****
>>
>> Thankyou everyone****
>>
>>  ****
>>
>> Charlie Harris****
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel****
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
http://akoul.posterous.com/
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>


P Before printing, think about the environment.

-- 
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Reply via email to