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
