[RESOLVED] Can't Get Menu Code to Run

[pgag45]

are you saying this should work? in workbook_open

myThing5.Delete
Set myThing5 = Application.CommandBars.Add("paul3", msoBarPopup, , True)
UserForm1.Show vbModeless

[RobDog888]

No.

What if its the first run of your workbook? The command bar will not exist so referencing it will generate an error.

[pgag45]

So something like

Set temp = CommandBars.FindControls(msoControlButton, , "paul6")
    If temp.Count = 0 Then
        Set myThing5 = Application.CommandBars.Add("paul6", msoBarPopup, , True)
    ElseIf temp.Count > 0 Then
        myThing5.Delete
        Set myThing5 = Application.CommandBars.Add("paul6", msoBarPopup, , True)
    End If
    UserForm1.Show vbModeless

except that findControls method isn't working. Any help on using the findControls method? tried using MSDN but didn't help out too much.

[RobDog888]

More like this...

Set temp = CommandBars.FindControls(msoControlButton, , "paul6")
If TypeName(temp) = "Nothing" Then
    Set myThing5 = Application.CommandBars.Add("paul6", msoBarPopup, , True)
End If
UserForm1.Show vbModeless

[pgag45]

Set temp = CommandBars.FindControls(msoControlButton, , "paul6") isn't working, and msdn is of no help.

[RobDog888]

Why are you trying to look for a button when you are initially going to be looking for your commandbar?

[pgag45]

b/c button made the most sense out of these:

msoControlActiveX
msoControlAutoCompleteCombo
msoControlButton
msoControlButtonDropdown
msoControlButtonPopup
msoControlComboBox
msoControlCustom
msoControlDropdown
msoControlEdit
msoControlExpandingGrid
msoControlGauge
msoControlGenericDropdown
msoControlGraphicCombo
msoControlGraphicDropdown
msoControlGraphicPopup
msoControlGrid
msoControlLabel
msoControlLabelEx
msoControlOCXDropDown
msoControlPane
msoControlPopup
msoControlSpinner
msoControlSplitButtonMRUPopup
msoControlSplitButtonPopup
msoControlSplitDropdown
msoControlSplitExpandingGrid
msoControlWorkPane

[RobDog888]

Well as you can see the findcontrol function takes arguments of -
FindControl([Type], [Id], [Tag], [Visible], [Recursive])
You can leave the control type blank but when you added your command bar did you just give it a name and search for it by that name.

[pgag45]

Well this isn't working either...

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(, , "paul5512")
If TypeName(temp) = "Nothing" Then
    Set myThing5 = Application.CommandBars.Add("paul5512", msoBarPopup, , True)
End If
UserForm1.Show vbModeless
End Sub

error "object var not set" on findcontrols line

[RobDog888]

Not sure why this is so difficult but try this...

Set temp = CommandBars.Item("paul5512")

instead.

Are you looking for a button or a command bar?

[pgag45]

Yeah same error w/ that.

I'm looking for w/e myThing5 is... a commandBar, I think. Is my declaration of temp incorrect?

[RobDog888]

Well if its temp then thats different then what you had it as a few posts ago. Please post your current code so we can work with it.

[pgag45]

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(, , "paul")
Set temp = CommandBars.Item("paul")
If TypeName(temp) = "Nothing" Then
    Set myThing5 = Application.CommandBars.Add("paul", msoBarPopup, , True)
End If
UserForm1.Show vbModeless
End Sub

[RobDog888]

Ok its Temp so shouldnt be looking for it upon load then. Should be able to just add each time. Are you getting any errors with this code?

[pgag45]

Well I have to check if it's been created b/c of that strange thing that happens when IDE is opened.

and the above code doesn't work, as

Set temp = CommbandBars.Item("paul") gets error 91

[RobDog888]

Add error handling for that part.

[pgag45]

I don't think error handling is the answer. I need to find if this commandbar exists already, and need a method that works. I have no working method, so error would always be thrown.

[RobDog888]

That is what error handling is for.

If it errors then you know it exists when .Add or Set. Then you can resume next and continue on. If it doesnt exist then it will be all good.

[pgag45]

Tried this out.. Works until IDE is opened.. Then when going back to open the form crashes again.


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(, , "paul")
'On Error Resume Next
'Set temp = CommandBars.Item("paull")
'If TypeName(temp) = "Nothing" Then
' Set myThing5 = Application.CommandBars.Add("paull", msoBarPopup, , True)
'End If
'UserForm1.Show vbModeless
'On Error GoTo 0

On Error GoTo errHandler:
Set myThing5 = Application.CommandBars.Add("23", msoBarPopup, , True)
UserForm1.Show vbModeless
errHandler:
UserForm1.Show vbModeless
Resume Next
End Sub

[pgag45]

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(, , "paul")
'On Error Resume Next
'Set temp = CommandBars.Item("paull")
'If TypeName(temp) = "Nothing" Then
'    Set myThing5 = Application.CommandBars.Add("paull", msoBarPopup, , True)
'End If
'UserForm1.Show vbModeless
'On Error GoTo 0
 
On Error GoTo errHandler:
Set myThing5 = Application.CommandBars.Add("23", msoBarPopup, , True)
UserForm1.Show vbModeless
errHandler:
    UserForm1.Show vbModeless
Resume Next
End Sub

[pgag45]

another unsuccessful try... this sucks

Public myThing5 As Office.CommandBar
'Public temp As Office.CommandBarControls
 
Sub Workbook_BeforeClose(Cancel As Boolean)
myThing5.Delete
End Sub
 
Sub Workbook_Open()
On Error GoTo errHandler:
Set myThing5 = Application.CommandBars.Add("2245", msoBarPopup, , False)
UserForm1.Show vbModeless
errHandler:
    myThing5.Delete
    Set myThing5 = Application.CommandBars.Add("2245", msoBarPopup, , False)
    UserForm1.Show vbModeless
End Sub

[pgag45]

only thing that is not working, is when I try to open IDE while my workbook is open.. When that happens and I try to reopen my userForm... it crashes

[RobDog888]

Try this code as it doesnt error at all for me either with opening the IDE or not.

Option Explicit
 
Public myThing5 As Office.CommandBar
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If TypeName(myThing5) <> "Nothing" Then
        myThing5.Delete
    End If
End Sub
 
Private Sub Workbook_Open()
    On Error GoTo errHandler
    Set myThing5 = Application.CommandBars.Add("2245", msoBarPopup, , False)
    UserForm1.Show vbModeless
    Exit Sub
errHandler:
    If TypeName(myThing5) = "Nothing" Then
        Set myThing5 = Application.CommandBars.Item("2245")
    Else
        MsgBox "Unexpected Error"
    End If
    If UserForm1.Visible = False Then
        UserForm1.Show vbModeless
    End If
End Sub

[pgag45]

Great... Actually arrived at a similar piece of code through some error handling as well, but yours works fine too..

thanks for all the help, I appreciate it much!