I have a userform in a book where you check a box called
Fillcolour(one of many checkboxes on the UserForm).
Then you press OK - this triggers the following macro:

Private Sub CmdOK_Click()

Me.Hide
Application.ScreenUpdating = False
Call AddMenuModifications
Unload Me
Application.ScreenUpdating = True

End Sub

Then in a normal module:

Sub AddMenuModifications()

On Error Resume Next
With CommandBars("Cell")
If UsrMenuBars.ChkFillColour Then
With .Controls.Add(msoControlButton)
.Caption = "Fill Colour"
.FaceId = 417
.OnAction = "ShowColorPallet"
End With
End If

End Sub

and in the same module:

Sub ShowColorPallet()
With Application.CommandBars("Fill Color")
.Top = 350
.Left = 500
.Visible = True
End With
End Sub

If I put just this code in a workbook on its own then it works fine -
but in the book I want it in, there are lots of bits of code like the
above which modify excel commandbars.
Anyhow when I press the new modifications excel opens another copy of
the workbook with the onaction code in it (or tries to - it tells me
I'm already in it, which I am!) - it then comes up with an error
message that just says "400"

Anyone ever seen this before??

Help greatly appreciated (I can forward the whole workbook if thats
any help)

Jason.

It sounds like your button is still pointing at the other workbook's macro. If
you delete the button, then add it and assign it to the current workbook, you
may have better luck:

Option Explicit
Sub AddMenuModifications()

On Error Resume Next
Application.CommandBars("cell").Controls("Fill Colour").Delete
On Error GoTo 0

With CommandBars("Cell")
With .Controls.Add(msoControlButton)
.Caption = "Fill Colour"
.FaceId = 417
.OnAction = ThisWorkbook.Name & "!ShowColorPallet"
End With
End With

End Sub


In fact, you may even want an auto_close macro that removes the option from the
cell toolbar when you close that workbook:

sub Auto_close()
On Error Resume Next
Application.CommandBars("cell").Controls("Fill Colour").Delete
On Error GoTo 0
end sub


jason wrote:

I have a userform in a book where you check a box called
Fillcolour(one of many checkboxes on the UserForm).
Then you press OK - this triggers the following macro:

Private Sub CmdOK_Click()

Me.Hide
Application.ScreenUpdating = False
Call AddMenuModifications
Unload Me
Application.ScreenUpdating = True

End Sub

Then in a normal module:

Sub AddMenuModifications()

On Error Resume Next
With CommandBars("Cell")
If UsrMenuBars.ChkFillColour Then
With .Controls.Add(msoControlButton)
.Caption = "Fill Colour"
.FaceId = 417
.OnAction = "ShowColorPallet"
End With
End If

End Sub

and in the same module:

Sub ShowColorPallet()
With Application.CommandBars("Fill Color")
.Top = 350
.Left = 500
.Visible = True
End With
End Sub

If I put just this code in a workbook on its own then it works fine -
but in the book I want it in, there are lots of bits of code like the
above which modify excel commandbars.
Anyhow when I press the new modifications excel opens another copy of
the workbook with the onaction code in it (or tries to - it tells me
I'm already in it, which I am!) - it then comes up with an error
message that just says "400"

Anyone ever seen this before??

Help greatly appreciated (I can forward the whole workbook if thats
any help)

Jason.

--

Dave Peterson

Cheers for the help Dave - I'll include the workbook.name bit of code
in the onaction section in the future - just to make things
watertight(as the file will eventually be an add-in)
I messed around with it and it seems that because I had macros of a
similar name in Personal.xls that some confusion was generated - once
they were commented out everything was ok!!

Thanks again
J

Dave Peterson wrote in message ...
It sounds like your button is still pointing at the other workbook's macro. If
you delete the button, then add it and assign it to the current workbook, you
may have better luck:

Option Explicit
Sub AddMenuModifications()

On Error Resume Next
Application.CommandBars("cell").Controls("Fill Colour").Delete
On Error GoTo 0

With CommandBars("Cell")
With .Controls.Add(msoControlButton)
.Caption = "Fill Colour"
.FaceId = 417
.OnAction = ThisWorkbook.Name & "!ShowColorPallet"
End With
End With

End Sub


In fact, you may even want an auto_close macro that removes the option from the
cell toolbar when you close that workbook:

sub Auto_close()
On Error Resume Next
Application.CommandBars("cell").Controls("Fill Colour").Delete
On Error GoTo 0
end sub


jason wrote:

I have a userform in a book where you check a box called
Fillcolour(one of many checkboxes on the UserForm).
Then you press OK - this triggers the following macro:

Private Sub CmdOK_Click()

Me.Hide
Application.ScreenUpdating = False
Call AddMenuModifications
Unload Me
Application.ScreenUpdating = True

End Sub

Then in a normal module:

Sub AddMenuModifications()

On Error Resume Next
With CommandBars("Cell")
If UsrMenuBars.ChkFillColour Then
With .Controls.Add(msoControlButton)
.Caption = "Fill Colour"
.FaceId = 417
.OnAction = "ShowColorPallet"
End With
End If

End Sub

and in the same module:

Sub ShowColorPallet()
With Application.CommandBars("Fill Color")
.Top = 350
.Left = 500
.Visible = True
End With
End Sub

If I put just this code in a workbook on its own then it works fine -
but in the book I want it in, there are lots of bits of code like the
above which modify excel commandbars.
Anyhow when I press the new modifications excel opens another copy of
the workbook with the onaction code in it (or tries to - it tells me
I'm already in it, which I am!) - it then comes up with an error
message that just says "400"

Anyone ever seen this before??

Help greatly appreciated (I can forward the whole workbook if thats
any help)

Jason.