|
Menus
Hi:
In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isnt there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
If you are customizing the existing worksheet menu bar, then just don't
remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
There is no saveAs option for commandbars. If you do the customize route,
then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the .xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
Thanks Tom
If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the .xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
the type property determines if it is a "Worksheet Menu Bar" type. the
type is: msoBarTypeMenuBar You could possibly create this with code, then go into customize mode and drag (copy) commandbars to it. Here is some code modified from http://msdn.microsoft.com/library/de...commandbar.asp this only copies the top level controls when you do it for the Worksheet menu bar so it will need some work - this isn't an area I have done a lot of work in so someone else may have a better idea. Sub Tester1() Dim sOriginal As String Dim sCopy As String sOriginal = "Worksheet Menu Bar" sCopy = "New Worksheet Menu Bar" CBCopyCommandBar sOriginal, sCopy, True End Sub Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup ElseIf cbrCopy.Type = msoBarTypeNormal Then cbrCopy.Visible = True ElseIf cbrCopy.Type = msoBarTypeMenuBar Then cbrOriginal.Enabled = False cbrCopy.Visible = True cbrCopy.Enabled = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the ..xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
Thanks Tom
Your comment there are many ways to go with this becomes more obvious with each article I read and I have been researching this on and off for a week or so. I also though I would receive a reply from a menu person. I have seen some pretty good replies concerning menus in the post I researched. Anyway I think I have enough info and test examples now to go forward. Thanks again for your time, very much appreciated! TK "Tom Ogilvy" wrote: the type property determines if it is a "Worksheet Menu Bar" type. the type is: msoBarTypeMenuBar You could possibly create this with code, then go into customize mode and drag (copy) commandbars to it. Here is some code modified from http://msdn.microsoft.com/library/de...commandbar.asp this only copies the top level controls when you do it for the Worksheet menu bar so it will need some work - this isn't an area I have done a lot of work in so someone else may have a better idea. Sub Tester1() Dim sOriginal As String Dim sCopy As String sOriginal = "Worksheet Menu Bar" sCopy = "New Worksheet Menu Bar" CBCopyCommandBar sOriginal, sCopy, True End Sub Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup ElseIf cbrCopy.Type = msoBarTypeNormal Then cbrCopy.Visible = True ElseIf cbrCopy.Type = msoBarTypeMenuBar Then cbrOriginal.Enabled = False cbrCopy.Visible = True cbrCopy.Enabled = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the ..xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
Ron de Bruin might have some better first hand experience for you. He
seemed to be doing "dictator" applications when he first started posting here, so replacing the Worksheet Menu Bar would be a standard for that type of app. I don't think he addesses that here, but there is some good information: http://www.rondebruin.com/menuid.htm send him an email and I am sure he can give you some good advice. -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom Your comment there are many ways to go with this becomes more obvious with each article I read and I have been researching this on and off for a week or so. I also though I would receive a reply from a menu person. I have seen some pretty good replies concerning menus in the post I researched. Anyway I think I have enough info and test examples now to go forward. Thanks again for your time, very much appreciated! TK "Tom Ogilvy" wrote: the type property determines if it is a "Worksheet Menu Bar" type. the type is: msoBarTypeMenuBar You could possibly create this with code, then go into customize mode and drag (copy) commandbars to it. Here is some code modified from http://msdn.microsoft.com/library/de...commandbar.asp this only copies the top level controls when you do it for the Worksheet menu bar so it will need some work - this isn't an area I have done a lot of work in so someone else may have a better idea. Sub Tester1() Dim sOriginal As String Dim sCopy As String sOriginal = "Worksheet Menu Bar" sCopy = "New Worksheet Menu Bar" CBCopyCommandBar sOriginal, sCopy, True End Sub Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup ElseIf cbrCopy.Type = msoBarTypeNormal Then cbrCopy.Visible = True ElseIf cbrCopy.Type = msoBarTypeMenuBar Then cbrOriginal.Enabled = False cbrCopy.Visible = True cbrCopy.Enabled = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the ..xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
I update the page with a few new examples
http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... Ron de Bruin might have some better first hand experience for you. He seemed to be doing "dictator" applications when he first started posting here, so replacing the Worksheet Menu Bar would be a standard for that type of app. I don't think he addesses that here, but there is some good information: http://www.rondebruin.com/menuid.htm send him an email and I am sure he can give you some good advice. -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom Your comment there are many ways to go with this becomes more obvious with each article I read and I have been researching this on and off for a week or so. I also though I would receive a reply from a menu person. I have seen some pretty good replies concerning menus in the post I researched. Anyway I think I have enough info and test examples now to go forward. Thanks again for your time, very much appreciated! TK "Tom Ogilvy" wrote: the type property determines if it is a "Worksheet Menu Bar" type. the type is: msoBarTypeMenuBar You could possibly create this with code, then go into customize mode and drag (copy) commandbars to it. Here is some code modified from http://msdn.microsoft.com/library/de...commandbar.asp this only copies the top level controls when you do it for the Worksheet menu bar so it will need some work - this isn't an area I have done a lot of work in so someone else may have a better idea. Sub Tester1() Dim sOriginal As String Dim sCopy As String sOriginal = "Worksheet Menu Bar" sCopy = "New Worksheet Menu Bar" CBCopyCommandBar sOriginal, sCopy, True End Sub Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup ElseIf cbrCopy.Type = msoBarTypeNormal Then cbrCopy.Visible = True ElseIf cbrCopy.Type = msoBarTypeMenuBar Then cbrOriginal.Enabled = False cbrCopy.Visible = True cbrCopy.Enabled = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the ..xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
Menus
Hi Ron
As you indicated in your email €¯I also added the examples to my site€¯ http://www.rondebruin.com/menuid.htm I picked up the last procedure needed from your website. The complete menu manipulation I was trying to accomplish that you wrote the procedures for works great. My sincere thanks for the procedures and an excellent Job. Good Luck TK "Ron de Bruin" wrote: I update the page with a few new examples http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... Ron de Bruin might have some better first hand experience for you. He seemed to be doing "dictator" applications when he first started posting here, so replacing the Worksheet Menu Bar would be a standard for that type of app. I don't think he addesses that here, but there is some good information: http://www.rondebruin.com/menuid.htm send him an email and I am sure he can give you some good advice. -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom Your comment there are many ways to go with this becomes more obvious with each article I read and I have been researching this on and off for a week or so. I also though I would receive a reply from a menu person. I have seen some pretty good replies concerning menus in the post I researched. Anyway I think I have enough info and test examples now to go forward. Thanks again for your time, very much appreciated! TK "Tom Ogilvy" wrote: the type property determines if it is a "Worksheet Menu Bar" type. the type is: msoBarTypeMenuBar You could possibly create this with code, then go into customize mode and drag (copy) commandbars to it. Here is some code modified from http://msdn.microsoft.com/library/de...commandbar.asp this only copies the top level controls when you do it for the Worksheet menu bar so it will need some work - this isn't an area I have done a lot of work in so someone else may have a better idea. Sub Tester1() Dim sOriginal As String Dim sCopy As String sOriginal = "Worksheet Menu Bar" sCopy = "New Worksheet Menu Bar" CBCopyCommandBar sOriginal, sCopy, True End Sub Function CBCopyCommandBar(strOrigCBName As String, _ strNewCBName As String, _ Optional blnShowBar As Boolean = False) As Boolean ' This procedure copies the command bar named in the strOrigCBName ' argument to a new command bar specified in the strNewCBName argument. Dim cbrOriginal As CommandBar Dim cbrCopy As CommandBar Dim ctlCBarControl As CommandBarControl Dim lngBarType As Long On Error GoTo CBCopy_Err Set cbrOriginal = CommandBars(strOrigCBName) lngBarType = cbrOriginal.Type Select Case lngBarType Case msoBarTypeMenuBar Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarMenuBar) Case msoBarTypePopup Set cbrCopy = CommandBars.Add(Name:=strNewCBName, Position:=msoBarPopup) Case Else Set cbrCopy = CommandBars.Add(Name:=strNewCBName) End Select ' Copy controls to new command bar. For Each ctlCBarControl In cbrOriginal.Controls ctlCBarControl.Copy cbrCopy Next ctlCBarControl ' Show new command bar. If blnShowBar = True Then If cbrCopy.Type = msoBarTypePopup Then cbrCopy.ShowPopup ElseIf cbrCopy.Type = msoBarTypeNormal Then cbrCopy.Visible = True ElseIf cbrCopy.Type = msoBarTypeMenuBar Then cbrOriginal.Enabled = False cbrCopy.Visible = True cbrCopy.Enabled = True End If End If CBCopyCommandBar = True CBCopy_End: Exit Function CBCopy_Err: CBCopyCommandBar = False Resume CBCopy_End End Function -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom If I can indulge you with one more question: I book marked those sites. One I think explains how to build a "Worksheet Menu Bar" with code. As you pointed out in your first reply building toolbars is pretty straight forward; however, I haven't been able to replicate either the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have to be built with code or can they be duplicated in the customized box. Thanks again TK "Tom Ogilvy" wrote: There is no saveAs option for commandbars. If you do the customize route, then your changes will be saved in the .xlb file and the bar will be remain in that customized state until someone resets it or moves/renames the ..xlb file or re-cusstomizes it. there are many ways to go with this so maybe a little background information might be inorder This is a good place to start although not a tutorial: http://support.microsoft.com/default...b;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 File Name: WE1183.EXE File Size: 58041 bytes File Date: 06/20/97 Keywords: kbfile kbappnote Description: This Application Note can help you learn techniques for writing Visual Basic(R) for Applications code to customize menus in Microsoft Excel 97. This Application Note contains code examples that you can use with the following elements: menu bars, menus, menu items, submenus, and shortcut menus. http://msdn.microsoft.com/library/of...97/web/008.htm MS Officer 97 Programmer's Guide Chapter 8: Menus and Toolbars Here is an article about creating commandbars with code: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...02&Product=xlw How to customize menus and menu bars in Excel http://support.microsoft.com/?id=159619 XL97: Sample Macros for Customizing Menus and Submenus http://support.microsoft.com/?id=213550 XL2000: Sample Macros for Customizing Menus and Submenus -- Regards, Tom Ogilvy "TK" wrote in message ... Thanks Tom I think I need more of a stick by stick procedure. Say for example I wanted to customize Worksheet Menu Bar, I think I choose View/Toolbars/customize then Worksheet Menu Bar then new, delete the controls not needed and save it as "NewMenu" for example. The next step: I assume it is loaded in the Workbook Open and deleted in the Workbook close sommthing like? Private Sub Workbook_Open() Application.CommandBars("NewMenu").Visible = True End Sub Now will the original menus load the next time Excel loads if this workbook is not opened? Thanks TK "Tom Ogilvy" wrote: If you are customizing the existing worksheet menu bar, then just don't remove the controls you want to retain. If you are building new "worksheet menu bar", then just add the standard controls you want to use. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: In an effort to make a workbook less intimidating I would like to make invisible (actually removed not dimmed) all Toolbars including the Worksheet Menu Bar and Formula Bar when a workbook is opened then display a customized Worksheet Menu Bar and Standard Menu Bar. At the workbook close I world like to restore (not reset) the users menu setup. I know how to add and delete Worksheet Menu Items and create toolbars with VBA but isn't there a way to use or retain and use the basics such as File/save and Tools/Spelling without writing code or macros for each one. Thanks TK |
| All times are GMT +1. The time now is 09:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com