ONACTION macro tries to open another workbook
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. |
ONACTION macro tries to open another workbook
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. |
All times are GMT +1. The time now is 07:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com