×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

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

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

(OP)
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.

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

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

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

(OP)
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

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

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

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

(OP)
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("A4:D15")
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close