VBA运行时错误'91':对象变量或块变量未设置
我面临着我的宏的问题,它从桌面平稳运行,但是当我把相同的文件放入网络驱动器时,其他用户面临Runtime error 91 Object variable or With block variable not set
。
也让我知道如何使用宏删除受保护的视图。VBA运行时错误'91':对象变量或块变量未设置
需要你的帮助下面的代码:
模块
Sub UnhideAllSheets()
'Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
Sub HideAllSheets()
'Unhide all sheets in workbook.
Call UnhideAllSheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, "START", vbTextCompare) = 0 _
And InStr(1, ws.Name, "Data", vbTextCompare) = 0 Then
ws.Visible = xlVeryHidden
End If
Next ws
End Sub
Sub DRD()
Dim ws As Worksheet
Call HideAllSheets
'If VBA.Environ("Username") = "140736TJ" Then
'Sheets("8.Generator").Visible = xlSheetVisible
'Sheets("8.Generator").Select
With ActiveWorkbook.Worksheets
Select Case VBA.Environ("Username")
Case "130349TJ", "130355TJ"
Sheets("1.Large MCH").Visible = xlSheetVisible
Sheets("1.Large MCH").Select
Case "140646TJ"
Sheets("2.Large FAB").Visible = xlSheetVisible
Sheets("2.Large FAB").Select
Case "130361TJ"
Sheets("3.Blade").Visible = xlSheetVisible
Sheets("3.Blade").Select
Case "120243TJ"
Sheets("4.Nozzle").Visible = xlSheetVisible
Sheets("4.Nozzle").Select
Case "130360TJ", "146061tc"
Sheets("5.T.Assy").Visible = xlSheetVisible
Sheets("5.T.Assy").Select
Case "110206TJ"
Sheets("6.Rotor").Visible = xlSheetVisible
Sheets("6.Rotor").Select
Case "120237TJ"
Sheets("7. Control Valve").Visible = xlSheetVisible
Sheets("7. Control Valve").Select
Case "140736TJ", "110088TJ", "130344TJ"
Call UnhideAllSheets
Sheets("DRD Index Consolidation").Select
Case "120234TJ"
Call UnhideAllSheets
Sheets("DRD Index Consolidation").Select
Call StopDeleteRowCols
Case Else
MsgBox "ACCESS DENIED"
ActiveWorkbook.Close
Call ResetDeleteRowCols
End Select
End With
End Sub
Sub StopDeleteRowCols()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars.FindControls(ID:=293)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=294)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=296)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=3181)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=292)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=3125)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=21)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=945)
ctl.Enabled = False
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=4)
ctl.Enabled = False
Next ctl
End Sub
Sub ResetDeleteRowCols()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars.FindControls(ID:=293)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=294)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=296)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=3181)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=292)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=3125)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=21)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=945)
ctl.Enabled = True
Next ctl
For Each ctl In Application.CommandBars.FindControls(ID:=4)
ctl.Enabled = True
Next ctl
End Sub
Private Sub Workbook_Open()
DRD
End Sub
你应该说什么行你所得到的错误。我确定错误来自Application.CommandBars.FindControlsID:=?)
找不到任何控件。为了捕获错误,我创建了一个方法Sub BatchEnableControls(Enable As Boolean)
来查找和启用/禁用这些控件。我还做了一些其他更改,以使代码更易于遵循和扩展。
Sub DRD()
Dim ws As Worksheet
Call HideAllSheets
'If VBA.Environ("Username") = "140736TJ" Then
'Sheets("8.Generator").Visible = xlSheetVisible
'Sheets("8.Generator").Select
Select Case VBA.Environ("Username")
Case "130349TJ"
Sheets("1.Large MCH").Visible = xlSheetVisible
Sheets("1.Large MCH").Select
Case "140646TJ"
Sheets("2.Large FAB").Visible = xlSheetVisible
Sheets("2.Large FAB").Select
Case "130361TJ"
Sheets("3.Blade").Visible = xlSheetVisible
Sheets("3.Blade").Select
Case "120243TJ"
Sheets("4.Nozzle").Visible = xlSheetVisible
Sheets("4.Nozzle").Select
Case "130360TJ"
Sheets("5.T.Assy").Visible = xlSheetVisible
Sheets("5.T.Assy").Select
Case "110206TJ"
Sheets("6.Rotor").Visible = xlSheetVisible
Sheets("6.Rotor").Select
Case "120237TJ"
Sheets("7. Control Valve").Visible = xlSheetVisible
Sheets("7. Control Valve").Select
Case "130355TJ"
Sheets("1.Large MCH").Visible = xlSheetVisible
Sheets("1.Large MCH").Select
Case "146061tc"
Sheets("5.T.Assy").Visible = xlSheetVisible
Sheets("5.T.Assy").Select
Case "140736TJ", "110088TJ", "130344TJ", "120234TJ"
Call UnhideAllSheets
Sheets("DRD Index Consolidation").Select
Case "120234TJ"
Call UnhideAllSheets
Sheets("DRD Index Consolidation").Select
BatchEnableControls False
Case Else
MsgBox "ACCESS DENIED"
BatchEnableControls True
ActiveWorkbook.Close
End Select
End Sub
Sub BatchEnableControls(Enable As Boolean)
EnableControls Enable, 293
EnableControls Enable, 294
EnableControls Enable, 296
EnableControls Enable, 3181
EnableControls Enable, 292
EnableControls Enable, 3125
EnableControls Enable, 21
EnableControls Enable, 945
EnableControls Enable, 4
End Sub
Sub EnableControls(Enable As Boolean, ControlID As Long)
Dim ctl As CommandBarControl
On Error Resume Next
For Each ctl In Application.CommandBars.FindControls(ID:=ControlID)
ctl.Enabled = Enable
Next ctl
If Err.Number <> 0 Then
Debug.Print "Error: " & Err.Description & " Could not find Control ID:=& ControlID "
End If
On Error GoTo zero
End Sub
@ Thosmas先生;仍然有相同的错误。 –
在excel选项中,如果我启用默认接受所有宏,这个运行时错误91即将到来,如果我禁用宏并接受每次打开工作表时手动,那么一切都很好。请帮忙 –
哪条线给你错误? – 2016-07-18 06:40:22
哪一行发生错误? –