Geek Answers Handbook

Excel VBA ActiveWorkbook "nothing" when "Enable Editing" from ProtectedView

I have a VBA macro that is called from a spreadsheet function (user-defined function, UDF). When the spreadsheet is downloaded from the Internet, and the user has set the Trust Center settings accordingly, the spreadsheet will open with the so-called "Protected View". The function will not be called. The "Enable Editing" button is displayed. If the button is pressed, the spreadsheet is “trusted” and resumes normally, starting the calculation and, therefore, invokes a user-defined function.

However, in this VBA function, the value Application.ActiveWorkbookis equal Nothing. This can be checked in the debugger.

Since I just need to read some properties (such as the path name) of the spreadsheet, I could also check the availability Application.ActiveProtectedViewWindow, which should reference a protected version of the workbook. You can check this object in the debugger. However, when launched in a release (without debugging), the value is Application.ActiveProtectedViewWindowalso equal Nothing.

Both behaviors — especially the first — seem to be a bug in Excel 2010 and 2013 (see also the post on the MSDN forum ).

Question : Is there a way to access the properties of the active book after it has been enabled for editing?

PS: Siddharth Rout, "ThisWorkbook" : . UDF XLA. , ThisWorkbook XLA. ActiveWorkbook (= , UDF) ThisWorkbook (= UDF).

:

  • , Excel, .

  • . XLA.

  • - .

+4
5

: , . - - ActiveWindow.Parent ActiveWorkbook.

.

Private Sub Workbook_Open()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ActiveWorkbook Is Nothing)
End Sub

True

, , False

Private Sub Workbook_Open()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ThisWorkbook Is Nothing)
End Sub

...

: , ?

. ThisWorkbook ActiveWorkbook

Protected Mode, ActiveWorkbook. , .

Private Sub Workbook_Activate()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ActiveWorkbook Is Nothing)
End Sub

, False

, , ActiveWorkbook .

Private Sub Workbook_Activate()
    MsgBox ActiveWorkbook.Path
End Sub

, Protected Mode

enter image description here

ActiveWindow.Parent.Path ActiveWorkbook.Path .

+6

Application.Caller.Parent.Parent Application.Activeworkbook

+1

, () , .

ActiveWorkbook, , ActiveWorkbook.Path.

ActiveWorkbook.Path Application.RecentFiles(1).Path, . , "Enabled Editing". , , : , , .

(: ActiveWorkbook.Path , Application.RecentFiles(1).Path , -).

+1

, , ;)

-

Dim wbk as Workbook
Set wbk = Application.ProtectedViewWindows(index).Workbook

Warning:
ActiveSheet returns Nothing when the active window is also protected.

Dim wsh As Worksheet
Set wsh = wbk.Worksheets(index)
+1
source

Try using this code.

If (UCase(ActiveWorkbook.Name) = ucase("<YOUR XLA NAME WITH EXTENSION>")) Then 
    End
End If
Set wbObj = ActiveWorkbook

The first time you run a macro, it just ends without doing anything. The second time, he selects the correct file.

0
source

More articles:


All Articles