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.
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
- 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
RE: how to use vba to forbid "Worksheet Menu Bar" being selected
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
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
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
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