Enable sheet tab right click

Eric Carolus

Board Regular
Joined
Sep 17, 2012
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
Hi Folks

I need some help with a workbook. I have 4 sheets in a workbook. On these sheets the right click sheet tab menu had been disabled. On ONE of these sheets is a button that can copy a sheet called “Income”. On the copied sheet the right click sheet tab must be enabled. Can someone help please? Any and all help will be appreciated. Some of the code I found online but I cannot make it work.

VBA Code:
Private Sub CommandButton2_Click()

'Decalre the variables
Dim newName As String
Dim newSheet As Worksheet


 ' Ensure that the Context Menu of the new sheet is in place/Visible

' Application.CommandBars("Ply").Enabled = True

' Unprotect the worksheet called Income with the password called "Church"

Worksheets("Income").Unprotect Password:="Church"

'Make Content Menu visible

Application.CommandBars("Ply").Enabled = True


' Copy the sheet called "Income"

ActiveSheet.Copy After:=Worksheets(Worksheets.Count)


Set newSheet = ActiveSheet

' Ensure that the Context Menu of the new sheet is in place/Visible

Application.CommandBars("Ply").Enabled = True

' Application.CommandBars("Ply").Controls("Delete Sheet").Enabled = False

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Protect the worksheet called Income with the password called "Church"

Worksheets("Income").Protect Password:="Church"

' Prompt the user for a new name/Provide a name for the new sheet

newName = InputBox("Enter the new name for the copied sheet:", "Name New Sheet")

' Check if the name is not empty and does not already exist

If newName <> "" And Not WorksheetExists(newName) Then

newSheet.Name = newName

' Ensure the new sheet is unprotected

newSheet.Unprotect Password:="Church"

'' ' Ensure that the Context Menu of the new sheet is in place/Visible

Application.CommandBars("Ply").Enabled = True

Else

MsgBox "Invalid sheet name. Please provide a unique name that is not blank."

' Optionally, remove the newly created sheet if the name is invalid

Application.DisplayAlerts = False

newSheet.Delete

Application.DisplayAlerts = True

End If

Application.CommandBars("Ply").Enabled = True

End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Folks
Maybe some more information. One of the four sheets is called "Income". The button ON the sheet called "Income" copies the sheet and the copied sheet's right click on the sheet tab should disable the menu. Maybe there is a different way of looking at this. I wish to be able to Delete the resulting copied sheets (if necessary) BUT THE 4 ORIGINAL SHEETS
 
Upvote 0
... BUT THE user should never be able to delete the 4 ORIGINAL SHEETS. Maybe there is a different way of doing it, other than the way I proposed in my first post? Thanks folks.
 
Upvote 0
If there is a way to disable just one tab of many, I don't know of it. Application.CommandBars("Ply").Enabled = True/False has always been an all-or-nothing function for me. You may need to rethink your strategy.
 
Upvote 0
With a bit of trickery, you could try using the Worksheet Activate/Deactivate events to dynamically enable/disable the sheet's right-click menu based on the activesheet.

See if this works for you :

Code goes in the Worksheet Module:
VBA Code:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#Else
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If

Private oCopySh As Worksheet
Private bDeleting As Boolean, bFlag As Boolean

Public Sub CommandButton1_Click()

    Set oCopySh = GetMyWorksheet
    bDeleting = False: bFlag = False
    
    If oCopySh Is Nothing Then
        ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
        Set oCopySh = ActiveSheet
    End If
    
    With oCopySh
        .Name = "My_Copy"  '<< change name as required.
        .OnSheetActivate = Me.CodeName & ".EnablePly"
        .OnSheetDeactivate = Me.CodeName & ".EnablePly"
        Application.CommandBars("Ply").Enabled = True
    End With

End Sub

Private Sub EnablePly()

    With Application
        .CommandBars("Ply").Enabled = False
        If CBool(ActiveSheet Is oCopySh) Then
            If bDeleting = False Then
                .CommandBars("Ply").Enabled = True
                If GetAsyncKeyState(VBA.vbKeyRButton) Then
                    If bFlag Then
                        .OnTime Now, Me.CodeName & ".ShowMenu"
                    Else
                        .CommandBars("Ply").Enabled = False
                    End If
                End If
            End If
        Else
            If bFlag = False Then
                Call SetForegroundWindow(GetDesktopWindow)
                Call SetForegroundWindow(.hwnd)
            End If
        End If
    End With
    
    bFlag = True
    
End Sub

Private Sub ShowMenu()
    With Application.CommandBars("Ply")
        .ShowPopUp
        .Enabled = False
    End With
End Sub

Private Function GetMyWorksheet() As Worksheet
    On Error Resume Next
    Set GetMyWorksheet = Worksheets("My_Copy")
End Function

Private Sub Worksheet_BeforeDelete()
    bDeleting = True
End Sub
 
Last edited:
Upvote 0
Solution
Hu Jaafar Tribak
The code works beautifully!! Thank you so much.
One thing though, on the pop up menu, the "Delete" is greyed out.
I need this cause I want this sheet to be deleted if necessary.
Thank you again!!
 
Upvote 0

Forum statistics

Threads
1,223,941
Messages
6,175,537
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top