Enable sheet tab right click

[Eric Carolus]

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.

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

 

[Eric Carolus]

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

 

[Eric Carolus]

... 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.

 

[rlv01]

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.

 

[Jaafar Tribak]

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:

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

 

[Eric Carolus]

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!!

 

[Eric Carolus]

Hi Jaafar Tribak
False alarm! Sorry!! It works as intended. Thanks again.
Your help is much appreciated.