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
#&2"
.Controls("Simulation #2").OnAction = "Sim2"
End With
End Sub
Sub SubMenu_Sim3()
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("&NAME").Controls("Output")
With newSubItem
.Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
#&3"
.Controls("Simulation #3").OnAction = "Sim3"
End With
End Sub
Sub SubMenu_Sim4()
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("&NAME").Controls("Output")
With newSubItem
.Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
#&4"
.Controls("Simulation #4").OnAction = "Sim4"
End With
End Sub
Sub SubMenu_Sim5()
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("&NAME").Controls("Output")
With newSubItem
.Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
#&5"
.Controls("Simulation #5").OnAction = "Sim5"
End With
End Sub
Sub SubMenu_Sim6()
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("&NAME").Controls("Output")
With newSubItem
.Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
#&6"
.Controls("Simulation #6").OnAction = "Sim6"
End With
End Sub
Sub SubMenu_Sim7()
Dim newSubItem As Object
Set newSubItem = CommandBars("Worksheet menu bar") _
.Controls("&NAME").Controls("Output")
With newSubItem
.Controls.Add(Type:=msoControlButton, Before:=1).Caption = "Simulation
#&7"
.Controls("Simulation #7").OnAction = "Sim7"
End With
End Sub
===================================================
Private Sub CommandButton1_Click()
Call gen1
End Sub
===================================================
Sub gen1()
'
Sheets("staging").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select
Call copy
Sheets("1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveWindow.SmallScroll Down:=0
Call fix_x
Sheets("1").Select
Call hide
Range("C2").Select
UserForm1.hide
End Sub
===================================================
Sub hide()
'
' This hides the cost section, as we have not received costs
'
Rows("70:94").Select
Selection.EntireRow.Hidden = True
End Sub
===================================================
Sub copy()
Sheets("staging").Select
Rows("1:189").Select
Selection.copy
Range("A1").Select
End Sub
===================================================
Sub fix_x()
'
Sheets("staging").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
End Sub
