I'm using the Parameter property of the controls to enable/disable them along
the lines of a concept from "Prof. Excel Dev.". Since the command bar parent
has no such property, and I want to keep the code reusable, after I've set
all the controls I'm trying the code below, which is documented better then
it works (it doesn't call itself recursively)

Questions, criticisms and comments welcome - there must be an easier way to
do this...

'---------------------------------------------------------------------------------------
' Procedure : hasAtLeastOneControlEnabled
' Purpose : Determines if the passed command bar has at least one control
enabled. If the
' bar has even one control enabled then the bar should be
enabled; if no
' controls enabled then the bar needs to be disabled.
' Inputs : CommandBar the command bar of interest
' Outputs : Boolean True if one control is enabled (the first one
found is enough).
' Precon(s) : The controls have already been enabled/disabled based on
context.
' DateTime : 2/1/2006
' Author : EBF
'---------------------------------------------------------------------------------------
'
Private Function hasAtLeastOneControlEnabled(aParentBar As
Office.CommandBar) As Boolean
On Error Resume Next
Dim bResult As Boolean
Dim ctl As Office.CommandBarControl
For Each ctl In aParentBar.Controls
If TypeOf ctl Is CommandBarPopup Then
bResult = hasAtLeastOneControlEnabled(ctl) <====this doesn't
recurse here
Else
If ctl.Enabled Then
bResult = True
hasAtLeastOneControlEnabled = bResult
Exit Function
End If
End If
Next ctl
End Function

Eric,

the function parameter s/b passed ByVal and defined as a generic
object, since you're passing it both commandbar and commandbarpopup
objects

i've added a check that the popup itself is enabled and simplified the
code.

Note: For this code to work in xl97 change TypeOf Is to
lcase$(TypeName(octl)) = "commandbarpopup"


Function BarHasEnabledControls(ByVal oParent As Object) As Boolean
Dim oCtl As CommandBarControl
For Each oCtl In oParent.Controls
If TypeOf oCtl Is CommandBarPopup And oCtl.Enabled Then
BarHasEnabledControls = BarHasEnabledControls(oCtl)
Else
BarHasEnabledControls = oCtl.Enabled
End If
If BarHasEnabledControls Then Exit Function
Next
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Eric Fingerhut wrote :

I'm using the Parameter property of the controls to enable/disable
them along the lines of a concept from "Prof. Excel Dev.". Since the
command bar parent has no such property, and I want to keep the code
reusable, after I've set all the controls I'm trying the code below,
which is documented better then it works (it doesn't call itself
recursively)

Questions, criticisms and comments welcome - there must be an easier
way to do this...

'---------------------------------------------------------------------
------------------ ' Procedure : hasAtLeastOneControlEnabled
' Purpose : Determines if the passed command bar has at least one
control enabled. If the
' bar has even one control enabled then the bar
should be enabled; if no
' controls enabled then the bar needs to be disabled.
' Inputs : CommandBar the command bar of interest
' Outputs : Boolean True if one control is enabled (the first
one found is enough).
' Precon(s) : The controls have already been enabled/disabled based
on context.
' DateTime : 2/1/2006
' Author : EBF
'---------------------------------------------------------------------
------------------ '
Private Function hasAtLeastOneControlEnabled(aParentBar As
Office.CommandBar) As Boolean
On Error Resume Next
Dim bResult As Boolean
Dim ctl As Office.CommandBarControl
For Each ctl In aParentBar.Controls
If TypeOf ctl Is CommandBarPopup Then
bResult = hasAtLeastOneControlEnabled(ctl) <====this
doesn't recurse here
Else
If ctl.Enabled Then
bResult = True
hasAtLeastOneControlEnabled = bResult
Exit Function
End If
End If
Next ctl
End Function