Correct Useage of .FindControls

[Guest]

Hey All,

Not able to find any helpful resources on MSDN or by search on this forum, I
am wondering if anyone can help me correctly use FindControls. I am creating
a number of commandBars, but right now, just trying to get one to work now.

I am trying to implement findControls, so that, before I create another
button, it doesn't already exist... Have to check this b/c if vb IDE is
opened while my workbook is opened, some strange things happen with
workbook_open().

Anyway, hopefully I am clear enough. Here is my code (commented line of code
that isn't valid).

Code:

Public myThing5 As Office.CommandBar Public temp As
Office.CommandBarControls Sub Workbook_BeforeClose(Cancel As Boolean)
myThing5.Delete End Sub Sub Workbook_Open() Set temp =
CommandBars.FindControls(, , "test") 'NOT VALID If TypeName(temp) = "Nothing"
Then Set myThing5 = Application.CommandBars.Add("test", msoBarPopup, , True)
End If UserForm1.Show vbModeless End Sub


Thanks!

 

[Bernie Deitrick]

Forget .Findcontrols. Just delete it before adding a new one

Public myThing5 As Office.CommandBar
Public temp As Office.CommandBarControls

Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
myThing5.Delete
End Sub

Sub Workbook_Open()
Application.CommandBars("test").Delete
Set myThing5 = Application.CommandBars.Add("test", msoBarPopup, , True)
UserForm1.Show vbModeless
End Sub

HTH,
Bernie
MS Excel MVP

 

[Ron de Bruin]

Simple delete it first before you add it.

Use

On error resume next
'delete code
on error goto 0

 

[Bernie Deitrick]

The open routine should have this as the first line, too.

On Error Resume Next

Sorry about that.


HTH,
Bernie
MS Excel MVP

 

[Guest]

thanks for the help, but still not quite working...

with this code:

Public myThing5 As Office.CommandBar
Public temp As Office.CommandBarControls

Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
myThing5.Delete
End Sub

Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("test2").Delete
Set myThing5 = Application.CommandBars.Add("test2", msoBarPopup, , True)
UserForm1.Show vbModeless
End Sub


Still doesn't work quite right... Works perfect until IDE is opened. Then if
you go back to the form the button, with the following line of code:
ThisWorkbook.myThing5.ShowPopup

no longer works...

I can attach this very small demo book if you would like to see/test this
phenomenon.

 

[Guest]

meh.. if I throw in a

Private Sub cmdShowMenu_Click()
On Error Resume Next
ThisWorkbook.myThing5.ShowPopup
End Sub

if the IDE is opened... it doesn't crash, it just doesn't work..

 

[Peter T]

You would only qualify myThing5 with Thisworkbook if myThing was declared
public in the Thisworkbook module, and Thisworkbook contains the running
code.

If the popup is only to be used in your userform I would create it as needed
and destroy it immediately after, eg

' userform code
Private myThing5 As Office.CommandBar

Private Sub CommandButton1_Click()
On Error Resume Next
Application.CommandBars("test2").Delete ' just in case
On Error GoTo 0

Set myThing5 = Application.CommandBars.Add("test2", msoBarPopup, , True)
With myThing5.Controls.Add(1, , , , True)
.Caption = "my Macro"
.Visible = True
'.OnAction = "myMacro"
' etc
End With

myThing5.ShowPopup

done:
On Error Resume Next
myThing5.Delete

Exit Sub
errH:
Resume done

End Sub

Regards,
Peter t