[RESOLVED] Can't Get Menu Code to Run

Hey all,

I am trying to implement a version of RobDog888's Menu FAQ into my own program, but I keep getting the same error. RobDog ensures me that his code is working and he has run it. But I just can't figure out what is wrong. I have the necessary references, and RobDog's exact file won't even run on my system. The file is on this FAQ (http://www.vbforums.com/showthread.php?t=402050) ... could some people test it out, and see if it is running on their system? Or if anyone knows what could be going wrong?

Thanks

Oh just thought if you have all Office Updates and Service packs?

Yes...both are updated.

hrmm..

yup it works on all my other test computers as well, just not this one... using excel 11.8134.8132 sp2 .... Same references... I just can't figure this out, what else would make a difference?

Its your Office install then. Do a Detect and Repair from the help menu.

Actually kind of just figured it out..

try doing this...

open workbook (your window pops up automatically ... works fine)
go to that form... run that (works fine)
but instead of hitting "close" hit the red "X"
now run the form again.
should get error 5

so if you don't call mocbimage.delete, next time you run it crashes.. I think I may have saved it like that, so I'm gonna test some stuff out..

but could you try running the above and see if its crashes you too?

Is there a way to not make these temporary? So we don't have to worry about deleting them?

hrmm... I'm unable to set a command bar now, anytime I try I get an invalid argument error 5... and I can't delete something if it's not set.

What is it that you are trying to do because the example runs completely fine under several OS's and Excel versions. If you have modified it then I need to know what you are trying to do.

Try this....

open workbook (your window pops up automatically ... works fine)
go to that form... run that (works fine)
but instead of hitting "close" hit the red "X"
now run the form again.
should get error 5

Ok, now that you have explained how you were generating the error I have been able to fix it.

When you close the UserForm from the 'x' button the commandbar doesnt get removed as the removal code is in the button click event. So to fix it we just move that code to the UserForm_QueryClose event.

Replace the button click code with all this code.

Code:

---

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    moCBImage.Delete
    Unload Me
End Sub

---

Ok... but I'm still stuck.. I can't delete the button w/o setting it.. and can't set it without error 5... so do I basically need to reinstall office?

Why are you deleting it?

The fix is a simple code replacement.

b/c my excel (on my work computer, which I'm not on right now) is screwed up... I can't set any CommandButton without getting error 5...

Close it and reopen it.

Ok I'll try some testing on Monday... I think something is bugged, but I'll post then, I don't have access to that computer til then..

None the less, thanks much for the continuing help RobDog

np, but if you are having issues with Excel itself then perhaps a Detect and Repair will help or a Office Update.

Ok, so I just rebooted my computer... tried running your new FAQ file, and as soon as I open it (open Workbook is called to show the form), I get error 5, illegal procedure or argument.

Tried detect and repair plus office update, and still bugged.

Alright, finally fixed it... Had to rename and delete some stuff, as the old names had never been released from memory..

One last question rob... Should the commandbar.delete be in UserForm_QueryClose or UserForm_Terminate?

What is the diffn b/w the 2? Only thing I am worried about now, is what if my program crashes? Are either of these called?

If your program crashes then no code will be called or run. You need error handling to reduce that effect. ;)

QueryClose is the proper place.

Quote:

---

The Terminate event occurs after the object is unloaded. The Terminate event isn't triggered if the instances of the UserForm or class are removed from memory because the application terminated abnormally. For example, if your application invokes the End statement before removing all existing instances of the class or UserForm from memory, the Terminate event isn't triggered for that class or UserForm.

---

hrmmm.. Ok

Is there anyway to create these menu bars as so they do not need to be deleted at all?

Yes, when .Add'ing them pass the True parameter to designate them as Temporary. Then for other instances your bar wont be shown unless it calls your macro code to add them. Also, when closing the workbook with them in it it will automatically destroy them for you. This is the best method when needing bars for only a particular workbook and not globally across Excel.

I don't think I was clear... Is there a way to create a commandBar that doesn't need to be deleted on close or terminate?

We are creating temporary bars right now, but they still need to be deleted... I tried setting temp = false, but those still need to be deleted... Seems kind of strange to me, that these need to be manually deleted

You shouldnt have to but it doesnt hurt to explicitly destroy the buttons. If you are completely closing the workbook then it will destroy them automatically. But if you keep the workbook open then it will not.

So

Set myThing = Application.CommandBars.Add("test88", msoBarPopup, , False)

will still need to be deleted, if they close my form and then re-open it?

No, you can leave it created as it is only destroyed if they close the workbook that the userform is contained in. This is for workbook closures and not userforms ;)

but I would need to create them in workbook_open instead of userform_init?

oh my finally got it all working...

heres my workbook code if anyone wants to see it

Code:

---

Public myThing3 As Office.CommandBar

Sub Workbook_BeforeClose(Cancel As Boolean)
    myThing3.Delete
End Sub

Sub Workbook_Open()
    Set myThing3 = Application.CommandBars.Add("test90", msoBarPopup, , False)
    frmMain.Show vbModeless
End Sub

---

thanks for all the help rob... definitely took me a while to completely understand how this all works. Kind of a confusing little class, but not too hard once you mess around with it enough.

hrmm.. perhaps not so resolved.

all is working fine.. until I open vb editor... then when I go back to main, I get error 5 again

When you open the VBA IDE are you stopping the code execution? Error what line?

Open my workbook following code is executed and works fine.

Code:

---

Public myThing5 As Office.CommandBar

Sub Workbook_BeforeClose(Cancel As Boolean)
    myThing5.Delete
End Sub

Sub Workbook_Open()
    Set myThing5 = Application.CommandBars.Add("test93", msoBarPopup, , False)
    frmMain.Show vbModeless
End Sub

---

works perfect, until... ide is opened. Even if no changes are made to the code, when trying to run main again, run-time error 91 pops up (variable not set)

with line

Code:

---

Private Sub cmdFile_Click()
ThisWorkbook.myThing5.ShowPopup
End Sub

---

which is a button in my main form that shows my command bar.

Can you attach your workbook or a demo example of it?

Yes, let me make a demo real quick

Ok, and place comments where the error line is orruring and how you duplicate it.

Check the sheet for instructions to crash it..

Figured it out.

When you go into design mode in the IDE you are stopping your code and not allowing the form to retain its event link for the popup. Now you would think that just restarting the workbook by reopening it would work, it does but only after this next comment, when the code goes to run and .Add your commendbar it already exists since it didnt get deleted from the app being stopped.

Now to fix it just delete the command bar or reset them from Excels GUI.

Code:

---

Application.CommandBars("test99").Delete

---

Use the .FindControl first before attempting to .Add it and you will be good regardless of any crashing. If you changed the .Add to a temporary that should work just as well.

Little confused as to what you are saying... You mean before I add it in Workbook_Open, to check if an instance of it already exists, then delete it?

And assuming the above is correct, I am having trouble implementing .FindControl.

The below doesn't work..

Code:

---

set temp = Application.CommandBars.FindControls(CommandBar, , "test970")
    If Not temp = Null Then
        myThing5.Delete
    End If
    Set myThing5 = Application.CommandBars.Add("test970", msoBarPopup, , False)
    UserForm1.Show vbModeless

---

what does .findControl return if it doesn't exist? Or should I be using .FindControls?

Yes and no. If it finds it then there is no reason ot add it again or delete it as its reay to go. Also, if it was a temporary commandbar then you could do the .Add everytime as each time it will not exist.

.Findcontrols, yes.

Just tested making the commandbar temp... and still getting the same error.

Will try implementing .FindControls now

are you saying this should work? in workbook_open

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

No.

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

So something like

vb Code:

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.

More like this...

Code:

---

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

---

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

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

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

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.

Well this isn't working either...

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(, , "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

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?

Yeah same error w/ that.

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

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.

vb 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(, , "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

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?

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

Add error handling for that part.

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.

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.

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

vb 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(, , "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