Error when opening file downloaded from internet

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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 '<<<.
VBA Code:
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

Board Regular
Joined
Apr 3, 2011
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
    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

Board Regular
Joined
Apr 3, 2011
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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...:)(y)
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,176
Messages
5,640,611
Members
417,157
Latest member
nirmalp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top