What do each control: Excell11.pip and Excell11.xlb

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
I was wonder if anybody could explain the difference between the Excell11.pip and Excel11.xlb. I know the 11 means i'm running XL 2003.

The reason i need to know is because i'm trying to build a standardized templete with macro icons for my company's XL users. I've already distributed the macros, but the marcros need to be linked to toolbar icons. I figured the best way to do this would be to distribute a standardized excel templete (most users would be lost trying to link the macros themselves). After some research, I also learned about the .xlb but I also found the .pip and I have no idea what it does (now i feel like one of the user's i described above).

Does the .xlb also control settings in the tools-->options-->general?
What does the .pip do?
Any better ideas to link the macro's to the icons for the 1000's of users?

Does anyone know how to make an additional item in format toolbar (you know the one with File, Edit, View, Insert etc..)? I think this would be another option for distrubtion since I could just add a new drop down to the this toolbar with all the macro's listed.

Thanks for any help and sorry for the mult. questions.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
I would reiterate the suggestion to create what you need as an add-in. Have the add-in run a routine to create the new toolbar (or add items to an existing toolbar) when it is activated as an add-in (on the Workbook_AddinInstall event) and remove them when it is uninstalled. Joe Was gave you some links to a pretty good description of how to create or add toolbar items.

The problem with distributing a new .xlb file is that it will step on any customized toolbars that your users have set up for themselves - which is guaranteed to engender ill-will - and you still have the problem of ensuring that your file with the VBA in it is in the right place on each machine, and so on. The add-in packages all that stuff together, and allows users to load and unload the tools as required.
 

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
I ended up using Joe Was toolbar trick. It works perfectly! This has saved me a ton of time and work! I added a few ampersands (&) for hot keys to top off Joe's code. Joe and everyone else, thank you so much for your help and guidance.

-Risk


Sub myAdd_MyMenu_ToDefaultToolbar()
'Standard module code, like: Module1!
Dim myNewMainMenu, myNewMainMenuItem
'This adds a new item to the Default toolbar at the very top of the page, like: File Edit View...

On Error GoTo myErr
'Delete custom menu if it exists!
Call Remove_MyMenu

'Add a new item to the default toolbar!
With CommandBars("Worksheet Menu Bar")
Set myNewMainMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
End With

'Name this new toolbar item!
myNewMainMenu.Caption = "&Macro Menu"

'Add a sub-menu item to the new menu list!
Set myNewItem1 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem1
.Caption = "Value HP &All"
.TooltipText = "Values all Hyperion formula's except HPLNK on all tabs."
.Style = msoButtonCaption
.OnAction = "ValueHPAll" 'Name of macro to run.
End With

'Add a Second sub-menu item to the new menu list!
Set myNewItem2 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem2
.Caption = "Value HP &Selected"
.TooltipText = "Values all Hyperion formula's which are selected."
.Style = msoButtonCaption
.OnAction = "ValueHPSelected" 'Name of macro to run.
End With

'Add a Third sub-menu item to the new menu list!
Set myNewItem3 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem3
.Caption = "Hide Zero &Row"
.TooltipText = "Hides the row if the selected sum is zero or null."
.Style = msoButtonCaption
.OnAction = "HideZeroRows" 'Name of macro to run.
End With
Exit Sub

myErr:
MsgBox "An error has occured, did not create menu items. " & Chr(13) & _
"Error number: " & Err.Number & Chr(13) & "Error Description: " & Err.Description, vbExclamation + vbOKOnly, "Error!"
Resume Next
End Sub


Sub Remove_MyMenu()
'Standard module code, like: Module1!
'Removes custom menu if it exists!

On Error Resume Next

CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0
End Sub

Private Sub myTestItem()
'Standard module code, like: Module1!

MsgBox "This is a test for a Sub-Menu item activation!"
End Sub
 

Forum statistics

Threads
1,137,300
Messages
5,680,699
Members
419,928
Latest member
dolincasting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top