View Full Version : [SOLVED:] Disabling a button for specific worksheets
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
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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.