Is it possible to use a WorksheetFunction when looking at CommandBar
data? What i'm trying to do is search the names of the CommandBars
and delete any that contains a certain string. I've got the function
written to remove the ampersand and could write one to find the
string, but is there a way to use the .Find function to do this?

I've tried a couple different ways and can't think of how to get it to
work... any ideas?


Sub AddMenuItems()

For Each ctl In Application.CommandBars("Worksheet Menu
Bar").Controls
header_name = ctl.Caption
head_count = Len(header_name)
For i = 1 To Len(header_name)
If Right(Left(header_name, i), 1) = "&" Then
header_name = Left(header_name, i - 1) & _
Right(header_name, head_count - i)
End If
Next

'With header_name
' c = header_name.Find( _
' what:=find_item, _
' MatchCase:=False)
'End With
find_item = "String to look for"
a = WorksheetFunction.Find(find_item, header_name, 1)

found_MP = header_name.indexof(find_item)

Next

End Sub

Sub Auto_Open()
AddMenuItems
End Sub


Thanks,
Thedude

VBA has its own equivalent. Look at VBA's help for instr().

(E-Mail Removed) wrote:
>
> Is it possible to use a WorksheetFunction when looking at CommandBar
> data? What i'm trying to do is search the names of the CommandBars
> and delete any that contains a certain string. I've got the function
> written to remove the ampersand and could write one to find the
> string, but is there a way to use the .Find function to do this?
>
> I've tried a couple different ways and can't think of how to get it to
> work... any ideas?
>
> Sub AddMenuItems()
>
> For Each ctl In Application.CommandBars("Worksheet Menu
> Bar").Controls
> header_name = ctl.Caption
> head_count = Len(header_name)
> For i = 1 To Len(header_name)
> If Right(Left(header_name, i), 1) = "&" Then
> header_name = Left(header_name, i - 1) & _
> Right(header_name, head_count - i)
> End If
> Next
>
> 'With header_name
> ' c = header_name.Find( _
> ' what:=find_item, _
> ' MatchCase:=False)
> 'End With
> find_item = "String to look for"
> a = WorksheetFunction.Find(find_item, header_name, 1)
>
> found_MP = header_name.indexof(find_item)
>
> Next
>
> End Sub
>
> Sub Auto_Open()
> AddMenuItems
> End Sub
>
> Thanks,
> Thedude

--

Dave Peterson

Dave,

Do you ever get a solution in your head... then blindly work towards
that solution instead of working towards a much more logical goal?
Thank goodness there are guys like you around here to point people
like me in the right direction.

Thanks,
Thedude

Lots of times.

It's always nice to get another pair (or lots of pairs) of eyes to help.

(E-Mail Removed) wrote:
>
> Dave,
>
> Do you ever get a solution in your head... then blindly work towards
> that solution instead of working towards a much more logical goal?
> Thank goodness there are guys like you around here to point people
> like me in the right direction.
>
> Thanks,
> Thedude

--

Dave Peterson