Workbook Open Event Error

Feb 21, 2013

Have some code in the workbook open event, which when I run when the workbook is already open works fine, however when triggered when the workbook is actually opened it fails on the first line.

Code:
Private Sub Workbook_Open()
Dim lngLastRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim X As Long
Dim Y As Long

'Define Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Sheets("Sheet1").Activate

View 2 Replies


ADVERTISEMENT

Object Defined Error When Code In Workbook Open Event

Sep 7, 2007

I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.

The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.

The code is below;

Private Sub Workbook_Open()

DoEvents

Dim Res1 As VbMsgBoxResult
Dim GovRng As Range

For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng

Exit Sub

View 6 Replies View Related

Workbook Open Event Not Working

Nov 29, 2011

In R1 I sometimes place a cell reference that I want the workbook to immediately go to when it is next opened. I have the following code which does not work as expected (Ignore the necessary .Select parts):

Code:
Private Sub Workbook_Open()
Sheets("MSCI Asia Data").Select
Range("R1").Select
If Not IsEmpty(ActiveCell) Then Application.Goto reference:=Range(Range("R1").Value), scroll:=True
End Sub

Currently R1 contains the value R91 but when the workbook opens, it is not going to the cell R91 in the required worksheet.

View 9 Replies View Related

Open Another Workbook For Multiple Userform Event Use.

Sep 12, 2009

The use of this userform is to find a customer reference number. Im using two combobox's and a textbox.value that are populated from another workbook.

Though the way it is at the moment it opens the workbook and closes the workbook every time a new value is set to one of the combobox's.

I want to open the workbook on the useform initialize and do everthink the useform need from it. And then on the userform terminate close the workbook. Or somehink to this equlivent so this process of finding the customer referance number goes faster.

View 6 Replies View Related

Workbook Open Event :: If Workbook Is Already Open?

May 29, 2008

I have an Excel application in which I use the Workbook Open event to show a userform.

This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.

View 9 Replies View Related

Worksheet Open Event: Macro Run Every Time A Sheet In A Workbook Is Accessed

Jan 5, 2010

Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.

View 2 Replies View Related

Workbook Open Error ...

Oct 23, 2008

The below code line is causing the workbook to debug then fail to open for 50% of users and i dont know why, has anybody got any ideas.

Could it be bacuase it cannot read the machines time or date ?

Code: If day is Sat or Sun, or if time is after 3pm it calls a macro.

Private Sub Workbook_Open()

If Weekday(Date) = 7 Or Weekday(Date) = 1 Or Time >= TimeSerial(15, 0, 0) Then

I cant test the machines its failing on as i'm working from home !

View 9 Replies View Related

Workbook.Open Error 1004

Sep 6, 2009

This is what i am doing, i m trying to open file one after other in a folder, and process each of them for some information in it and then closing it without saving it. and looping through next.

Code runs great the first loop record, when it tries to get into loop second time, it breaks with error 1004: run time.

Workbook.Open failed...... I have no clue, as files are there folder is there and code runs the first time...

View 9 Replies View Related

Excel Error Every Time Open The Workbook?

Oct 24, 2013

i get the following error everytime i open my workbook

Removed Records: Object from /xl/printerSettings/printerSettings2.bin part (Print options)
Removed Records: Object from /xl/printerSettings/printerSettings3.bin part (Print options)

ive tried everything. ive attached the workbook (obviously with all data removed).

View 5 Replies View Related

Workbook Open Events Fires Once. Subsequent Opens Causes Error

Oct 3, 2007

I have a program that uses the Document Open event to display a custom form. This program is being used on about 50 computers for the past 8 years with no problems. One user has a problem now. They can open the program once and the code fires. But when the user tries to open the same file a second time, the "Microsoft Excel has encountered a problem and needs to close" dialog box is displayed. The document that is recovered has no vba modules and no code in the Document open event. I've uninstalled and reinstalled Office Professional. Shut down all firewalls.

View 9 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

Apr 6, 2013

I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.

I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.

I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need

A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)

I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.

week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost

1
2
3
4
5
6
7
8

View 7 Replies View Related

Sum Column Getting "circular Reference In An Open Workbook" Error

Feb 19, 2009

i am trying to do a sum of my columns but it's not calculating at all.I keep getting "circular reference in an open workbook" error. This is the only workbook opened.

View 4 Replies View Related

Make Workbook Open- Open Correct Month Workbook

Jul 8, 2006

I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook

open macro-

Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub

This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?

View 9 Replies View Related

Open Event Isn't Working?

Jun 19, 2013

So I have a sheet that has a calendar control on it. I'm trying to make it so that when I open excel it automatically sets the calendar to todays date. I'm using the code:

VB:
Private Sub Worksheet_Open()
Calendar2.Value = Date
End Sub

If I click the play button the calendar changes to today's date. However, when I open excel it doesn't work. I saw screenshots online where the dropdown box to the right (in VBA) that lists the events had 'open' listed there. Mine does not. It appears as if the 'open' event is missing from the 'library'.

View 3 Replies View Related

DblClick Event To Open Another UserForm

Jul 20, 2006

How can I use a ComboBox Dbl_Click event to open another UserForm ??? The event exists, but how it works ???

View 5 Replies View Related

Automatically Open New Workbook After 1st Workbook Open 4 Seconds

Nov 6, 2009

I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.

I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.

I'm sure there is a function someplace for this that can be used in a macro.

Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.

View 14 Replies View Related

Open Non-Excel File Using ListBox_DblClick(...) Event

Apr 11, 2008

I have a Listbox which brings up a list of files in 2 folders. The code works perfectly and I have no problems with it however I would like to add a further bit of functionality to the ListBox using:

View 12 Replies View Related

Cell Click Event Open The Form

Apr 23, 2008

Is there a way that when a cell is clicked in a worksheet. that I can get a user form to open?

View 9 Replies View Related

Prevent Event Code Running When Other Workbooks Open

Aug 23, 2008

I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.

Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .

View 7 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

Change Event Error

Dec 9, 2009

I was wondering what I can add so this will not error when I delete the contents of the target range,

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("C11:C29, E11:E29")) Is Nothing Then
If Target.Cells.CountLarge > 2 Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target)
end sub

View 9 Replies View Related

Before Save Event Error

Sep 21, 2006

Here is my existing code...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application. ScreenUpdating=False
' Cancel user prompted save procedure
Cancel = True
' Code removed... changes some values and settings before saving
ThisWorkbook.Save
' Code removed... return the original values and settings
Application.ScreenUpdating=True
End Sub

For some reason, the workbook is not being saved when the above code is executed. It seems the problem lies with "ThisWorkbook.Save"... in that it just isn't saving! If I delete "Cancel=True", the workbook will save (obviously!).

I have spent an hour trying to work this out and have come to the conclusion that it is something other than my code... has anyone experienced this problem before?

View 9 Replies View Related

Before Close Event Error

Oct 28, 2006

I'm using the following code in "ThisWorkbook" of a couple of different workbooks, and the problem occurs consistently with the code use regardless of which workbook...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close False
End Sub

The code itself does exactly as it should (not save changes when closing), but shortly after the workbook has closed, any other open workbooks will freeze, then give a popup message saying an error has occurred and a log is being generated, and EXCEL shuts down (loosing any other work)...

I have searched my HDD and only found 4 .log files that reference dates that coincide with the errors, although the times don't match(?)...

Originally Posted by WinMgmt.log
(Fri Oct 27 08:12:29 2006) : core is being shut down by WinMgmt.exe, it returned 0x0

Originally Posted by wmiadap.log
(Fri Oct 27 08:17:54 2006) : Performance library winspool.drv will be ignored as it was previously disabled (WbemAdapStatus = -1).

Originally Posted by wbemcore.log
(Fri Oct 27 08:47:10 2006) : Core physically unloaded!

Originally Posted by gotomon.log
Log file started at Fri, 27 Oct 2006 08:17:46 Eastern Daylight Time
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0x0
0:00:00 i: G2EnumPortsCore() buffer available:0 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leaving early --
0:00:00 i: G2EnumPortsCore() -- start -- level=1 pPorts=0xc55980
0:00:00 i: G2EnumPortsCore() buffer available:24 buffer needed:24
0:00:00 i: G2EnumPortsCore() -- leave --

I had been using;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save True
End Sub

Which had the same error... when I changed it to Close, I thought it had been fixed, but it was just that the delay is longer before the freeze.

View 4 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

Event Macro Error When Selecting More Than One Cell

Nov 13, 2008

I'm using a macro that picks up on changes in a active cell.. This is the code

HTML Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Value <> "" Then

ThisWorkbook.Sheets("Log").Cells(Target.Row, 2).Formula = "=Today()"
End If
End If
End Sub

There's a problem with this code, when select from a range and hit delete, i get a error mismatch. I want to have the macro ignore when i select active cell range of greater then 1.

View 2 Replies View Related

ComboBox Change Event Gives Error 1004

Jan 3, 2013

The following code works fine, but when I put the code in an ActiveX Combobox Change Event it gives a run-time error 1004. ("Select Method of Range class failed")The error occurs on the following line

Code:
Worksheets("SAVED").Range("A" & l).Select

Code:
Dim l As Long
Application.ScreenUpdating = False
l = Application.WorksheetFunction.Match(Worksheets("DATA").Range("O34"), Worksheets("SAVED").Range("A1:A10000"), 0)
Worksheets("SAVED").Activate
'ROSTER===
Worksheets("SAVED").Range("A" & l).Select
Selection.Resize(1, 739).Offset(1, 2).Copy

[code]......

View 2 Replies View Related

Excel 2010 :: Combobox Event Error

Jul 10, 2013

Using Excel 2010. I have a ComboBox (Not ActiveX) on a worksheet

I'm trying to add an event so that when user click on a choice a macro is fired using the selected item as citeria for a filter When I click on the drop down and make a selection I receive this error msg:

Cannot find the macro "cboUserSelection_Change" The macro may not be available in this workbook or all macros may be disabled

I also tried _AfterUpdate - similar error message.

I added the Combo Box code to the Worksheet code, not a standard module

I was able to run a quick test macro so I do not believe "all macros are disabled"

View 1 Replies View Related

Worksheet Change Event Intersect Error 91

May 13, 2007

This is my
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or Intersect(Target, Range("F6:F3000")) = "" Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or Intersect(Target, Range("I6:I6000")) = "" Then
Exit Sub
Else

Do Something......
End If
End If
End If
End Sub

when excution comes to this line
If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then

a run time error (91) appears, it tells object variable or with block variable not set.

View 9 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







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