Prev: C# COM in Excel: Avoid using Marshal
Next: Usage of serial port in VBA ( exel )
From: Matt on 25 Apr 2007 12:58
Thanks Dave for all the help! I am going with corruption and move on with
life! Thanks again for all the help!!

"Dave Peterson" wrote:

> I don't understand why you have an option to create the menu inside an option in
> that menu (activeateinput is the .onaction for the i&nput routine.
>
> If you discount my confusion, I still don't see anything in your code that runs
> when the workbook closes--or when excel closes. You may have an event that
> calls one of these routines that calls one of the routines that calls one of the
> routines??????
>
> Anyway, you can clean up some of your code by using something like this:
>
> Option Explicit
> Sub CreateMenu()
>
> Dim CustBar As CommandBar
> Dim oControl As CommandBarControl
> Dim ctrl As CommandBarControl
>
> Dim myMacs As Variant
> Dim myCaps As Variant
> Dim iCtr As Long
>
> Set CustBar = Application.CommandBars("Worksheet Menu Bar")
>
> On Error Resume Next
> CustBar.Controls("&Name").Delete
> On Error GoTo 0
>
> Set oControl = CustBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
>
> With oControl
> .Caption = "&NAME"
> End With
>
> myCaps = Array("Simulation #&1", _
> "Simulation #&2", _
> "Simulation #&3", _
> "Simulation #&4", _
> "Simulation #&5", _
> "Simulation #&6", _
> "Simulation #&7", _
> "&Introduction", _
> "I&nput", _
> "&Output", _
> "&Clear")
>
> myMacs = Array("Sim1", _
> "Sim2", _
> "Sim3", _
> "Sim4", _
> "Sim5", _
> "Sim6", _
> "Sim7", _
> "activateintro", _
> "activateinput", _
> "donothing", _
> "delete_prior")
>
> If UBound(myMacs) <> UBound(myCaps) Then
> MsgBox "Design error!"
> Exit Sub
> End If
>
> For iCtr = LBound(myMacs) To UBound(myMacs)
> Set ctrl = oControl.Controls.Add(Type:=msoControlButton, _
> temporary:=True)
> With ctrl
> .Caption = myCaps(iCtr)
> .OnAction = "'" & ThisWorkbook.Name & "'!" & myMacs(iCtr)
> End With
> Next iCtr
> End Sub
>
> Make sure you put the macro names and the macro captions in the correct order!
>
> Matt wrote:
> >
> > Hey Dave,
> > We tried changing the code to the following, and the file didn't crash on
> > closing. Every subsequent try crashes. Does anything get cached in Excel that
> > might cause something like that? The memory is cleared too which is weird, so
> > is something about the form cached between uses regardless of a memory
> > clearing? Thanks! Sorry for all the dang questions!
> >
> > This is everything:
> >
> > ===================================================
> >
> >
> >
> > Sub activateinput()
> >
> > Call CreateMenu
> >
> > UserForm1.Show
> >
> > End Sub
> >
> >
> >
> > ===================================================
> >
> >
> >
> > Sub CreateMenu()
> >
> > '
> >
> > 'THE DROP-DOWN CODE BEGINS HERE
> >
> > Dim custBar, oControl
> >
> > Set custBar = CommandBars("Worksheet Menu Bar")
> >
> >
> >
> > For Each oControl In custBar.Controls
> >
> > If oControl.Caption = "&NAME" Then
> >
> > oControl.Delete
> >
> > End If
> >
> > Next
> >
> > Call CreateMenu2
> >
> > End Sub
> >
> >
> >
> > ===================================================
> >
> >
> >
> > Sub CreateMenu2()
> >
> >
> >
> > Dim custBar As Object
> >
> > Set custBar = CommandBars("Worksheet Menu Bar").Controls. _
> >
> > Add(Type:=msoControlPopup)
> >
> > With custBar
> >
> > .Caption = "&NAME"
> >
> > End With
> >
> >
> >
> > Call CreateClearMenu
> >
> >
> >
> > Call SubMenu_Output
> >
> >
> >
> > Call CreateInputMenu
> >
> >
> >
> > Call CreateIntroMenu
> >
> >
> >
> > Call SubMenu_Sim7
> >
> >
> >
> > Call SubMenu_Sim6
> >
> >
> >
> > Call SubMenu_Sim5
> >
> >
> >
> > Call SubMenu_Sim4
> >
> >
> >
> > Call SubMenu_Sim3
> >
> >
> >
> > Call SubMenu_Sim2
> >
> >
> >
> > Call SubMenu_Sim1
> >
> >
> >
> >
> >
> > End Sub
> >
> >
> >
> > ===================================================
> >
> >
> >
> > Sub CreateIntroMenu()
> >
> > With CommandBars("Worksheet menu bar").Controls("&NAME")
> >
> > .Controls.Add(Type:=msoControlButton, Before:=1).Caption =
> > "&Introduction"
> >
> > .Controls("Introduction").OnAction = "activateintro"
> >
> > End With
> >
> > End Sub
> >
> >
> >
> > ===================================================
> >
> >
> >
> > Sub CreateInputMenu()
> >
> > With CommandBars("Worksheet menu bar").Controls("&NAME")
> >
> > .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "I&nput"
> >
> > .Controls("Input").OnAction = "activateinput"
> >
> > End With
> >
> > End Sub
> >
> > Sub SubMenu_Output()
> >
> > Dim newSub As Object
> >
> > Set newSub = CommandBars("Worksheet menu bar").Controls("&NAME")
> >
> > With newSub
> >
> > .Controls.Add(Type:=msoControlPopup, Before:=1).Caption = "&Output"
> >
> > End With
> >
> > End Sub
> >
> >
> >
> > ===================================================
> >
> >
> >
> > Sub CreateClearMenu()
> >
> > With CommandBars("Worksheet menu bar").Controls("&NAME")
> >
> > .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "&Clear"
> >
> > .Controls("Clear").OnAction = "delete_prior"
> >
> > End With
> >
> > End Sub
> >
> > Sub SubMenu_Sim1()
> >
> > Dim newSubItem As Object
> >
> > Set newSubItem = CommandBars("Worksheet menu bar") _
> >
> > .Controls("&NAME").Controls("Output")
> >
> > With newSubItem
> >
> > .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
> > #&1"
> >
> > .Controls("Simulation #1").OnAction = "Sim1"
> >
> > End With
> >
> > End Sub
> >
> > Sub SubMenu_Sim2()
> >
> > Dim newSubItem As Object
> >
> > Set newSubItem = CommandBars("Worksheet menu bar") _
> >
> > .Controls("&NAME").Controls("Output")
> >
> > With newSubItem
> >
> > .Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
First  |  Prev  | 
Pages: 1 2 3 4
Prev: C# COM in Excel: Avoid using Marshal
Next: Usage of serial port in VBA ( exel )