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?
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?
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?
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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.
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
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
[RESOLVED] Can't Get Menu Code to Run
Reply With Quote
