Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
I read the long discussion that Ron and Peter were trying to answer back in 2005, but another solution was found prior to answering question in the OP. I am trying to take control of the Inser Copied Cells button on the Row CommandBar. It is not cooperating very well, as the button appears and disappears, thus clearing any changes I make to it. So far I know: 1) The option will only appear after a CutCopyMode is not longer false 2) The option will only appear once the user activates the Row Menu. So I cannot seem to access this command until the menu becomes visible, but I can't seem to do anything to menu while it's visible. I tried adding an Application.OnTime event, but that won't fire if the menu is visible. Essentially I can take over the menu if the user shows it and then selects elsewhere, but if the show it for the first time, I cannot control that option. I'm trying to take it over so that users do not have the ability to copy formatting from another location into a specific sheet. They need to have the ability to copy rows or insert rows, but I jusst don't want the formatting to come with it. I've hijacked all other means, just this one seems impossible to control. Any idea? -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
this really isn't very clear. what is a Row Command Bar? "J Streger" wrote in message ... I read the long discussion that Ron and Peter were trying to answer back in 2005, but another solution was found prior to answering question in the OP. I am trying to take control of the Inser Copied Cells button on the Row CommandBar. It is not cooperating very well, as the button appears and disappears, thus clearing any changes I make to it. So far I know: 1) The option will only appear after a CutCopyMode is not longer false 2) The option will only appear once the user activates the Row Menu. So I cannot seem to access this command until the menu becomes visible, but I can't seem to do anything to menu while it's visible. I tried adding an Application.OnTime event, but that won't fire if the menu is visible. Essentially I can take over the menu if the user shows it and then selects elsewhere, but if the show it for the first time, I cannot control that option. I'm trying to take it over so that users do not have the ability to copy formatting from another location into a specific sheet. They need to have the ability to copy rows or insert rows, but I jusst don't want the formatting to come with it. I've hijacked all other means, just this one seems impossible to control. Any idea? -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
Yup and it's added only when the menu is called, meaning you have no time to actually grab hold of the function. And the only way to disable it is to protect the sheet, and that is not acceptable for what I'm doing. *sigh* -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
did you try deleting it? "J Streger" wrote in message ... Yup and it's added only when the menu is called, meaning you have no time to actually grab hold of the function. And the only way to disable it is to protect the sheet, and that is not acceptable for what I'm doing. *sigh* -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
i doubt that works - I'd guess the item gets added when copy is selected, and I'd expect the OnAction to be recreated then too? "Patrick Molloy" wrote in message ... did you try deleting it? "J Streger" wrote in message ... Yup and it's added only when the menu is called, meaning you have no time to actually grab hold of the function. And the only way to disable it is to protect the sheet, and that is not acceptable for what I'm doing. *sigh* -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
I think I figured out how to latch onto it. I threw the last line in where my method used to have the 2: On Error Resume Next Application.CommandBars("Row").Controls("Insert Copied C&ells") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") Application.CommandBars("Row").Controls("Insert") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") It seems Insert and Insert Copied C&ells are both linked, as in they cannot coexist on the menu, but yet when they switch they share the same properties. So you can set the OnAction property while on insert, then when you copy and show the menu, the OnAction method is auto linked to the Insert Copied cells. You just need to put a line for both as you never know which is actively there. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
You are on are on the right lines but actually there are three of them that appear depending on the scenario, have a look in a new instance. Rather than the brute force perhaps look at what controls already exist on the bar. for each ctr in commandbars("Row") debug.? ctr.id, ctr.caption next If I follow, the objective is to prevent paste formats. What about Ctrl-v or shift-insert (onkey ?). Have you looked at protecting the sheet but allowing most user changes except formats, and maybe "allow edit" on the whole sheet. Regards, Peter T "J Streger" wrote in message ... I think I figured out how to latch onto it. I threw the last line in where my method used to have the 2: On Error Resume Next Application.CommandBars("Row").Controls("Insert Copied C&ells") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") Application.CommandBars("Row").Controls("Insert") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") It seems Insert and Insert Copied C&ells are both linked, as in they cannot coexist on the menu, but yet when they switch they share the same properties. So you can set the OnAction property while on insert, then when you copy and show the menu, the OnAction method is auto linked to the Insert Copied cells. You just need to put a line for both as you never know which is actively there. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
3!?! Wow, I guess I probably should loop rahter than brute force it at that rate. Thanks for that! And yes I tried that but too many factors are getting in the way. Protection is great but the sheets are shared more often than not. Since you can't undo protection while shared it makes writing code difficult and some things just aren't possible. I have a ton of code to control Copy, Past, Cut, Insert, Delete to keep things in order as my user base can be quite...challenging at time. We are also using Excel 2003 currently, and are using Outline levels, which also don't seem to work under a protected sheet. I know they fixed that aspect in 2007, but the protection/shared conflict causes way to many issues. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Peter T" wrote: You are on are on the right lines but actually there are three of them that appear depending on the scenario, have a look in a new instance. Rather than the brute force perhaps look at what controls already exist on the bar. for each ctr in commandbars("Row") debug.? ctr.id, ctr.caption next If I follow, the objective is to prevent paste formats. What about Ctrl-v or shift-insert (onkey ?). Have you looked at protecting the sheet but allowing most user changes except formats, and maybe "allow edit" on the whole sheet. Regards, Peter T "J Streger" wrote in message ... I think I figured out how to latch onto it. I threw the last line in where my method used to have the 2: On Error Resume Next Application.CommandBars("Row").Controls("Insert Copied C&ells") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") Application.CommandBars("Row").Controls("Insert") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") It seems Insert and Insert Copied C&ells are both linked, as in they cannot coexist on the menu, but yet when they switch they share the same properties. So you can set the OnAction property while on insert, then when you copy and show the menu, the OnAction method is auto linked to the Insert Copied cells. You just need to put a line for both as you never know which is actively there. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
I'm always rather nervous of disabling standard coomandbar controls in case anything goes wrong. Maybe try something along the following lines which avoids that potential problem and gives you a lot more flexibility control, eg only cancel the action in a given workbook, or say run test2 in the wb's activate event and no worries about resetting. ' in a normal workbook Option Explicit Private mColCls As Collection Dim cc As CommandBarButton Sub test2() Dim sWBname As String Dim v Dim cbr As CommandBar Dim ctr As CommandBarControl Dim cls As Class1 sWBname = ActiveWorkbook.Name Set mColCls = New Collection For Each v In Array("Row", "Column", "Cell") Set cbr = CommandBars(v) For Each ctr In cbr.Controls Select Case ctr.ID Case 296, 297, 3181, 3183, 3185, 3187 Set cls = New Class1 Set cls.gCtrl = ctr mColCls.Add cls, v Debug.Print ctr.ID, ctr.Caption, v Exit For End Select Next Next End Sub '' in a class named Class1 Public WithEvents gCtrl As CommandBarButton Public gsWBname As String Private Sub gCtrl_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) If ActiveWorkbook.Name = gsWBname And Ctrl.ID 3183 Then CancelDefault = True MsgBox Ctrl.Caption & " won't work in " & gsWBname End If End Sub to clear up simply do Set mColCls = Nothing Regards, Peter T PS I see a typo in my last post for each ctr in commandbars("Row") should of course have read for each ctr in commandbars("Row").controls "J Streger" wrote in message ... 3!?! Wow, I guess I probably should loop rahter than brute force it at that rate. Thanks for that! And yes I tried that but too many factors are getting in the way. Protection is great but the sheets are shared more often than not. Since you can't undo protection while shared it makes writing code difficult and some things just aren't possible. I have a ton of code to control Copy, Past, Cut, Insert, Delete to keep things in order as my user base can be quite...challenging at time. We are also using Excel 2003 currently, and are using Outline levels, which also don't seem to work under a protected sheet. I know they fixed that aspect in 2007, but the protection/shared conflict causes way to many issues. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Peter T" wrote: You are on are on the right lines but actually there are three of them that appear depending on the scenario, have a look in a new instance. Rather than the brute force perhaps look at what controls already exist on the bar. for each ctr in commandbars("Row") debug.? ctr.id, ctr.caption next If I follow, the objective is to prevent paste formats. What about Ctrl-v or shift-insert (onkey ?). Have you looked at protecting the sheet but allowing most user changes except formats, and maybe "allow edit" on the whole sheet. Regards, Peter T "J Streger" wrote in message ... I think I figured out how to latch onto it. I threw the last line in where my method used to have the 2: On Error Resume Next Application.CommandBars("Row").Controls("Insert Copied C&ells") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") Application.CommandBars("Row").Controls("Insert") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") It seems Insert and Insert Copied C&ells are both linked, as in they cannot coexist on the menu, but yet when they switch they share the same properties. So you can set the OnAction property while on insert, then when you copy and show the menu, the OnAction method is auto linked to the Insert Copied cells. You just need to put a line for both as you never know which is actively there. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Insert Copied Cells
Forgot to include "cls.gsWBname = sWBname" here's the normal module code again, Class1 code as before it is again Sub test2() Dim sWBname As String Dim v Dim cbr As CommandBar Dim ctr As CommandBarControl Dim cls As Class1 sWBname = ActiveWorkbook.Name Set mColCls = New Collection For Each v In Array("Row", "Column", "Cell") Set cbr = CommandBars(v) For Each ctr In cbr.Controls Select Case ctr.ID Case 296, 297, 3183, 3185, 3187 Set cls = New Class1 Set cls.gCtrl = ctr cls.gsWBname = sWBname mColCls.Add cls, v Debug.Print ctr.ID, ctr.Caption, v Exit For End Select Next Next End Sub "Peter T" <peter_t@discussions wrote in message ... I'm always rather nervous of disabling standard coomandbar controls in case anything goes wrong. Maybe try something along the following lines which avoids that potential problem and gives you a lot more flexibility control, eg only cancel the action in a given workbook, or say run test2 in the wb's activate event and no worries about resetting. ' in a normal workbook Option Explicit Private mColCls As Collection Dim cc As CommandBarButton Sub test2() Dim sWBname As String Dim v Dim cbr As CommandBar Dim ctr As CommandBarControl Dim cls As Class1 sWBname = ActiveWorkbook.Name Set mColCls = New Collection For Each v In Array("Row", "Column", "Cell") Set cbr = CommandBars(v) For Each ctr In cbr.Controls Select Case ctr.ID Case 296, 297, 3181, 3183, 3185, 3187 Set cls = New Class1 Set cls.gCtrl = ctr mColCls.Add cls, v Debug.Print ctr.ID, ctr.Caption, v Exit For End Select Next Next End Sub '' in a class named Class1 Public WithEvents gCtrl As CommandBarButton Public gsWBname As String Private Sub gCtrl_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) If ActiveWorkbook.Name = gsWBname And Ctrl.ID 3183 Then CancelDefault = True MsgBox Ctrl.Caption & " won't work in " & gsWBname End If End Sub to clear up simply do Set mColCls = Nothing Regards, Peter T PS I see a typo in my last post for each ctr in commandbars("Row") should of course have read for each ctr in commandbars("Row").controls "J Streger" wrote in message ... 3!?! Wow, I guess I probably should loop rahter than brute force it at that rate. Thanks for that! And yes I tried that but too many factors are getting in the way. Protection is great but the sheets are shared more often than not. Since you can't undo protection while shared it makes writing code difficult and some things just aren't possible. I have a ton of code to control Copy, Past, Cut, Insert, Delete to keep things in order as my user base can be quite...challenging at time. We are also using Excel 2003 currently, and are using Outline levels, which also don't seem to work under a protected sheet. I know they fixed that aspect in 2007, but the protection/shared conflict causes way to many issues. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Peter T" wrote: You are on are on the right lines but actually there are three of them that appear depending on the scenario, have a look in a new instance. Rather than the brute force perhaps look at what controls already exist on the bar. for each ctr in commandbars("Row") debug.? ctr.id, ctr.caption next If I follow, the objective is to prevent paste formats. What about Ctrl-v or shift-insert (onkey ?). Have you looked at protecting the sheet but allowing most user changes except formats, and maybe "allow edit" on the whole sheet. Regards, Peter T "J Streger" wrote in message ... I think I figured out how to latch onto it. I threw the last line in where my method used to have the 2: On Error Resume Next Application.CommandBars("Row").Controls("Insert Copied C&ells") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") Application.CommandBars("Row").Controls("Insert") _ .OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _ "OverrideInsertCells"), "") It seems Insert and Insert Copied C&ells are both linked, as in they cannot coexist on the menu, but yet when they switch they share the same properties. So you can set the OnAction property while on insert, then when you copy and show the menu, the OnAction method is auto linked to the Insert Copied cells. You just need to put a line for both as you never know which is actively there. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: hmm 10) no. I still have the Insert Copied Cells ... item i have to reset to clear it. there's obviously some code in the copy routine that turns this item on or off internally. bummer "J Streger" wrote in message ... Ahhh, but the menu option isn't always there for adjust. Try this: 1) Clear the cut Copy Mode. 2) Rt Click on a row number to bring up the menu, and verify that Insert Copied Cells isn't present. 3) Try to run the Intercept Code. It should fail. Then: 4) Copy any cell to enter cutcopymode 5) Try to run the intercept code. It should fail, as the menu option is there. 6) Rt click on a row number. 7) Run the intercept code. It should succeed. 8) Set CutCopyMode to false. 9) Run the Intercept Code. It should Succeed. 10) Rt Click on the row number. Insert Copied Cells should be gone. 11) Run the Intercept code. It should fail again. It's this disappearing menu item that is driving me insane! :P -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: how interesting! I've been doing this for 20 years and I've never used this menu in code! thank you :) so, you can intercept if you will eg Sub Intercept() With Application.CommandBars("Row").Controls("Insert Copied C&ells") .OnAction = "ShowMessage" End With End Sub Sub ShowMessage() MsgBox "Hello World!" End Sub this means you can write your own code that will paste whatever, or you can just delete this menu item "J Streger" wrote in message ... Row command Bar: Application.CommandBars("Row").Controls("Insert Copied C&ells") Also some more testing revealed that protecting the sheet from inserting rows does turn off this menu option, so there should be a way of affecting the function either on the fly or accessing it prior to. Still haven't found a way though. -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 "Patrick Molloy" wrote: this really isn't very clear. what is a Row Command Bar? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot insert cells copied from one workbook to another | Excel Discussion (Misc queries) | |||
Insert copied cells | Excel Worksheet Functions | |||
Insert Copied Cells? | Excel Discussion (Misc queries) | |||
Insert Copied Cells? | Excel Programming | |||
insert copied cells | Excel Discussion (Misc queries) |