Findcontrol
 

I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec

Findcontrol
 

Alec,

It works for me, but I'm not sure what you're doing. If you post the
relevant code somebody could probably help.

Doug

"Alec Bowman" wrote in message
...
I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec

Findcontrol
 

Alec,

I can't replicate your problem. What version of Excel are you
using? You do realize that the code you posted will return only
the first control with that tag, not a collection of all controls
with that tag. Perhaps you could post more code, specifically the
code you use to create the controls.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Alec Bowman" wrote in
message ...
I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec

Findcontrol
 

Chip/Doug,

I'm only a simple engineer not a programmer!! What I'm
trying to do is process engineering recording data from
multiple sources. In some cases there are several
different graphs that could be used, thus a popup menu is
appropriate. In other cases a simple Control button
suffices.

At present, I regenerate the menu structure when the
spreadsheet is openned or new data added. I'm not quite
ready to change the approach just yet!

Relevant code is as below. I'm using Excel 97 at home
and Excel 2000 at work.

Thanks in anticipation

Dim MyControls As CommandBarControl
Dim NewButton As CommandBarButton
Dim NewButtonCaption As String
Dim NewButtonAction As String
Dim SubMenu As Object
Dim SubMenuItem As Object
Dim SubMenuCaption1 As String
Dim SubMenuCaption2 As String
Dim SubMenuAction1 As String
Dim SubMenuAction2 As String



Sub Process_Synchro_Recordable_Signal_TRAIN
() 'Process Synchro Training data

etc
etc

NewButtonCaption = "Synchro Training"
SubMenuCaption1 = "Plot
Training" ' Submenu captions if required
SubMenuCaption2 = "Plot Normalised Training"
SubMenuAction1 = "Plot_Synchro_Training"
SubMenuAction2 = "Plot_Synchro_Training_Norm"

AddNewButton

End Sub

Sub AddNewButton()

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption) ' Check if custom button exists
If MyControls Is Nothing
Then ' If the button
does not exist then...
If SubMenuCaption1 = Empty
Then ' If only a single
action is required, create a Control Button for Worksheet
Set NewButton = CommandBars("Worksheet Menu
Bar").Controls("SWMLU").Controls.Add
(Type:=msoControlButton, Befo=1)
With NewButton
.BeginGroup = True
.Caption = NewButtonCaption
.Tag = NewButtonCaption
.FaceId = 0
.OnAction = NewButtonAction
End With
NewButton.Visible = True
ElseIf SubMenuCaption1 < Empty Then
Set SubMenu = CommandBars("Worksheet Menu
Bar").Controls("SWMLU")
With SubMenu
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = NewButtonCaption
End With

Set SubMenuItem = CommandBars("Worksheet
menu bar").Controls("SWMLU").Controls(NewButtonCaption )
With SubMenuItem
.Controls.Add(Type:=msoControlButton,
Befo=1).Caption = SubMenuCaption1
.Controls(SubMenuCaption1).OnAction =
SubMenuAction1
End With

End If
Else
Exit Sub ' Controls already
exist
End If



-----Original Message-----
Alec,

It works for me, but I'm not sure what you're doing. If
you post the
relevant code somebody could probably help.

Doug

"Alec Bowman"
wrote in message
...
I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec




.

Findcontrol
 

Alec,

It's great to create the buttons on the fly. I do think you should just
create the whole popupmenu (or perhaps a toolbar) each time, even if some of
the buttons aren't needed every time. That makes it a lot simpler.

I'd look at John Walkenbach's site, where he has lots of info and examples
about creating custom menus on the fly. The following link would be a good
place to start. It has links to a page that shows how to create custom
menus and one on how to create custom toolbars. They'll show you simpler
and more reliable ways to do what you are trying to.

http://j-walk.com/ss/excel/tips/commandbars.htm

hth,

Doug

"Alec Bowman" wrote in message
...
Chip/Doug,

I'm only a simple engineer not a programmer!! What I'm
trying to do is process engineering recording data from
multiple sources. In some cases there are several
different graphs that could be used, thus a popup menu is
appropriate. In other cases a simple Control button
suffices.

At present, I regenerate the menu structure when the
spreadsheet is openned or new data added. I'm not quite
ready to change the approach just yet!

Relevant code is as below. I'm using Excel 97 at home
and Excel 2000 at work.

Thanks in anticipation

Dim MyControls As CommandBarControl
Dim NewButton As CommandBarButton
Dim NewButtonCaption As String
Dim NewButtonAction As String
Dim SubMenu As Object
Dim SubMenuItem As Object
Dim SubMenuCaption1 As String
Dim SubMenuCaption2 As String
Dim SubMenuAction1 As String
Dim SubMenuAction2 As String



Sub Process_Synchro_Recordable_Signal_TRAIN
() 'Process Synchro Training data

etc
etc

NewButtonCaption = "Synchro Training"
SubMenuCaption1 = "Plot
Training" ' Submenu captions if required
SubMenuCaption2 = "Plot Normalised Training"
SubMenuAction1 = "Plot_Synchro_Training"
SubMenuAction2 = "Plot_Synchro_Training_Norm"

AddNewButton

End Sub

Sub AddNewButton()

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption) ' Check if custom button exists
If MyControls Is Nothing
Then ' If the button
does not exist then...
If SubMenuCaption1 = Empty
Then ' If only a single
action is required, create a Control Button for Worksheet
Set NewButton = CommandBars("Worksheet Menu
Bar").Controls("SWMLU").Controls.Add
(Type:=msoControlButton, Befo=1)
With NewButton
.BeginGroup = True
.Caption = NewButtonCaption
.Tag = NewButtonCaption
.FaceId = 0
.OnAction = NewButtonAction
End With
NewButton.Visible = True
ElseIf SubMenuCaption1 < Empty Then
Set SubMenu = CommandBars("Worksheet Menu
Bar").Controls("SWMLU")
With SubMenu
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = NewButtonCaption
End With

Set SubMenuItem = CommandBars("Worksheet
menu bar").Controls("SWMLU").Controls(NewButtonCaption )
With SubMenuItem
.Controls.Add(Type:=msoControlButton,
Befo=1).Caption = SubMenuCaption1
.Controls(SubMenuCaption1).OnAction =
SubMenuAction1
End With

End If
Else
Exit Sub ' Controls already
exist
End If



-----Original Message-----
Alec,

It works for me, but I'm not sure what you're doing. If
you post the
relevant code somebody could probably help.

Doug

"Alec Bowman"
wrote in message
...
I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec




.

Findcontrol
 

A couple of points.

1) You know where the menu will be, so why do you need to look for it.
On error resume Next
set someControl = CommandBars("Worksheet Menu Bar").Controls("SWMLU")
On Error goto 0
if someControl is Nothing then
'add a control

2) The best method is to just delete the top level menu

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("SWMLU").delete
On Error goto 0
' at this point, you know the menu does not exist, so build it.

--
Regards,
Tom Ogilvy

"Alec Bowman" wrote in message
...
Chip/Doug,

I'm only a simple engineer not a programmer!! What I'm
trying to do is process engineering recording data from
multiple sources. In some cases there are several
different graphs that could be used, thus a popup menu is
appropriate. In other cases a simple Control button
suffices.

At present, I regenerate the menu structure when the
spreadsheet is openned or new data added. I'm not quite
ready to change the approach just yet!

Relevant code is as below. I'm using Excel 97 at home
and Excel 2000 at work.

Thanks in anticipation

Dim MyControls As CommandBarControl
Dim NewButton As CommandBarButton
Dim NewButtonCaption As String
Dim NewButtonAction As String
Dim SubMenu As Object
Dim SubMenuItem As Object
Dim SubMenuCaption1 As String
Dim SubMenuCaption2 As String
Dim SubMenuAction1 As String
Dim SubMenuAction2 As String



Sub Process_Synchro_Recordable_Signal_TRAIN
() 'Process Synchro Training data

etc
etc

NewButtonCaption = "Synchro Training"
SubMenuCaption1 = "Plot
Training" ' Submenu captions if required
SubMenuCaption2 = "Plot Normalised Training"
SubMenuAction1 = "Plot_Synchro_Training"
SubMenuAction2 = "Plot_Synchro_Training_Norm"

AddNewButton

End Sub

Sub AddNewButton()

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption) ' Check if custom button exists
If MyControls Is Nothing
Then ' If the button
does not exist then...
If SubMenuCaption1 = Empty
Then ' If only a single
action is required, create a Control Button for Worksheet
Set NewButton = CommandBars("Worksheet Menu
Bar").Controls("SWMLU").Controls.Add
(Type:=msoControlButton, Befo=1)
With NewButton
.BeginGroup = True
.Caption = NewButtonCaption
.Tag = NewButtonCaption
.FaceId = 0
.OnAction = NewButtonAction
End With
NewButton.Visible = True
ElseIf SubMenuCaption1 < Empty Then
Set SubMenu = CommandBars("Worksheet Menu
Bar").Controls("SWMLU")
With SubMenu
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = NewButtonCaption
End With

Set SubMenuItem = CommandBars("Worksheet
menu bar").Controls("SWMLU").Controls(NewButtonCaption )
With SubMenuItem
.Controls.Add(Type:=msoControlButton,
Befo=1).Caption = SubMenuCaption1
.Controls(SubMenuCaption1).OnAction =
SubMenuAction1
End With

End If
Else
Exit Sub ' Controls already
exist
End If



-----Original Message-----
Alec,

It works for me, but I'm not sure what you're doing. If
you post the
relevant code somebody could probably help.

Doug

"Alec Bowman"
wrote in message
...
I am using Findcontrol to ensure that I only create one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec




.

Findcontrol
 

The reason for creating the buttons when required is that
there are currently approx. 20 different records which
can be examined, each with between 1 and approx 4
different ways of plotting e.g Training Angle, Training
Velocity, Training Acceleration.

We have only got about 10% of the software functionality
in place so far, so we could expect perhaps another 100
or so possible record types.

That's why I would rather not create the whole menu
structure every time.

Will try your suggested link.

Many thanks

Alec


-----Original Message-----
Alec,

It's great to create the buttons on the fly. I do think
you should just
create the whole popupmenu (or perhaps a toolbar) each
time, even if some of
the buttons aren't needed every time. That makes it a
lot simpler.

I'd look at John Walkenbach's site, where he has lots of
info and examples
about creating custom menus on the fly. The following
link would be a good
place to start. It has links to a page that shows how
to create custom
menus and one on how to create custom toolbars. They'll
show you simpler
and more reliable ways to do what you are trying to.

http://j-walk.com/ss/excel/tips/commandbars.htm

hth,

Doug

"Alec Bowman"
wrote in message
...
Chip/Doug,

I'm only a simple engineer not a programmer!! What I'm
trying to do is process engineering recording data from
multiple sources. In some cases there are several
different graphs that could be used, thus a popup menu
is
appropriate. In other cases a simple Control button
suffices.

At present, I regenerate the menu structure when the
spreadsheet is openned or new data added. I'm not
quite
ready to change the approach just yet!

Relevant code is as below. I'm using Excel 97 at home
and Excel 2000 at work.

Thanks in anticipation

Dim MyControls As CommandBarControl
Dim NewButton As CommandBarButton
Dim NewButtonCaption As String
Dim NewButtonAction As String
Dim SubMenu As Object
Dim SubMenuItem As Object
Dim SubMenuCaption1 As String
Dim SubMenuCaption2 As String
Dim SubMenuAction1 As String
Dim SubMenuAction2 As String



Sub Process_Synchro_Recordable_Signal_TRAIN
() 'Process Synchro Training data

etc
etc

NewButtonCaption = "Synchro Training"
SubMenuCaption1 = "Plot
Training" ' Submenu captions if required
SubMenuCaption2 = "Plot Normalised Training"
SubMenuAction1 = "Plot_Synchro_Training"
SubMenuAction2 = "Plot_Synchro_Training_Norm"

AddNewButton

End Sub

Sub AddNewButton()

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption) ' Check if custom button
exists
If MyControls Is Nothing
Then ' If the
button
does not exist then...
If SubMenuCaption1 = Empty
Then ' If only a single
action is required, create a Control Button for
Worksheet
Set NewButton = CommandBars("Worksheet Menu
Bar").Controls("SWMLU").Controls.Add
(Type:=msoControlButton, Befo=1)
With NewButton
.BeginGroup = True
.Caption = NewButtonCaption
.Tag = NewButtonCaption
.FaceId = 0
.OnAction = NewButtonAction
End With
NewButton.Visible = True
ElseIf SubMenuCaption1 < Empty Then
Set SubMenu = CommandBars("Worksheet Menu
Bar").Controls("SWMLU")
With SubMenu
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = NewButtonCaption
End With

Set SubMenuItem = CommandBars
("Worksheet
menu bar").Controls("SWMLU").Controls(NewButtonCaption )
With SubMenuItem
.Controls.Add
(Type:=msoControlButton,
Befo=1).Caption = SubMenuCaption1
.Controls(SubMenuCaption1).OnAction
=
SubMenuAction1
End With

End If
Else
Exit Sub ' Controls already
exist
End If



-----Original Message-----
Alec,

It works for me, but I'm not sure what you're doing.
If
you post the
relevant code somebody could probably help.

Doug

"Alec Bowman"
wrote in message
...
I am using Findcontrol to ensure that I only create
one
instance of a particular command bar button. e.g.

Set MyControls = CommandBars.FindControl
(Tag:=NewButtonCaption)

This worked fine when button was msoControlButton
style
but doesn't when it's a msoControlPopup style.

Any ideas gratefully received

Alec




.



.