Solved: Excel 2000 commandbar question

[Ken Puls]

Hey guys,

Anyone still using Excel 2000? I need to set up some virtual PC's for testing as I only have 2003 and 2007 on my machines right now.

At any rate, I got a PM from a user regarding my kb entry on how to Disable Cut, Copy, Paste (http://vbaexpress.com/kb/getarticle.php?kb_id=373) in Excel. Although they acknowledge that the entry works fine in 2003, they're having an issue in 2000. (I'm under the impression that they'd like it to work in both.) Error message I was given is:

"Enabled method of "_CommandBarButton" has failed."

The problem is obviously with the last line of the For loop shown below:
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
Next
End Sub
Not having 2000, I'm a littel stuck to give an answer.... Was this property not available in 2000? That seems strange to me.

Another odd thing is the error refering to "CommandBarButton". Nowhere in the code is anything declared as a "CommandBarButton", only "CommandBar" and "CommandBarControl".

I've PM'd the user to watch this thread for replies as well.

[Bob Phillips]

Ken,

It worked fine for me me in my tests.

I used

call enablemenuitem(30003,false)

which is the File>Edit control. What was the OP using?

[Ken Puls]

Honestly, I have no idea. I've reproduced all of the detail that I was PM'd. It was disclosed as a 2000 issue immediately, which is why I posted.

I suspect that they've changed something, and have asked them to post any detail here. :)

Thanks for checking it out, Bob. Much appreciated.

[TrippyTom]

Hey Ken,

Another posssibility (far fetched maybe) might be that the user has to enable a resource in 2000 that you don't have to enable in 2003?

I dunno, just a guess.

[Ken Puls]

Potentially, Tom, although based on Bob's success I'd be surprised. Stranger things have been know to happen though. :) The other thing I briefly wondered about was a missing VBA reference, although I don't think that that idea would yield much fruit, either.

The op did PM me back to say that they copied the code verbatim from the KB entry and haven't changed anything...

[barletta]

Option Explicit
Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub


Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub


thanks again...

[barletta]

don't know if this info helps......Add-Ins loaded when starting MSExcel - funcres.xla, password.xla and znpdfexcel.xla

[Ken Puls]

I'm not familiar with the last two add-ins, but there is an easy way to test that. Go to Tools|Add-ins and uncheck all the add-ins you have there. Re-load the workbook with the code and see if it works.

I'd also be curious to see the results of this. Modify the EnableMenuItem routine to read as follows:

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
Debug.Print ctlId
For Each cBar In Application.CommandBars
Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
Next
End Sub

If you don't already have it showing, make sure that the Immediate window is open. Clear everything out of it so that it is blank, then run the routine. Once you've finished (even if it fails), then paste the results of the immediate window for us. That should tell us which control is failing.

Oh, and btw... I've applied VBA tags to your code above to make it more readable. You can do the same by highlighting any code you post and clicking the green and white VBA button in the toolbar.

Cheers,

[Bob Phillips]

Ken,

Got it. I should have tested the whole routine not just the bit you posted.

There is a commandbar called Clipboard, which in 2002 on has all of those controls, 2000 has some. The ones it has cannot be enabled (for some reason).

Might be circumvented with




Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

[Ken Puls]

Cool deal, Bob. I guess it might be time to update a KB entry. :)

Barletta, can you try Bob's code and let us know if that fixes the problems for you?

[barletta]

i'll try it...give me about 10 minutes

[barletta]

works like a champ! thanks guys...cheers!

[Ken Puls]

Nice work, Bob! I'll update that KB entry to reflect the change.

[Ken Puls]

Updated. :)

[barletta]

well the code works great but it has also turn off the ability to open and close/collapse groups, any way that feature can remain on? i believe it called Outlining...thanks again

[Ken Puls]

Afraid I'm not sure on this one, barletta. Sorry. :(