Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Temp Toolbar
Good day all, as a newbie i need help with the following; I got the code
from this site by searching and was supplied by Bob Phillips. What i need to do is take all toolbars, only leaving the temp toolbar with about 5 macro options on e.g. save, print etc.. I do not need sub menu's but need it reset to normal when the user is finished. Sub PartsMenu() Dim HelpMenu As CommandBarControl Dim MainMenu As CommandBarPopup Dim MenuItem As CommandBarControl Dim Submenuitem As CommandBarButton ' Deletes menu if it exits Call DeleteMenu ' Find the help menu Set HelpMenu = CommandBars(1).FindControl(ID:=30010) If HelpMenu Is Nothing Then ' Add the menu to the end Set MainMenu = CommandBars(1).Controls. _ Add(Type:=msoControlPopup, temporary:=True) Else ' Add menu before help Set MainMenu = CommandBars(1).Controls. _ Add(Type:=msoControlPopup, befo=HelpMenu.Index, _ temporary:=True) End If ' Add caption MainMenu.Caption = "&Parts Utility" ' Searching for parts Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "&Search Parts..." .FaceId = 48 .ShortcutText = "Ctrl+Shift+S" .OnAction = "SetupSearch" End With ' LO / Remaining printout Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "&Generate Parts Review..." .FaceId = 285 .ShortcutText = "Ctrl+Shift+D" .OnAction = "LORemaining" End With ' View summary sheet Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlPopup) With MenuItem .Caption = "Sub menu" End With Set Submenuitem = MenuItem.Controls.Add _ (Type:=msoControlButton) With Submenuitem .Caption = "&View Summary..." .FaceId = 592 .OnAction = "Summary" End With ' Print summary sheet Set Submenuitem = MenuItem.Controls.Add _ (Type:=msoControlButton) With Submenuitem .Caption = "Print Summary" ' .Application = 364 .OnAction = "PrintSummary" End With End Sub Sub DeleteMenu() On Error Resume Next Application.CommandBars(1).controls("&parts Utility").Delete On Error GoTo 0 End Sub Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Temp Toolbar
Do you mean you want to hide all toobars?
Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCBEnd Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars if oCB.Name < "Temp" Then oCB.Enabled = False Next oCB End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Good day all, as a newbie i need help with the following; I got the code from this site by searching and was supplied by Bob Phillips. What i need to do is take all toolbars, only leaving the temp toolbar with about 5 macro options on e.g. save, print etc.. I do not need sub menu's but need it reset to normal when the user is finished. Sub PartsMenu() Dim HelpMenu As CommandBarControl Dim MainMenu As CommandBarPopup Dim MenuItem As CommandBarControl Dim Submenuitem As CommandBarButton ' Deletes menu if it exits Call DeleteMenu ' Find the help menu Set HelpMenu = CommandBars(1).FindControl(ID:=30010) If HelpMenu Is Nothing Then ' Add the menu to the end Set MainMenu = CommandBars(1).Controls. _ Add(Type:=msoControlPopup, temporary:=True) Else ' Add menu before help Set MainMenu = CommandBars(1).Controls. _ Add(Type:=msoControlPopup, befo=HelpMenu.Index, _ temporary:=True) End If ' Add caption MainMenu.Caption = "&Parts Utility" ' Searching for parts Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "&Search Parts..." .FaceId = 48 .ShortcutText = "Ctrl+Shift+S" .OnAction = "SetupSearch" End With ' LO / Remaining printout Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlButton) With MenuItem .Caption = "&Generate Parts Review..." .FaceId = 285 .ShortcutText = "Ctrl+Shift+D" .OnAction = "LORemaining" End With ' View summary sheet Set MenuItem = MainMenu.Controls.Add _ (Type:=msoControlPopup) With MenuItem .Caption = "Sub menu" End With Set Submenuitem = MenuItem.Controls.Add _ (Type:=msoControlButton) With Submenuitem .Caption = "&View Summary..." .FaceId = 592 .OnAction = "Summary" End With ' Print summary sheet Set Submenuitem = MenuItem.Controls.Add _ (Type:=msoControlButton) With Submenuitem .Caption = "Print Summary" ' .Application = 364 .OnAction = "PrintSummary" End With End Sub Sub DeleteMenu() On Error Resume Next Application.CommandBars(1).controls("&parts Utility").Delete On Error GoTo 0 End Sub Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Temp Toolbar
HI Bob,
I need to open a document with the macro in and then from this it will open another workbook which the user must work with and it is this document that i need to have only certain commands for the user to choose from. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Temp Toolbar
Okay, so add the code I provided to that second workbook.
-- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... HI Bob, I need to open a document with the macro in and then from this it will open another workbook which the user must work with and it is this document that i need to have only certain commands for the user to choose from. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Temp Toolbar
Hi Bob, I think i am not explaining myself properly, sorry. I get file
that is downloaded from SAP onto the computers C drive, i am wanting to create a macro file so to say that the user must open and input on request the file name that he saved it under, the code will then open that file it will then be manipulated into the cells and then the user will change the data and he must then be able to save it again but i have to delete the headers before it gets saved, so thought that if i remove the commands and only have one button for example, it would then run the code to save it and delete the not required rows. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using obj.Namespace(sourceFileName) Namespace(DestinationFilename).Itemsand then opening the xls file which creates a Temp Folder inside Temp | Excel Discussion (Misc queries) | |||
Excel 2000 cannot save and create temp file | Setting up and Configuration of Excel | |||
how to create toolbar? | Excel Programming | |||
macros create temp files | Excel Programming | |||
Create toolbar-help | Excel Programming |