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
ADVERTISEMENT
Jul 31, 2008
I am trying exactly the same things stated in here Open two workbooks at the same time as stated in the other thread's answer I would love to "just use the Workbook name IF both Workbooks are in the same folder." both files are in the same folder and this is the code that I am trying to use
Private Sub Workbook_Open()
Workbooks.Open ("toto1234.xls")
and it does not work any one explain me what I am doing wrong?
View 2 Replies
View Related
Jan 8, 2009
I have written a very basic Macro code to retrieve a photo off of my server. I want it to run this macro when a person has selected a certain item off of a drop down list. The problem I am having is that when I try and run the macro manually or when the workbook opens I get a an error message that reads "Compile Error: Invalid outside procedure". I click "Okay" and it highlights this portion of the code
View 5 Replies
View Related
Jun 24, 2014
I'm trying to introduce some VBA code to an existing workbook that will automatically sort column A alphabetically on Sheet 2 (Summary) (excluding rows 1 & 2 as they contain headings) whenever the workbook is opened.
At the moment this code doesn't automatically run and when I run it manually I get a run-time error '91' the first time I try and when I run it for a second time (again manually) it works.
The code I've got at the moment is:
VB:
'The following code will run the sorting subroutine when the workbook is opened:
Private Sub Workbook_Open()
Call SortByWave
End Sub
'And this subroutine will enable the autofilter functionality, clear all existing filters and then filter the data on column A:
Sub SortByWave()
[Code] ......
View 5 Replies
View Related
Feb 15, 2008
I have a requirement to create a master spreadsheet using a macro to copy set cells from several other spreadsheets and paste them into the master one in a list for accounting purposes.
So far the macro reads in the spreadsheet filename(s) and copy/pastes the cells to the master one - I need to be able to paste the values onto subsequent rows for each input sheet which is where my problems lies. Currently the row just pasted in is overwritten over by the following one, I need to go to the next row down but am stuck on how to do it.
Sub EmployeeExp()
'
' EmployeeExp Macro
' Macro recorded 12/02/2008 by eugene.cross
'
Dim i As Integer
Dim f As String
I've attached an example master output sheet to show what I want to produce, albeit this has only one row!
View 7 Replies
View Related
Dec 3, 2008
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
way to suppress the code in the target workbook I open through workbooks.open
View 9 Replies
View Related
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
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
View Related
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
Jul 25, 2014
What I want to do is take information from a main sheet and pull certain rows (determined based up on the selection made in the delivered to column) and move it to a worksheet that contains information only for that individual entity. Below is the list of headers in my lis of all information
Ticket #DateDelivered ToNet kg WtTonsRunningTotalDaily Total
As I said, depending upon the name in the delivered to, I would like to carry forward the following information only for the specified vendor. For example if we have 3 entries 1 delivered to company a, 1 to company b and 1 to company c, each would carry forward to the respective worksheet for that vendor.
Ticket #DateDelivered ToTons
how I might accomplish this in a usable format for what I am trying to do.
View 2 Replies
View Related
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
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
Oct 4, 2008
How do I run this I can not get it to work I keep getting errors this is the way I have it for now.
Private Sub ThisWorkbook_Open()
MY_MONTH = Month(Now()) + 5
With Sheets("Master List")
.Unprotect ("123")
.Columns("A").Locked = False
.Columns("A").Hidden = True
.Columns("T:IV").Hidden = True
.Rows("265:65536").Hidden = True
.Columns("E:Q").Locked = False
.Columns("E:Q").Hidden = True
.Columns(MY_MONTH).Hidden = False
.Columns(MY_MONTH - 1).Hidden = False
.Columns(MY_MONTH - 1).Locked = True
Dim ws As Worksheet
For Each ws In Sheets([{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC","2008","Read Me","Record"}])
With ws
.Protect ("123")
End With
Next
End Sub
Compile error: Expected End With
View 9 Replies
View Related
Jan 31, 2010
I would really like to know if there is a way that when my workbook opens if the following can happen?
- Not display the tabs (NOT "HIDE" the sheets, just not be able to view the tabs themselves)
- Go to full screen (not display ribbon)
View 6 Replies
View Related
Jul 16, 2008
My workbooks contains 2 worksheets with data and autofilters on for each column in use.
When my workbook opens I am trying to reset the autofilters of each worksheet in the workbook and to filter the data according to one criteria in one column.
This is what I have that works to reset each worksheet but I haven't been able to figure out how to subsequently filter each worksheet.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
For Each w In Worksheets
w.Unprotect
If w.FilterMode Then w.ShowAllData
w.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Next w
Application.ScreenUpdating = True
End Sub
The autofilter sits in A3 to N3 and I am trying to filter according to column M (i.e. 13th column)
View 9 Replies
View Related
Oct 27, 2006
I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?
Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.
View 9 Replies
View Related
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
Nov 24, 2009
I need to stop the calculations when the workbook opens. I have tried
View 2 Replies
View Related
Jan 6, 2010
I have a workbook on a share drive that is used by multiple people. In the event that someone leaves the workbook open after using it, I have a timer function that pops up a splash screen after 4 minutes of inactivity which states, "This workbook will close in 1 minute if there is no further activity". If there is no further activity in that minute, the workbook closes.
The code works fine...unless the workbook is manually closed in that minute between the splash screen and when the timer would have closed the workbook. If that happens, the workbook closes normally and then briefly reopens and closes a minute later when the timer would have closed the workbook. Is there any code to prevent this?
View 4 Replies
View Related
Mar 7, 2012
I have the following code in a module that I would like to initiate when the workbook opens:
Code:
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" ( _
ByVal lpBuffer As String, _
ByRef nSize As Long) As Long
Public Property Get ComputerName() As String
[Code] ........
In a worksheet, I have =compname() in cell A1 and =hdserialnumber() in A2. What is the trick to make the values in these cells appear as soon as the worksheet opens?
View 2 Replies
View Related
Oct 21, 2008
I am trying to write a code where user has to respond to input box option depending on yes or no selection. There are two types of responses to different questions. One needs to respond between 1 and 100 (if yes) and for some other questions needs between 1 and 4 (if yes). A "no" will enter zeo value. But it is not working. I am using two named ranges "VALIDCELLS" (for 1 to 100) and "FREQVALIDCELLSS" (for 1 to 4). Here is the code;
View 6 Replies
View Related
Apr 29, 2008
I'm new to VBA so my i'm having alot of problems figuring out simple stuff.
Below is my script and when i run it, the dates turns out incorrect.
I noticed this only happens to dates that are before 13th of each month.
Example,
1st May 2008 ( 01/05/2008)
will turn up as
5th Jan 2008 ( 05/01/2008)
However when i manually open the file Todays trades.csv
The date looks just fine.
Is there some problem with using VBA to call up the file?
View 9 Replies
View Related
Jun 11, 2013
I need to set the "Sheet1" to be the sheet that always displays first, regardless of what sheet was active when last saved.
The reason is that I have a sheet with instructions on it as how to use the Workbook, including "You must Enable Macros" etc. When Macros are enabled, it's all good, as most people use a USERFORM which I have coded to set "Sheet1".Select and save their work upon closing using the USERFORM.
BUT when the advanced users modify other Sheets directly without using the USERFORM, and "SAVE" and then "Close" , the next time a user opens the Workbook (If they do not enable Macros) it opens to the sheet the advanced user Saved on. Thus killing my instructions page and putting the exact people I did not want messing around on certain sheets, on those sheets, fully bypassing the USERFORM created to avoid the problem.
I can force the workbook to switch to "Sheet1" and Save and Close, every time someone closes or EXITs, BUT I quickly realized when testing that any mistakes made are then permanent because you cant EXIT without Saving.
So. Is there a setting somewhere (Not Requiring Enabled Macros) to force a Workbook to ALWAYS open "Sheet1" first, regardless of what Sheet was last saved in the Workbook?
View 8 Replies
View Related
Nov 28, 2012
Is there a way of displaying a message box on the first of every month when a workbook opens ?
View 2 Replies
View Related
May 18, 2009
I have a workbook where the sheets are all protected and I want to stop users scrolling up or across beyond the limits of the input areas.
I have used the following code (or variations of)...
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G32"
End Sub
This works fine except that when I first open the workbook, the first sheet displayed can still be scrolled. As soon as you move to another sheet and then back again, it is then OK.
It appears that the Worksheet_Activate code does not execute when the workbook is first opened.
How do I get round this?
View 9 Replies
View Related
Aug 26, 2006
I am trying to find a way to automatically do a query and dump the data into a sheet when a previously created workbook is open. I know how to get the data (use the Tools, Import External Data option to retrieve data from Access) but what I don't know is how to make it do it automatically when the workbook is open.
View 2 Replies
View Related
Aug 6, 2007
I have a work book that contains 10sheets. considering one as main sheet remaning as sub sheets. I want to open main sheet from all other remaining sheets using a common button.when I tried normally is shows compile error: Ambiguous name detected. Any other way to solve it.
View 7 Replies
View Related
Mar 14, 2008
I have a problem, where every time I close or open any workbook, Excel prompts me to "Enable or Disable Macros" in one particular workbook, wether I have tried to open it or not. In the said workbook, I have a macro that automatically updates a pivot table upon selecting the worksheet where the pivot table resides.
The code for it is as follows:
Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub
Private Sub Worksheet_Activate()
Call UpdateIt
End Sub
There is something in the code that calls UpdateIt any time a workbook is opened, closed or selected.
View 3 Replies
View Related
Sep 13, 2007
Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?
View 3 Replies
View Related
May 3, 2013
When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
View 2 Replies
View Related
