PDA

View Full Version : [SOLVED] Disabling a button for specific worksheets



Ctrl
04-29-2008, 02:52 AM
Hi everyone

I'm using a custom made toolbar with a button to sort things out.
What I really need is to have this button only working for specific worksheets.
So how can I make this button be disabled each time 'let's say'
sheet1 "Report A" and sheet2 "Report B" are selected.

And of course this button should be enabled again if other worksheets are selected.

I put this code in Module1 (I removed codes for other buttons)


Dim ComBar As CommandBar
On Error GoTo ErrorHandler
On Error Resume Next
CommandBars("My Toolbar").Delete
Set ComBar = CommandBars.Add(Name:="My Toolbar", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
ComBar.Protection = msoBarNoChangeVisible
With ComBar.Controls.Add(Type:=msoControlButton, Before:=4)
.FaceId = 210
.TooltipText = "Sort"
.OnAction = "Macro1"
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

Bob Phillips
04-29-2008, 02:56 AM
This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.CommandBars("My Toolbar").Controls("Sort").Enabled = _
(Sh.Name <> "Report A" And Sh.Name <> "Report B")
End Sub

Ctrl
04-29-2008, 04:17 AM
Thank you xld for your swift reply but it didn't work for me.
I got the Run-time error '5' Invalid procedure call or argument on your code.

Now i put all codes in the workbook maybe will make things more reasonable. These codes are mainly for deleteing/disabling all toolbars and cut/copy/paste commands.

And one important thing is that I'm referring to a custom made 'Sort' button not the built-in 'Sort' button that has an id.

Here what goes in "ThisWorkbook"


Option Explicit
Private mFormulaBar

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = True
DeleteToolbar
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
AddNewToolBar
End Sub


And these codes in Module1


Option Explicit
Sub AddNewToolBar()
Dim ComBar As CommandBar
On Error GoTo ErrorHandler
On Error Resume Next
CommandBars("My Toolbar").Delete
Set ComBar = CommandBars.Add(Name:="My Toolbar", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
ComBar.Protection = msoBarNoChangeVisible
With ComBar.Controls.Add(Type:=msoControlButton, ID:=3, Before:=1)
.TooltipText = "Save"
End With
With ComBar.Controls.Add(Type:=msoControlButton, ID:=19, Before:=2)
.TooltipText = "Copy"
End With
With ComBar.Controls.Add(Type:=msoControlButton, ID:=370, Before:=3)
.FaceId = 22
.TooltipText = "Paste"
End With
With ComBar.Controls.Add(Type:=msoControlButton, Before:=4)
.FaceId = 210
.TooltipText = "Sort"
.OnAction = "Macro1"
End With
With ComBar.Controls.Add(Type:=msoControlButton, Before:=5)
.FaceId = 2650
.TooltipText = "Import"
.OnAction = "Macro2"
End With
With ComBar.Controls.Add(Type:=msoControlButton, ID:=4, Before:=6)
.TooltipText = "Print"
End With
With ComBar.Controls.Add(Type:=msoControlButton, ID:=752, Before:=7)
.Caption = "Exit"
.TooltipText = "Exit..."
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

Sub DeleteToolbar()
On Error Resume Next
CommandBars("My Toolbar").Delete
End Sub

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(370, Allow) ' paste value
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
If cBar.Name <> "My Toolbar" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
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

Excuse my ignorance :dunno

Simon Lloyd
04-29-2008, 04:37 AM
How about :


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Report A" Or Sh.Name = "Report B" Then
Application.CommandBars("My Toolbar").Controls("Sort").Enabled = False
Else
Application.CommandBars("My Toolbar").Controls("Sort").Enabled = True
End If
End Sub

or


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Report A" Or Sh.Name = "Report B" Then
Application.EnableEvents=False
Else
Application.EnableEvents= True
End If
End Sub

in essence its the same as xlds' code, this again goes in the ThisWorkBook module (Alt+F11, double click Thisworkbook icon in projects window top left!).

Bob Phillips
04-29-2008, 05:31 AM
The problem is that I read the Tooltiptext as the Caption. You need to add a caption to the control,


.Caption = "SortIt"


then use




Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.CommandBars("My Toolbar").Controls("SortIt").Enabled = _
(Sh.Name <> "Report A" And Sh.Name <> "Report B")
End Sub

Ctrl
04-29-2008, 05:52 AM
Thank you xld... now it is working :clap: it's all about the .Caption
Also thanks to Simon Lloyd your first code is just fine provided that I add .Caption to the sort button

Now i will see how it works in my project. If everything is alright I will mark this as sloved.

Ctrl
04-29-2008, 07:03 AM
I added this code to the Workbook_Open()

Sheets("Sheet1").Select

This will make sure that the 'Sort' button will not be enabled in case the workbook is saved, closed then opened again with a workeksheet that this button should not be working with.

Thank you again for the help.