Workbook_Open Doesn't Run On Second Open

Dec 29, 2009

I have noticed this on more than one workbook with an Workbook_Open macro. When you open Excel, open Workbook1, do some work , save or don't save and close, then reOpen Workbook1 without having closed the Excel application, the Workbook_Open macro in Workbook1 doesn't run.

Closing Excel and reopening Workbook1 initiates the Workbook_Open macro. It's as if Excel remembers having previously opened Workbook1 and so it doesn't rerun the Workbook_Open macro the next time you open it.

View 3 Replies


ADVERTISEMENT

Private Sub Workbook_Open() Doesn't Execute

Feb 25, 2008

why the code below might not execute ? It just seems like it skips over the code and doesn't run at all.

Private Sub Workbook_Open()
'AssignRequestNumber()
MsgBox ("Whoa")
Run "AssignTrackNumber"

End Sub

I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.

View 9 Replies View Related

Both 'Private Sub Workbook_Open' And 'Auto Open' Run Sequentially

Aug 1, 2009

I have built a complex vba & multisheet spreadsheet that I am looking to secure against all the common attacks. So I have:A Workbook Open pw;
VBA password (29 symbols/numbers/Caps/lower case)
Very Hidden worksheets
Hidden rows/columns
Restricted scroll areas
Workbook protection
Code that auto protects all sheets upon opening
Registry referencing in Workbook Open with timed closure if not matched
Now I am on the last leg of implementing protection against Application.EnableEvents = False; force enabling of Macros and hiding of toolbars, scrollbars etc... Phew.

Soooo, in my research, I have learnt that if EnableEvents = False, Workbook_Open is essentially skipped and the security VBA routines are disabled. To get around, I have copied the entire contents of the Workbook_Open routine to a module under Auto_Open. The first line of both these scripts is: Application.EnableEvents = True.

Works! So far so good.
However, I have a Msgbox prompt in the scripts that displays twice. So in essence, Excel is running Workbook Open first, then Auto Open second.
If I open another instance of Excel and run Application.EnableEvents = False first before opening my spreadsheet, I only get one message. So only the Auto Open script runs.

View 9 Replies View Related

2007 Doesn't Want To Open WB Created In Code

May 17, 2007

In my first workbook I select an area to copy/paste into a new wb with this code (from the board)

[Set source = Range("A55:K109").SpecialCells(xlCellTypeVisible)

Dim FName As String
Dim VBComp As VBComponent
Dim wbnew As Workbook
Dim x As String

Set wbnew = Workbooks.Add

Set dest1 = ActiveWorkbook

dest2.Activate
With ActiveWorkbook
FName = .Path & "code.txt"
If Dir(FName) "" Then
Kill FName
End If
For Each VBComp In .VBProject.VBComponents
If VBComp.Type vbext_ct_Document Then
VBComp.Export FName
wbnew.VBProject.VBComponents.Import FName
Kill FName
ElseIf VBComp.Name = "ThisWorkbook" Then
If VBComp.CodeModule.CountOfLines > 0 Then
wbnew.VBProject.VBComponents

The complete code in this workbook sends the entire workbook to one person and it creates a new workbook and pastes a range and the VB code needed to run the button it copies and sends this to another person.

The codes runs excellent in 2003. In 2007 I can get the entire workbook to send to the first person, but

The email to the second person will send, but they can not open the workbook. I get the error "File Format or file extension is not valid. Please verify file is not corrput and that extension matches file.

I know trust access to visual basic has to be enabled in 2003 for this code to run, so in 2007 I went to the Excel options trust center and clicked the box which says Trust access to the VBA project object model.

When I step through this code everything looks great. The worksheet area is correct, the button is copied, the code can be seen in the properties window, but then I get the email and it will not open.

There is even a line of code that kills the file after the mail is sent (temporary saving location is the desktop). So, I used a ' and made it a comment line so I could try to open the file from my desktop. It will not open there either. File extension being used is .xlsm

View 9 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

Remove Modules In Multiple Open Files Results In Error If Module Doesn't Exist

Jan 30, 2008

I have a number of similar templates on a server used to produce quotations from other files with lookup formulas. They all have 2 modules, 3 & 4. Module 3 deletes certain data and shows values instead of formulas for most of the pricing etc. Module 4 contains a macro that logs info in another central workbook on the server.

If 2 files are opened based on the same template at once, when the Quote_Wrapup macro (in module3) is run on one of the open files(code follows) from a button on the spreadsheet it often produces a Run-time error '9' Subscript out of range error.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("D8:E9").ClearContents
Range("D8:F9").Interior.ColorIndex = xlNone
Range("qdata5").Font.ColorIndex = 2
Range("qdata6").Font.ColorIndex = 2
Range("A18:A1018").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value
Range("A980") = Range("A980").Value...................

View 8 Replies View Related

Click Help On This Function My "Help" Doesn't Open

May 31, 2009

Why when I click jelp on this function my "Help" doesn't open. opens only grey window....

View 3 Replies View Related

At Startup .xls Doesn't Open At Startup Any Longer- 2007

Apr 25, 2008

I use Excel 2007 and I have an .xls that I've had set to auto-open at startup and has worked fine for months now. Alll of a sudden a couple days ago it stopped auto-opening, and I have to now manually open the .xls. I looved in the Advanced>At Startup setting and it's set to the correct folder. What other setting in Excel 2007 do I need to check that would be causing this startup problem?

View 9 Replies View Related

Modify Code So It Doesn't Run If Sheet Doesn't Exist

Oct 24, 2011

Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.

Code:

Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange

[Code] .........

View 6 Replies View Related

Workbook_open() To Every Workbook

Jun 4, 2008

I want to use this command when I open every workbook.

How do I do that? I know you're supposed to put the macro in the thisworkbook module in the workbook, but I have 600 workbooks that needs to be changed. That is just not a good idea. I've tried to put the macro in the workbook called own.xls (I use a swedish version of excel, dont know what it is called in english) thisworkbook,

View 9 Replies View Related

Workbook_open Yes/No Popup

Nov 18, 2009

I script that on open would pop up asking if a sync was done with an option for yes or no. If yes is pressed then it would just open, if no selected it would give a message "must sync before use" and close the workbook.

I do not want this message to pop up when other spreadsheets are open when this one is still open so im guessing private workbook_open

View 9 Replies View Related

Workbook_open Not Firing

Jun 26, 2007

I am using the following code in my Workbook_Open sub, located in the ThisWorkbook code module. I am enabling macros when opening. All other VBA code in the project is running correctly.

Private Sub Workbook_Open()
Worksheets("Sheet1").init
MsgBox "starting"
End Sub

The init sub did not appear to be running so I inserted the MsgBox to confirm, but that doesn't come up either.

View 6 Replies View Related

Workbook_Open() In Separate File

Oct 14, 2008

I have an Excel add-in file (stored .xla format) that is used by a lot of other spreadsheets on a network location because it is modified often, and all workbooks made from a template reference it. everything with that works fine, and any changes made to the add-in file are always reflected upon startup in the workbooks. So there isn't a problem with that. Recently I've had to add a Workbook_Open() sub into this add-in (which I wrote in 'ThisWorkbook'). However, this macro is never entered by any of the workbooks. Is it possible to have a workbook_open sub in another file? If so, what can be causing it to fail (there aren't any errors or anything like that in the sub)?

View 4 Replies View Related

Opening Userform With Workbook_Open Sub

Dec 9, 2008

I have 3 userforms:
COS
COSP
SLevel
and I have the code

Private Sub Workbook_Open()
COS.Show
End Sub

View 9 Replies View Related

Workbook_open Runs On Closing

Feb 16, 2007

I've a problem with the below code.

Option Explicit

Private Sub Workbook_Open()
Dim x As Date
x = InputBox("Enter End Date!")
Range("B2") = x
With Application. CommandBars("File")
.Controls("Save").Enabled = False
.Controls("Save").Visible = False
.Controls("Save As...").Enabled = False
.Controls("Save As...").Visible = False
End With
With Application.CommandBars("Standard")
.Controls("Save").Enabled = False .................

View 9 Replies View Related

Disable Workbook_open From Another Macro

Jun 14, 2007

from 1 sheet i am opening several other worksheets and read in the bits i want.
i am finding that on occasions the workbook_open on these sheets is causing great performance issues because of the calc mode set to automatic in the open routine of the individual sheets. i dont need it to be set to auto but the sheet owners perfer it to be set like this and i cant turn it off their sheets. is ther a way of opening a workbook from a macro and then override its workbook_open routine?

Set wb = Workbooks.Open(sSourceFolder & "" & "mySheet.xls", False, True)

i cant find any other switches that i could use to disable this.

View 2 Replies View Related

Disable/skip Workbook_open Function

Apr 20, 2006

i have a workbook iam using as a template.(workbook "template") the user opens which then requests a job number which will then saves the workbook as the enterd job number. this workbook also contains all the material data. eg: price and type which is on sheet "data". I am hopin to get to the sheet "data" from another workbooks command button by bypassing the job enter request by the workbook_open sub. basically i need some code that disables a workbook_open sub

View 8 Replies View Related

Kill And Insert Event In Workbook_open

Sep 22, 2006

Private Sub Workbook_Open()
If ActiveWorkbook. Name = "UserA.xls" Then
Call PreviewList
Else
Cancel = True
End If
End Sub

After the macro auto-startup, UserA works on the worksheet and input some procedures and save as ClientA.xls. However, before save as ClientA.xls and close, UserA wants to change the worksheet name and Procedure name as below, how to do that?

Private Sub Workbook_Open()
If ActiveWorkbook.Name = "ClientA.xls" Then
Call RevisedList
Else
Cancel = True
End If
End Sub

View 2 Replies View Related

Global Static Variables On Workbook_open

Jan 20, 2007

problems with Global Static Variables.

In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.

Here is what i did:

Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer

Private Sub Workbook_Open()

GlobalStartX = 6
GlobalStartY = 21

End Sub

View 4 Replies View Related

Multiple OnTime In Workbook_Open Event

Dec 6, 2007

the following workbook open event which repeats a macro called "Refresh" every 30 minutes. However, there is another macro "tame_blph" that i wanna call at 12 midnight everyday.

Private Sub Workbook_Open()

Call tame_blph

NextTick = Now + TimeValue("00:30:00")

Application .OnTime NextTick, "Refresh", , True

End Sub

Is it even possible to have multiple Workbook open events and/or multiple NextTick or something?

View 3 Replies View Related

Show User Form After Workbook_open Code Has Executed

Apr 19, 2006

i have this code which askes the user for a job number once the workbook is opened.

Private Sub Workbook_open()

' If sheet was named by original open routine, exit

If ActiveSheet.Name = "Main Roof" Then Exit Sub

' otherwise

Do
Returnvalue = InputBox("Please Enter a New Job Number.", "Information")

' Allow changes by entering q as the Job Number
If Returnvalue = "q" Then Exit Sub


' Delete the ' from the front of the following two lines and
' then when you enter q as the Job Number you will also be
' asked for a password. The default password is toe.

what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.

View 3 Replies View Related

Hide/delete/visble=false Userform From Workbook_open Sub

Apr 21, 2006

i have been trying to hide the userform from the workbook open event with no luck

Private Sub Workbook_Open()
ufmTheEstimator.Show
Dim Worksheet As Excel.Worksheet
If Me.Worksheets("Main Roof"). Name = True Then
ufmTheEstimator.Hide
End If
End Sub

View 4 Replies View Related

Workbook_open Event In Order To Stop Annoying Messages If You Haven't Physically Made Changes

Jul 3, 2007

I've been setting the above property to TRUE after making some changes in the Workbook_open event in order to stop annoying messages if you haven't physically made changes.

Unfortunately, it doesn't seem to copy through to the workbook_beforeclose event and I'm being asked if I want to save changes even though the macro made the changes and then set the property.
Here's my code for the open event :-

Private Sub Workbook_Open()
Application.ScreenUpdating = False
On Error Resume Next
For counter = Worksheets.Count To 2 Step -1
Sheets(counter).Visible = xlVeryHidden
Next
Sheets("Desktop").Visible = True
Sheets(1).Visible = xlVeryHidden.....

View 9 Replies View Related

Check If Workbook Is Open & Open If Not Open

Jul 1, 2007

I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.

View 5 Replies View Related

The Workbook Open Minimzed Until The The Splash Screen Closes Then Open Properly

Nov 20, 2008

My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.

View 5 Replies View Related

Macro -open The Look In List” And Stop So I Can Pick A File To Open

Oct 18, 2008

I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box

View 5 Replies View Related

Open Multiple Files From Array Of Filenames If Not Already Open VBA

Sep 15, 2014

I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....

VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer

[Code] .....

View 4 Replies View Related

Check To See If File Is Open, And If It Is Open, Activate It

May 14, 2009

I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:

View 5 Replies View Related

Force Re-Open Of Open Workbook With No Confirmation

Jun 11, 2009

this is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -

View 2 Replies View Related

Create Open Copy Of Open Workbook

Jul 30, 2009

Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved