Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
I wouldn't do it.
But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill -- Dave Peterson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Hi:
My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Thanks for the reply.
There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
This may help you:
Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Thanks Dave that helps.
Is there other values I can display as well? Just curious. Thanks Bill Dave Peterson wrote: This may help you: Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Look at VBA's help for CommandbarControl. You'll see lots of properties to
choose from. B Nieds wrote: Thanks Dave that helps. Is there other values I can display as well? Just curious. Thanks Bill Dave Peterson wrote: This may help you: Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
If you're rebuilding a toolbar, you might want to bite the bullet and do it all
mechanically. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. B Nieds wrote: Thanks for your help. Now I have a list of the buttons I need to receate. Bill Dave Peterson wrote: Look at VBA's help for CommandbarControl. You'll see lots of properties to choose from. B Nieds wrote: Thanks Dave that helps. Is there other values I can display as well? Just curious. Thanks Bill Dave Peterson wrote: This may help you: Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Thanks for your help. Now I have a list of the buttons I need to receate.
Bill Dave Peterson wrote: Look at VBA's help for CommandbarControl. You'll see lots of properties to choose from. B Nieds wrote: Thanks Dave that helps. Is there other values I can display as well? Just curious. Thanks Bill Dave Peterson wrote: This may help you: Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Execl Toolbars update
Great sites thanks for the references.
Bill Dave Peterson wrote: If you're rebuilding a toolbar, you might want to bite the bullet and do it all mechanically. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm In xl2007, those toolbars and menu modifications will show up under the addins. B Nieds wrote: Thanks for your help. Now I have a list of the buttons I need to receate. Bill Dave Peterson wrote: Look at VBA's help for CommandbarControl. You'll see lots of properties to choose from. B Nieds wrote: Thanks Dave that helps. Is there other values I can display as well? Just curious. Thanks Bill Dave Peterson wrote: This may help you: Option Explicit Sub testme() Dim myCB As CommandBar Dim myCtrl As CommandBarControl Dim iCtr As Long Dim DestCell As Range Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 4).Value _ = Array("CommandBar Name", "Menu Name", "Control Caption", "OnAction") Set DestCell = DestCell.Offset(1, 0) For Each myCB In CommandBars For iCtr = 1 To myCB.Controls.Count On Error Resume Next With myCB.Controls(iCtr) If .BuiltIn = False Then If .Type = msoControlPopup Then For Each myCtrl In .Controls DestCell.Value = myCB.Name DestCell.Offset(0, 1).Value = .Caption DestCell.Offset(0, 2).Value = myCtrl.Caption DestCell.Offset(0, 3).Value = myCtrl.OnAction Set DestCell = DestCell.Offset(1, 0) Next myCtrl Else DestCell.Value = myCB.Name DestCell.Offset(0, 2).Value = .Caption DestCell.Offset(0, 3).Value = .OnAction Set DestCell = DestCell.Offset(1, 0) End If End If End With On Error GoTo 0 Next iCtr Next myCB With DestCell.Parent .Select .Range("A1").Select .Range("a2").Select ActiveWindow.FreezePanes = True With .UsedRange.Columns .AutoFilter .AutoFit End With End With End Sub B Nieds wrote: Thanks for the reply. There are several custom buttons created over the years on different toolbars. If I can at least get a listing of all the custom buttons then I would not have to guess if I found them all. This information has to be store somewhere. Thanks Bill Dave Peterson wrote: I wouldn't do it. But if I had the pc with xl97 still available, I'd do this: Open Excel on that pc Make note of all the icons/modifications on the toolbar Use those notes to create a new toolbar on the pc with xl2002. You may find that when you rebuild the toolbar, there'll be icons that are never used and don't need to be added. And when you're building that toolbar searching for the icons you like, you'll see all the new features that can be added to toolbar. But when the toolbar is built the way you like, make a copy of it and store it in a safe location. You never know when something bad will happen--either on purpose or by accident. And by keeping a backup version, it'll be really fast to put the toolbar back the way you like. B Nieds wrote: Hi: My in-laws have decided it was time to upgrade to a new computer (used but new to them) and now have Window XP with MS Office 2002. They were using Office 97 so this is quite a step up for them. They use Excel and Word primarily and to make their life easier I have created (in Excel 97) several custom button (with macros) on several Toolbars. My question is: Where is the information on the Toolbars stored in Excel 97 and can I copy this information over to Excel 2002? Thanks Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Execl print to fit paper | New Users to Excel | |||
Execl problem with numbers | Excel Discussion (Misc queries) | |||
How can I update an MS Access table record from within Execl works | Excel Discussion (Misc queries) | |||
EXECL | Setting up and Configuration of Excel | |||
execl sheets are blinking always | Excel Discussion (Misc queries) |