Error when opening file downloaded from internet

[chriscorpion786]

Hi All,

I have a macro file that has Workbook open code that minimizes the ribbon and hides a sheet along with hiding the formula bar....
To check the file if its working correctly as I would like to distribute it to my colleagues, I sent it to my other laptop through email and when I opened it up on the other laptop downloaded from the internet, it gives me a "run time error'91' object variable or with block variable not set". Another error it throws up is " Run time-error 1004, Method sheets of object' Global failed".
Any solution and idea what could be causing this. When the same file is opened on the source laptop where I created it, it works absolutely fine without any errors.

Private Sub Workbook_Open()

With ActiveSheet
.Unprotect Password:="VBA"

With ActiveWindow
' .DisplayHorizontalScrollBar = False
' .DisplayVerticalScrollBar = False
End With
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ActiveWindow.DisplayHeadings = False
End With
With Application

.CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
End With
ActiveWindow.Zoom = 80
Sheets("Controls").Visible = xlVeryHidden
Sheets("CALENDAR").Activate
Range("N2").Value = Environ("Username")

Protectsheet

End With

End Sub

********************************

Sub Protectsheet()

ActiveSheet.Protect Password:="VBA"
Exit Sub

End Sub

 

[GWteB]

In your code, a few With ... End With statements are in the wrong place. In addition, it is uncertain which worksheet was active when your workbook was saved. So on one occasion this results in an error when opening the workbook, on the other hand it does not. This has nothing to do with forwarding your workbook by email. I changed the lines with '<<<.

Private Sub Workbook_Open()

    ActiveSheet.Unprotect Password:="VBA"
    
    With ActiveWindow
         .DisplayHorizontalScrollBar = False
         .DisplayVerticalScrollBar = False
    End With
    
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .ActiveWindow.DisplayHeadings = False
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
    
    ActiveWindow.Zoom = 80
    Sheets("Controls").Visible = xlSheetVeryHidden      ' <<<<<<
    Sheets("CALENDAR").Select                           ' <<<<<<
    
    Range("N2").Value = Environ("Username")
    ActiveSheet.Protect Password:="VBA"
End Sub

 

[chriscorpion786]

Hi, thanks for the code adjustment, at the moment I'm trying it out by stepping one line at a time using F8, as I initiate the line :
Sheets("CALENDAR").Range("N2").Value = Environ("Username"), the code jumps to another event that i have which is as below...The same thing happened even before I used your code that is the reason I used protectsheet macro and then exit sub so that it would not jump to the worksheetchange event. Could you see the error in the below code why it jumps from that line to below code. And by the way, these errors only happen first time when you download the file and enable editing , when you save the file on your desktop and open it second time or further the codes work fine..really cant guess whats happening and why...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim vldrng As Range
Dim startdt As Date
Dim enddt As Date
Dim VL As String

'User chooses the month and year...there are two vaildation cells for choosing month and year
Set vldrng = Sheets("CALENDAR").Range("K2:L2")

'if the user has made a selection
If Not Application.Intersect(vldrng, Target) Is Nothing Then

startdt = Sheets("Controls").Range("J2").Value
enddt = Sheets("Controls").Range("L2").Value

'if there is no filter on the sheet then turn it on, if there is then filter it
If Not Sheets("Tasks").AutoFilterMode Then
Sheets("Tasks").Range("A1").AutoFilter
Sheets("Tasks").Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startdt, Operator:=xlAnd, Criteria2:="<=" & enddt
Else
Sheets("Tasks").Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startdt, Operator:=xlAnd, Criteria2:="<=" & enddt


'make the selection of A1 on Tasks sheet
Sheets("Tasks").Select
Sheets("Tasks").Range("A1").Select
Sheets("CALENDAR").Select
Sheets("CALENDAR").Range("K2").Select
End If
End If

Application.ScreenUpdating = True

End Sub

 

[GWteB]

Hi, thanks for the code adjustment, at the moment I'm trying it out by stepping one line at a time using F8, as I initiate the line :
Sheets("CALENDAR").Range("N2").Value = Environ("Username"), the code jumps to another event that i have which is as below...

In this situation your post #3 code (the Worksheet_Change event procedure) is in the module of CALENDAR sheet and is triggered by the change of cell N2. That's native Excel behaviour.

The same thing happened even before I used your code that is the reason I used protectsheet macro and then exit sub so that it would not jump to the worksheetchange event.

Your separate protectsheet macro could not prevent that, because the program flow would then return to the calling sub, in this case to the Workbook_Open procedure. The Exit Sub statement is only effective in the sub where it is in, and in this case superfluous because it is just before the End Sub statement (which has the same effect, ie returning to the calling sub). Furthermore, your protectsheet macro was called after the Sheets("CALENDAR").Range("N2").Value = Environ("Username"). In case you had switched those two lines of code, an error would pop up due to the protection of your sheet. If you don't want the Change_Event to be triggered you could use:

    Application.EnableEvents = False
    Sheets("CALENDAR").Range("N2").Value = Environ("Username")
    Application.EnableEvents = True

Could you see the error in the below code why it jumps from that line to below code. And by the way, these errors only happen first time when you download the file and enable editing , when you save the file on your desktop and open it second time or further the codes work fine..really cant guess whats happening and why...

As far as I can see your post #3 code has no errors in it. The issue you're having is caused by Excels so called "Safe Mode". Safe Mode kicks in (among other things and for safety reasons) when you open a workbook which is attached to an email. As a personal remark: opening such an attachment directly from within your email is never a good idea.
Although you're enabling editing, Safe Mode is causing you troubles here. This is why:
- when the Workbook_Open event procedure is running , Excel remains in Safe Mode;
- in Safe Mode all (manual) changes to the Workbook are ignored / blocked;
- attempts to make changes through VBA (eg. Sheets(1).Select or Range("A1").Value = nn) always result in a Run-time Error;
- Safe Mode does not end until the Workbook_Open procedure has completely finished.
As a side note, using Userforms (regardless Modal / Modeless) during Safe Mode is useless.

Hopefully this makes things clearer.

 

[chriscorpion786]

Wow...that's very insightful advice...I will adjust the codes accordingly and see any workarounds that are needed. I've really learnt a few things here...
Much appreciated and thank you so much...

 

[GWteB]

You are welcome and thanks for letting me know.