Hi all
Excel 2K
I need to loop through a number of custom controls and enable some of them.
I can identify these controls with their .tag property which will always
begin with "AVV"
So I thought I could use the .FindControl method and the Like operator, but
I cannot figure out how to do it.
Could somebody explain how to achieve this?
TIA
Regards
Pascal

dim ctrl as Control
For Each Ctrl in Controls

If ctrl.Tag LIKE "TVV*" Then
ctrl.Enabled = True
End If

Next



HTH
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hi all
Excel 2K
I need to loop through a number of custom controls and
enable some of them.
I can identify these controls with their .tag property
which will always
begin with "AVV*"


So I thought I could use the .FindControl method and the
Like operator, but
I cannot figure out how to do it.
Could somebody explain how to achieve this?
TIA
Regards
Pascal



.

Hi Tom
Thank you very much for your answer.
Since I was only looking for controls within the Worksheet Menu Bar,I
eventually found a "lighter" solution with the following:
For Each Cb In Application.CommandBars("Worksheet Menu Bar").Controls
If Cb.Caption = "Options &Cotation" Then
For Each Ctl In Cb.Controls
If Ctl.Type = 10 Then
For Each SousCtl In Ctl.Controls
If SousCtl.Tag Like "A*" Then
SousCtl.Enabled = True
End If
Next SousCtl
End If
Next Ctl
End If
Next Cb
Thanks again Tom
Regards
Pascal

"Tom Ogilvy" a écrit dans le message de
...
Keywords: commandbars, recursive, search, tag

Sub SearchControls1()
Dim varr As Variant
Dim cbar As CommandBar
Dim cbCtrl As CommandBarControl
For Each cbar In Application.CommandBars
For Each cbCtrl In cbar.Controls
If cbCtrl.Type < msoControlPopup And _
cbCtrl.Type < msoControlGraphicPopup And _
cbCtrl.Type < msoControlButtonPopup And _
cbCtrl.Type < msoControlSplitButtonPopup And _
cbCtrl.Type < msoControlSplitButtonMRUPopup _
Then
If cbCtrl.Tag Like "AVV*" Then
If IsArray(varr) Then
ReDim Preserve varr(1 To UBound(varr) + 1)
Else
ReDim varr(1 To 1)
End If
Set varr(UBound(varr)) = cbCtrl
End If
Else
If cbCtrl.Tag Like "ABC*" Then
If IsArray(varr) Then
ReDim Preserve varr(1 To UBound(varr) + 1)
Else
ReDim varr(1 To 1)
End If
Set varr(UBound(varr)) = cbCtrl
End If
SearchPopup cbCtrl, varr
End If
Next
Next
For i = 1 To UBound(varr)
Debug.Print varr(i).Caption, varr(i).Tag
Next
End Sub

Public Sub SearchPopup(ctrl, varr)
Dim cbCtrl As CommandBarControl
For Each cbCtrl In ctrl.Controls
If cbCtrl.Type < msoControlPopup And _
cbCtrl.Type < msoControlGraphicPopup And _
cbCtrl.Type < msoControlButtonPopup And _
cbCtrl.Type < msoControlSplitButtonPopup And _
cbCtrl.Type < msoControlSplitButtonMRUPopup _
Then
If cbCtrl.Tag Like "AVV*" Then
If IsArray(varr) Then
ReDim Preserve varr(1 To UBound(varr) + 1)
Else
ReDim varr(1 To 1)
End If
Set varr(UBound(varr)) = cbCtrl
End If
Else
If cbCtrl.Tag Like "ABC*" Then
If IsArray(varr) Then
ReDim Preserve varr(1 To UBound(varr) + 1)
Else
ReDim varr(1 To 1)
End If
Set varr(UBound(varr)) = cbCtrl
End If
SearchPopup cbCtrl, varr
End If
Next

End Sub


Should do what you want.

--
Regards,
Tom Ogilvy

"papou" wrote in message
...
Hi all
Excel 2K
I need to loop through a number of custom controls and enable some of
them.
I can identify these controls with their .tag property which will always
begin with "AVV"
So I thought I could use the .FindControl method and the Like operator,
but
I cannot figure out how to do it.
Could somebody explain how to achieve this?
TIA
Regards
Pascal