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:=msoControlButton)
    
    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
0
mrey18 (3)
2/10/2004 4:45:34 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
754 Views

Similar Articles

[PageSpeed] 45

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" <mrey18@hotmail.com> wrote in message
news:mh1i205rvd7ep4dej5nejkdueq6l8r602c@4ax.com...
> 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:=msoControlButton)
>
>     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


0
Bernie
2/10/2004 5:47:13 PM
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
0
mrey18 (3)
2/11/2004 2:24:30 AM
Reply:

Similar Artilces:

How to create a partition on an external USB drive?
Toshiba Tablet XP Home/Student with plenty of memory. I have the program DriveImage XML. At one point it says to create a partition somewhere for the image it's going to create. I have a 40G external plug and play HD. I'd like to create a partition on it without disturbing other files and folders I have on it. There's plenty of free space. Various resources say use XP Disk Management to create that partition. So when I go there it appears as they say but when they say click on the free space choose the selection "Create new partiton." I have right clicked ...

user created shapes non printing
I started have a problem with vision 2002 that I have not noticed before. When I create a new shape, by default, it assumes the non-printing properly under FORMAT � BEHAVIOR. Also if I group a set of "printing" shapes the group will become non-printing. Can I change this behavior? How are you creating the new shape? Also are you using layers in your document? -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Robert" <hammer_757@hotmail.com> wrote in message news:9ec427f7.0409231005.576...

Error message needed if incorrect Date Of Birth (DOB) entered
Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need a...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

RichEditCtrl and Toolbar
Where can I find a Toolbar to download like the one used in Hotmail for Rich Edit Text and how do they attach the tool bar at the top of the richEditCtrl? ...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

"All users" "Programs" create/modify shortcut from app...
Hi all, I've created two shortcuts into "Programs" folder for "All Users" It lets me to get them available for all user. The problem: Application running in "User" context needs to delete and re-create such links but it fails due to an "access denied" ... Settings correct permission to such links it starts working as well I've created links using the IShellLink/IPersistFile sehll interfaces. So, I actually need to have link under "programs" for "All Users" which might be modified by application running in "Users"...

How to create a connection point in Excel
When I group autoshapes the group itself does not have connection points. A connector connects to one of the grouped shapes instead. So, the connector beginconnecedshape (or endconnectedshape) property contains the name of the contained shape and not the name of the group. Is there a way to create connection points for a group? Alternatively, is it possible to change a group into a single shape with connection points? ...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

Publisher 2003 Viewer is needed
Can Microsoft create a free viewer program for .pub files like they have for the other programs in Office? Can they? Yes Will they? Unlikely Mike Larry M. in Illinois wrote: > Can Microsoft create a free viewer program for .pub files like they have for > the other programs in Office? Larry M. in Illinois <Larry M. in Illinois@discussions.microsoft.com> was very recently heard to utter: > Can Microsoft create a free viewer program for .pub files like they > have for the other programs in Office? Could they? Yes Is it profitable for them to do so? Probably not. W...

VBA- Application.Wait?
Does PowerPoint not have "Application.Wait" like Excel? Is there another way to pause code for a few seconds in PPT 2003/2007 while a small bat file runs? -Mel On 3/10/10 2:29 AM, Mel wrote: > Does PowerPoint not have "Application.Wait" like Excel? Is there > another way to pause code for a few seconds in PPT 2003/2007 while a > small bat file runs? > > -Mel From Example 8.4 on my Web site (http://www.PowerfulPowerPoint.com/): Sub Wait() waitTime = 5 Start = Timer While Timer < Start + waitTime DoEvents ...

Visio 2003: Custom Toolbars disappear
Sorry for the cross-posting, but I didn't get any responses in the visio.troubleshoot NG. I recently upgraded to Visio 2003 and have a perplexing problem. When I create a custom toolbar--or even when I customize a standard toolbar--they do not appear the next time I load Visio. Any ideas? I'm getting tired of recreating my custom toolbar every day! Thanks, Dave Go to View/Toolbars/ and you should see the list of customized toolbars you have made at the bottom of the menu. Selecting them will make them appear I tried to duplicate the problem you are having with seeing a standard ...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Createing Quote sometimes make Error: 80070057
Hello NG, Today I found some funny problems. I create Quotes via the quote WebService from the CRM. This normally works fine, but now I have some quotes, which will get me an error, when I open it. This error is no normal error, I will get an white/yellow Page with ASPX Errorpage: Here the Message (sorry but I only have this error in German): Serverfehler in der Anwendung '/'. ---------------------------------------------------------------------------- ---- Falscher Parameter. Beschreibung: Beim Ausf�hren der aktuellen Webanforderung ist ein unverarbeiteter Fehler aufgetret...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Creating Control Grid
Hi all, I would like to know as to how I should go about creating a control grid of my very own. I just need a bit of push (suggestions). Thanks In Advance Where are you getting stuck? Of course, if the grid isn't too large and most of the cells will have data, then you could simply use a two-dimensional array. For larger grids that will be sparsley populated, there are a couple of algorithms to consider. A simple one is to create a one-dimensional array that represents the rows (or columns), and have each item contain or reference a linked list for all items in that row (complete...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

Need Hyperlink from Menu Page to work in email
I have a workbook with several tabs. First tab is a "menu" type page (summary of other pages). Currently I have hyperlinks to all the other pages from the menu page. What I want to be able to do is copy the menu page into an email and enable the users to click on the existing hyperlinks (whatever adjustments needed) to go directly to their page of the workbook. Using Excel 2007. Please advise how to do this and what changes may be need for both options to work. ...

Plugins, more info needed
Hi, We are looking at adding plugins to our app, but i am not sure what the best way of doing would it be. Does anybody know where i could find some example of project with their plug-ins. Preferably some code to play around with would be great. How would my app load those plugins? would they all be Dll's that load at runtime? We want to offer an SDK eventually allowing users to create their own plugins, so we want to do it properly to ensure that it is as flexible as possible. Many thanks. Sims. Hi, Typically, you have developers create a dll your app will load that exports cert...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...