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

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

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

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

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

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

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

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

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