Eric Carolus
Board Regular
- Joined
- Sep 17, 2012
- Messages
- 133
- Office Version
- 2016
- Platform
- 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.
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