how to use vba to forbid "Worksheet Menu Bar" being selected

[kapok1]

I want a VBA CODE to forbidden from copying the sheetsin this excel file to a new excel files or move to before or after other sheets. For example, SHEET1,SHEET2,....,SHEETn in one excel workbook, The n worksheets from SHEET1 to SHEETn are not permit to creat copies in a new file(It is not allowed to right-click the sheet name to select the 'Copy or Move to' menu) , I try many ways but also fail, for example:
Private Sub Workbook_Activate()
Dim ctl As Object
For Each ctl In Application.CommandBars("Worksheet Menu Bar").Controls
If ctl.ID = 244 Then
ctl.Enabled = False
Exit For
End If
Next ctl
End Sub

or other is not work also:
Private Sub Workbook_Activate()

'For Each ctl In Application.CommandBars.FindControls(ID:=847) '(&D)
' ctl.Enabled = False
'Next
For Each ctl In Application.CommandBars.FindControls(ID:=848) '(&M)...
ctl.Enabled = False
Next
For Each ctl In Application.CommandBars.FindControls(ID:=852) '(&W)
ctl.Enabled = False
Next
For Each ctl In Application.CommandBars.FindControls(ID:=889) '(&R)
ctl.Enabled = False
Next
'For Each ctl In Application.CommandBars.FindControls(ID:=945) '(&I)...
' ctl.Enabled = False
'Next
For Each ctl In Application.CommandBars.FindControls(ID:=946) '(&S)
ctl.Enabled = False
Next
'For Each ctl In Application.CommandBars.FindControls(ID:=21) '
' ctl.Enabled = False
'Next
'For Each ctl In Application.CommandBars.FindControls(ID:=19) '
' ctl.Enabled = False
'Next
'For Each ctl In Application.CommandBars.FindControls(ID:=22) '
' ctl.Enabled = False
'Next
'Application.DisplayAlerts = False
'Application.OnKey "^v", ""
'Application.OnKey "^c", ""
'Application.OnKey "^x", ""
Application.DisplayAlerts = True
End Sub

tahnks for your help, I want to settle, thanks.

 

[combo]

It is enough to turn on workbook structure protection, no VBA. This:
- disables changes in workbook structure. Adding, deleting, rearrangining, renaming, changing visibility of sheets is not allowed,
- copy/move sheet tab's popup menu item is disabled,
- sheets are still not protected and can be edited.

This option i available in File>Info menu.

combo

 

[kapok1]

thnks combo, but i have other code toprotect the sheets, so i just need the vba to forbid "Worksheet Menu Bar" being selected after the sheet is open. copy/move sheet tab's popup menu item should be disabled when the file is open and the MAC address is correct. If the mac address is wrong, the file could not be open and you could not see the hidded sheets . If the mac address is correct, i need to hide or diable the sheet tab's popup menu.

 

[combo]

Protection of workbook structure and protection of sheet are two differnt things.
Workbook structure protection can be handled by VBA too. So depending on the environment it is possible to temporarily disable the protection, process the workbook structure, and set the protection on again.


combo

 

[kapok1]

yes, i know it, for example, we could prohibite the "save as.." menu like following:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Cancel = True
End If
End Sub

Now, I just want to forbide the sheet munu "Copy or Move to..."
I try some from the internet, but it failed

 

[combo]

Just implement workbook structure protection and next test what is possible and what not from the sheet's tab popup menu.
Why do you need to replace native Excel functionality by disabling menu for all workskeets? What about the scenario when user has two workbooks open and needs to access sheet's menu in the the second one?

combo

 

[kapok1]

FIRSR I need to protect the sheet to be copy to a new workbook. so to disable the "copy or move to"menu when it open this files, and when it closes, it return to the original excel menu files.

by the way, I have another question, the following is the vba code, but could not find the files under the sub-folder, the code could only find and copy the specific datas from "cost"(sheet name) in the excel workbook files under the same folder, but could not copy the data from the files under the sub-folder in this folder.
and what's more, it could only copy all the data from the sheetname is "cost", if i wannna copy all the sheets(with same structure) from every workbook under the same folder including sub-folder, how to amend the code. thanks for your help.
file = Dir(folderPath & "\*.xls*")
Do While file <> ""

If Not file = ThisWorkbook.Name Then
Set wb = Workbooks.Open(folderPath & "\" & file)
With wb.Sheets("cost")
Set costRange = .Range("A415")
Set dataRange = .Range("G4:I15")
lastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
erow = 0 '

For Each Row In costRange.Rows
If Not IsEmpty(Row.Cells(1, 1)) Then

ThisWorkbook.Sheets(1).Cells(lastRow, 15) = wb.Name
ThisWorkbook.Sheets(1).Cells(lastRow, 2) = Row.Cells(1, 1)
'ThisWorkbook.Sheets(1).Cells(lastRow, 2).Resize(, 3) = Row.Value
ThisWorkbook.Sheets(1).Cells(lastRow, 11) = Row.Cells(1, 3)
ThisWorkbook.Sheets(1).Cells(lastRow, 12) = Row.Cells(1, 4)
lastRow = lastRow + 1

End If
Next
For Each Row In dataRange.Rows
If Not IsEmpty(Row.Cells(1, 1)) Then

ThisWorkbook.Sheets(1).Cells(lastRow, 15) = wb.Name
ThisWorkbook.Sheets(1).Cells(lastRow, 2).Value = Row.Cells(1, 1)
ThisWorkbook.Sheets(1).Cells(lastRow, 11) = Row.Cells(1, 2)
ThisWorkbook.Sheets(1).Cells(lastRow, 12) = Row.Cells(1, 3)
lastRow = lastRow + 1

End If
Next
End With
wb.Close SaveChanges:=False
End If
file = Dir
Loop