Command Box jams macro
 

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

--
retseor
-----------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...fo&userid=2469
View this thread: http://www.excelforum.com/showthread.php?threadid=48369

Command Box jams macro
 

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?

--
Regards,
Tom Ogilvy


"retseort" wrote in
message ...

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

For some reason it jams at the following point in the code. WHY? The
interesting thing is if I change the word stop to anything else it will
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 format
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 Sub


--
retseort
------------------------------------------------------------------------
retseort's Profile:
http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=483698

Command Box jams macro
 

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

--
retseor
-----------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...fo&userid=2469
View this thread: http://www.excelforum.com/showthread.php?threadid=48369

Command Box jams macro
 

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 wrote:

Sorry, I was trying to provide enough info. By Jam I mean a runtime
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 is
a commandbar button.

Thank you
Dan

--
retseort
------------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=483698

--

Dave Peterson

Command Box jams macro
 

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? :

--
retseor
-----------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...fo&userid=2469
View this thread: http://www.excelforum.com/showthread.php?threadid=48369

Command Box jams macro
 

Nope.

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

retseort wrote:

Thanks for the response.

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

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

Got any other idears? :)

--
retseort
------------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=483698

--

Dave Peterson