I have a excel file that has an application open code in THISWORKBOOK. This
creates and populates a toolbar. However, I would like to be able to add a
(Pareto) button as part of the code that is triggered when one of the
original buttons on the bar is pressed. The way it is now, all pertinant
buttons are on the toolbar.
The reason I want to change it is because, while doing testing with a
coworker, he pressed the pareto button before the data in the sheet was
ready. One of the other buttons readies the input data for the pareto button.

To attempt to restate this, one of the buttons that is currently created
upon open, I want not to be visible until certain other macros are run.

Is it possible to go back and recreate an application event after it has run?

Why not just disable it and have your button that prepares the sheet enable
it as the last act.

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have a excel file that has an application open code in THISWORKBOOK.
This
creates and populates a toolbar. However, I would like to be able to add
a
(Pareto) button as part of the code that is triggered when one of the
original buttons on the bar is pressed. The way it is now, all pertinant
buttons are on the toolbar.
The reason I want to change it is because, while doing testing with a
coworker, he pressed the pareto button before the data in the sheet was
ready. One of the other buttons readies the input data for the pareto
button.

To attempt to restate this, one of the buttons that is currently created
upon open, I want not to be visible until certain other macros are run.

Is it possible to go back and recreate an application event after it has
run?

I like the suggestion, Tom. But getting the code to add to the commandbar,
or even change the status of something on the bar is the problem. My
attempts to add code to my macro that will change the commandbar created as
an event is where I am struggling.

This is the code that I have tried:

With cbrCommandBar.Controls

Set cbcTypeParetoButton = .Add(msoControlButton)
'set properties of the Pareto command button.
With cbcTypeParetoButton
..Enabled = True
End With


"Tom Ogilvy" wrote:

Why not just disable it and have your button that prepares the sheet enable
it as the last act.

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have a excel file that has an application open code in THISWORKBOOK.
This
creates and populates a toolbar. However, I would like to be able to add
a
(Pareto) button as part of the code that is triggered when one of the
original buttons on the bar is pressed. The way it is now, all pertinant
buttons are on the toolbar.
The reason I want to change it is because, while doing testing with a
coworker, he pressed the pareto button before the data in the sheet was
ready. One of the other buttons readies the input data for the pareto
button.

To attempt to restate this, one of the buttons that is currently created
upon open, I want not to be visible until certain other macros are run.

Is it possible to go back and recreate an application event after it has
run?

Sub DDDD()
Dim cbrCommandBar As CommandBar
Dim cbcTypeParetoButton As CommandBarButton
Set cbrCommandBar = Application.CommandBars("Custom 1")
With cbrCommandBar.Controls
On Error Resume Next
.Item("MyButton").Delete
On Error GoTo 0
Set cbcTypeParetoButton = .Add(msoControlButton)
End With
'set properties of the Pareto command button.
With cbcTypeParetoButton
.Enabled = True
.FaceId = 330
.Caption = "MyButton"
' .OnAction =
End With

End Sub

Worked fine for me.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
I like the suggestion, Tom. But getting the code to add to the
commandbar,
or even change the status of something on the bar is the problem. My
attempts to add code to my macro that will change the commandbar created
as
an event is where I am struggling.

This is the code that I have tried:

With cbrCommandBar.Controls

Set cbcTypeParetoButton = .Add(msoControlButton)
'set properties of the Pareto command button.
With cbcTypeParetoButton
.Enabled = True
End With


"Tom Ogilvy" wrote:

Why not just disable it and have your button that prepares the sheet
enable
it as the last act.

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have a excel file that has an application open code in THISWORKBOOK.
This
creates and populates a toolbar. However, I would like to be able to
add
a
(Pareto) button as part of the code that is triggered when one of the
original buttons on the bar is pressed. The way it is now, all
pertinant
buttons are on the toolbar.
The reason I want to change it is because, while doing testing with a
coworker, he pressed the pareto button before the data in the sheet
was
ready. One of the other buttons readies the input data for the pareto
button.

To attempt to restate this, one of the buttons that is currently
created
upon open, I want not to be visible until certain other macros are
run.

Is it possible to go back and recreate an application event after it
has
run?

Tom,
I don't understand how to incorporate your code. Should I be inserting this
at the point where I want the command bar to have the new button installed
(on the already existing toolbar)?
How does this code know when to add the new button?

TIA

"Tom Ogilvy" wrote:

Sub DDDD()
Dim cbrCommandBar As CommandBar
Dim cbcTypeParetoButton As CommandBarButton
Set cbrCommandBar = Application.CommandBars("Custom 1")
With cbrCommandBar.Controls
On Error Resume Next
.Item("MyButton").Delete
On Error GoTo 0
Set cbcTypeParetoButton = .Add(msoControlButton)
End With
'set properties of the Pareto command button.
With cbcTypeParetoButton
.Enabled = True
.FaceId = 330
.Caption = "MyButton"
' .OnAction =
End With

End Sub

Worked fine for me.

--
Regards,
Tom Ogilvy


"Papa Jonah" wrote in message
...
I like the suggestion, Tom. But getting the code to add to the
commandbar,
or even change the status of something on the bar is the problem. My
attempts to add code to my macro that will change the commandbar created
as
an event is where I am struggling.

This is the code that I have tried:

With cbrCommandBar.Controls

Set cbcTypeParetoButton = .Add(msoControlButton)
'set properties of the Pareto command button.
With cbcTypeParetoButton
.Enabled = True
End With


"Tom Ogilvy" wrote:

Why not just disable it and have your button that prepares the sheet
enable
it as the last act.

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have a excel file that has an application open code in THISWORKBOOK.
This
creates and populates a toolbar. However, I would like to be able to
add
a
(Pareto) button as part of the code that is triggered when one of the
original buttons on the bar is pressed. The way it is now, all
pertinant
buttons are on the toolbar.
The reason I want to change it is because, while doing testing with a
coworker, he pressed the pareto button before the data in the sheet
was
ready. One of the other buttons readies the input data for the pareto
button.

To attempt to restate this, one of the buttons that is currently
created
upon open, I want not to be visible until certain other macros are
run.

Is it possible to go back and recreate an application event after it
has
run?