Here is the thing. Basically i have some codes here that enable and disbale
controls. There are also codes that open files. The thing is everything is
running perfectly on codes, like when i'm on file1 the codes bring me to
file2 etc. The problem comes now that i can only open file2 through codes. It
can't be done from the manual way of click through folders. Please help and
advice me what to do. Thanks!
The codes:
Private Sub Workbook_open()
If ActiveWorkbook.Name = "Monthly Accounts Portfolio_123.xls" Then
DisableCutAndPaste
Else:
Call EnableCutAndPaste
End If
End Sub
Sub ExcelInstances()
Dim xlApp1 As Object
Set xlApp1 = CreateObject("Excel.Application")
xlApp1.Visible = True
xlApp1.Workbooks.Open filename:="U:\Assets\Unsecured Loans\Cards\MIS\Disable
control wip\test\Monthly Accounts Portfolio_123.xls"
End Sub
Sub DisableCutAndPaste()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
EnableControl 3, False ' save
EnableControl 748, False ' saveas...
EnableControl 247, False ' pagesetup...
EnableControl 3823, False ' save as webpage
EnableControl 3655, False ' webpage preview
EnableControl 848, False ' move or copy sheet
EnableControl 846, False ' save workspace
EnableControl 30255, False ' print area
EnableControl 30095, False ' send to
EnableControl 762, False ' header & footer
EnableControl 30017, False 'macro
EnableControl 3738, False 'mail recipient
Application.OnKey "^s", ""
Application.OnKey "^c", ""
Application.OnKey "^x", ""
Application.OnKey "^p", ""
Application.OnKey "^w", ""
Application.OnKey "^v", ""
Application.OnKey "^+{F11}", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(Id:=847)
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(Id:=889)
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(Id:=748)
Ctrl.Enabled = False
Next Ctrl
' MenuBars(xlWorksheet).Menus("File").MenuItems("Save as Web
Page...").Delete
' MenuBars(xlWorksheet).Menus("File").MenuItems("Save Workspace...").Delete
' MenuBars(xlWorksheet).Menus("File").MenuItems("Send To").Delete
' MenuBars(xlWorksheet).Menus("File").MenuItems("Web Page Preview").Delete
End Sub
Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
Dim mb As MenuItems
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled ' For Each mb In Application
' mb.Reset
' Next mb
Next
End Sub
Sub EnableCutAndPaste()
EnableControl 21, True ' cut
EnableControl 19, True ' copy
EnableControl 22, True ' paste
EnableControl 755, True ' pastespecial
EnableControl 2521, True ' print
EnableControl 109, True ' print preview
EnableControl 4, True ' print...
EnableControl 3, True ' saveas
EnableControl 748, True ' saveas...
EnableControl 247, True ' pagesetup...
EnableControl 3823, True ' save as webpage
EnableControl 3655, True ' webpage preview
EnableControl 848, True ' move or copy sheet
EnableControl 30095, True ' send to
EnableControl 846, True ' save workspace
EnableControl 30255, True ' print area
EnableControl 762, True ' header & footer
EnableControl 846, True ' save workspace
EnableControl 30017, True 'macro
EnableControl 3738, True 'mail recipient
Application.OnKey "^s"
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "^w"
Application.OnKey "^p"
Application.OnKey "^+{F11}"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(Id:=847)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(Id:=889)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(Id:=748)
Ctrl.Enabled = True
Next Ctrl
End Sub
Similar Threads
