Command Box jams macro

[retseort]

I have some code that I am pretty sure I got off this board or at leas
some of it. It creates a command box that continues the macro once
action is taken.

For some reason it jams at the following point in the code. WHY? Th
interesting thing is if I change the word stop to anything else it wil
work once then jam again. I am using excel 2000

'Sets the toolbar's name
.Name = "Stop"


Here is the entire code....

Sub CreatePauseToolbar()

Dim NewBar As Object

'Creates the toolbar, and sets a variable to easily forma
it
Set NewBar = CommandBars.Add

With NewBar

'Sets the toolbar's name
.Name = "Stop"

'Makes the toolbar visible
.Visible = True

'Adds a button to the toolbar
.Controls.Add Type:=msoControlButton

With .Controls(1)

'Sets the style of the button to text only
.Style = msoButtonCaption

'Sets the caption of the button
.Caption = "Continue"

'Assigns the macro PartTwo to the button
.OnAction = "PartTwo"

End With
End With
End Sub



Sub PartTwo()



'Deletes the Pause toolbar
CommandBars("Stop").Delete

Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
ChDir "C:\TEMP"
ActiveWorkbook.SaveAs Filename:="C:\TEMP\mailcost_load.prn"
FileFormat:= _
xlTextPrinter, CreateBackup:=False
Range("A1").Select

Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
Cells.Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select
ActiveWorkbook.SaveAs Filename:="C:\TEMP\mailcost_load.prn"
FileFormat:= _
xlTextPrinter, CreateBackup:=False
End Su

 

[Tom Ogilvy]

What does "jam" mean?

You say at the following point in the code, then post about 50+ lines of
code spread between two routines.

I doubt anyone knows what a command box is. could it be a commandbar
button?

 

[retseort]

Sorry, I was trying to provide enough info. By Jam I mean a runtim
error 5. Invald Proceedure Call or Element.

It then highlights this line in my code.

'Sets the toolbar's name
.Name = "Stop"


For some reason it does not like the .Name (in this case it is 'stop')
I can change it and it will work then the error will return. And it i
a commandbar button.


Thank you
Da

 

[Dave Peterson]

It sounds like you have a commandbar that's already using that name.

on error resume next
application.commandbars("stop").delete
on error goto 0

will delete it if there's one there and won't hurt if there isn't.

 

[retseort]

Thanks for the response.

I tried that. It did not work. I also tried a different name. With
little more work I took the name line out of the code entirely. Now i
errors at the point that it removes the commandbar because it does no
have name to refer to.

So I restored the code and now I am back to square one. Still stuck o
the name.

Got any other idears? :

 

[Dave Peterson]

Nope.

Maybe post the existing code. The line that caused the error and the error
message itself.