Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with adjusting width of VBA-created toolbars
Hello,
I am using VBA to create a series of toolbars, which are then assigned to macros. The problem I am having is: If I create a toolbar manually, it automatically adjusts its width to whatever caption is specified. However, when I create a toolbar with VBA, the width does not adjust itself to the caption, so the toolbar is wider than neccessary. Here is an example of the code I am using: Sub newButton3() On Error Resume Next CommandBars("C").Delete Dim cBar, ccBar CommandBars.Add.Name = "C" Set cBar = CommandBars("C") Set ccBar = CommandBars("C").Controls.Add(Type:=msoControlButt on) With cBar .Visible = True .Left = 945 .Top = 260 .Controls.Add Type:=msoControlButton .Controls(1).Caption = "UserSetIT" .Controls(1).OnAction = _ "'C:\IFR Macros\MacroBank.xls'!UserSetit" End With With ccBar .Style = msoButtonCaption .TooltipText = "Allows User to Set CF and Span" End With End Sub Any pointers/suggestions would be greatly appreciated. Thanks for any help. Mark |
#2
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with adjusting width of VBA-created toolbars
Mark,
Your code worked fine for me.... Changing the caption text resulted in different width toolbars each time the code was run. HTH, Bernie MS Excel MVP "Mark Reynolds" wrote in message ... Hello, I am using VBA to create a series of toolbars, which are then assigned to macros. The problem I am having is: If I create a toolbar manually, it automatically adjusts its width to whatever caption is specified. However, when I create a toolbar with VBA, the width does not adjust itself to the caption, so the toolbar is wider than neccessary. Here is an example of the code I am using: Sub newButton3() On Error Resume Next CommandBars("C").Delete Dim cBar, ccBar CommandBars.Add.Name = "C" Set cBar = CommandBars("C") Set ccBar = CommandBars("C").Controls.Add(Type:=msoControlButt on) With cBar .Visible = True .Left = 945 .Top = 260 .Controls.Add Type:=msoControlButton .Controls(1).Caption = "UserSetIT" .Controls(1).OnAction = _ "'C:\IFR Macros\MacroBank.xls'!UserSetit" End With With ccBar .Style = msoButtonCaption .TooltipText = "Allows User to Set CF and Span" End With End Sub Any pointers/suggestions would be greatly appreciated. Thanks for any help. Mark |
#3
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with adjusting width of VBA-created toolbars
Thanks Bernie,
After reading your response I tried my macro again and discovered you were right: The toolbar width was automatically adjusting to the caption. What had fooled me was the fact that the toolbar was coming out a little wider than neccessary, causing extra space after the caption. I fooled around some more and took care of this problem by removing the following line of code: .Controls.Add Type:=msoControlButton Thanks for helping lead me to this fix. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Cell Width WITHOUT changing Column width | Excel Discussion (Misc queries) | |||
adjusting column width using keystrokes | Excel Discussion (Misc queries) | |||
ASC() does not convert full-width to half-width letters (Exel 2003 | Excel Worksheet Functions | |||
Toolbars Created in Excel 2002 not available in 2003 | Excel Discussion (Misc queries) |