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
ADVERTISEMENT
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
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
View Related
Jun 3, 2008
Is it possible to autonumber records created with data entered with a user form?
If it is possible - I would also like to know how I could display the current record number on the data entry form....
View 3 Replies
View Related
Aug 20, 2009
I am trying to set up an macro that runs when a workbook opens
I have placed the following code in the This Workbook Object
Private Sub Workbook_Open()
Worksheets("Test").OnEntry = "Every_Update"
With ThisWorkbook
Application.OnTime Now, "'" & .Name & "'!" & .CodeName & ".Workbook_Open2"
End With
End Sub
I am tyring to instruct Excel to run the macro "Every_Update" every time an entry is made in the worksheet "Test". This seems to fuction.
However, I have other code that I am tring to run in a a macro called "Workbook_Open2"
That macro contains functions that are not yet loaded into Excel during the Auto open sequence. I was hoping that by calling the subroutine, it would allow the functions to be enabled. Basically the function that I a trying to get is to open up another excel file and read a cell.
The code above gives me an error - it cannot find "Workbook_Open2" Should this not be in a module?
View 9 Replies
View Related
Aug 12, 2006
i am trying to run a macro automatcially when the workbook opens.
View 9 Replies
View Related
Nov 28, 2008
can i auto open the excel files in 1 folder in the correct path..
for example the path below
C:Documents and SettingsMy Documentsexample
i have many excel files in the folder there, i want it auto open 1 by 1 and do the marco, can do it?
View 2 Replies
View Related
Sep 29, 2009
If i have a userform: frmUpdateInfo
How do i have it auto open or at least be ready for use when going to a particular sheet in a workbook (Sheet name: "Matches")
View 6 Replies
View Related
Jun 6, 2012
I have excel file in my desktop and doing some report generation with that. i need to open that excel file for every 30minutes.
View 5 Replies
View Related
May 28, 2008
I have a excel file in which , there is probably a macro which runs when i open the file. The macro checks for a particular file on the D: drive.
I want to delete this macro. The problem is i cannot delete this macro, as it is not appearing in the Macros List.
Also i know i can disable this macro, by pressing the shift key whenever the file is opened.
But i want to delete this macro permanently from this file. I have tried various options like pression Alt-F11 & to locate the macro in module etc & also tried to locate it in the Workbook open event handler. But I cant see the macro.
How do i locate this macro & delete it.
View 9 Replies
View Related
May 22, 2014
I am trying to write a formula that will count rows. The hard part is when the project is cancelled I have the formula written to not count that row. However, I want the next valid row to be numbered sequentially. Here is the formula I have:
=IF($B2="","",IF($Q2="YES","",ROW($A1)))
So I want it to be numbered 1,2,3...., not 1,3,4.
View 9 Replies
View Related
Sep 13, 2005
I have a worksheet with 10 columns, and an ever number of growing rows.
What I would like to do is to Sort Column 'B', along with all the
other respective data in the other columns, each time the spreadsheet
opens. I would prefer to use VBA or some other auto-launching event.
View 9 Replies
View Related
Jun 7, 2011
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....
View 2 Replies
View Related
Nov 26, 2012
I have a menu workbook with macro buttons that call different workbooks. Call the menu workbook (switchgear.xlsm). Switchgear contains 2 buttons. Button 1 closes switchgear and opens Book1. Button 2 closes switchgear and opens book 2.
If book1 is open and nothing has changed for 3 minutes I would like it to close and open switchgear.xlsm. I do not want it to just close in 3 minutes... I want it to close after 3 minutes of inactivity.
I have search all over and tried and tried to achieve this without success...
I have been able to get the timer to work but it gets caught in a loop. If I open book1 and close it in less than a min then 3 minutes later out of nowhere the code tries to run???
Secondly when this happens I see several book1 vba projects loaded in the editor window???
View 9 Replies
View Related
Dec 21, 2006
I have an excel file stored on a network drive for the purpose of information sharing. (File protected with a password)
But some the guys leave the file open for quiet long time and hence I cannot open the file for updating the data.
-I need to have a macro that runs every 5 minutes and displays an alert message saying "Please close the File" as long as the file is kept open.
-A second macro with a modified version of the above to close the file automatically after 5 minutes from file opening time after showing an alert message "You cannot leave the File Open, File is Closed Automatically!"
View 9 Replies
View Related
Aug 8, 2007
Need vba code to automatically open sheet one when a workbook is opened? At the moment it opens on the page on which the workbook was last saved.
View 9 Replies
View Related
Jan 22, 2010
I am trying to create an Auto_Open macro that will check the date held in column I and if it is 60 days then it will leave it white.
View 9 Replies
View Related
Sep 7, 2006
I am trying to get my invoice sheet to automatically increment the invoice number when I open the workbook. I know it is in the code i.e. private sub? but it doesnt seem to work.
View 3 Replies
View Related
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
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
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
May 19, 2008
We have these worksheets that have 100 tabs each
each tab is named joel_1400, joel_1401...Joel_1499 insert data in each tab template as needed for RFI's. then we have to make another worksheet with 100 tabs for 1500 to 1599
what we are doing is copying the whole worksheet and then erasing all of the user fields and changing all of the names manually for each tab
View 14 Replies
View Related
Feb 22, 2010
I'd like to do is to use loops to copy each value in "Sheet1" into every other row in column B of "Sheet2" starting on row 16. I realize that this is a simple task, but I'd like to know how to do this for the purposes of learning the correct VBA coding.
View 7 Replies
View Related
Jul 27, 2009
I have a spreadsheet with a column of data that is similar for a while, then changes, is similar, then changes. I need to number them as each set of data changes, how can I go about that?
Here is an example of what I have:
Dog
Dog
Dog
Cat
Cat
Bird
Bird
Bird
Bird
Lion
Elephant
Elephant.....................
View 5 Replies
View Related
May 2, 2013
Suppose I'm a grocer and I use a spreadsheet to track my inventory. I sell fruits, vegetables, and dried spices. On one worksheet in my Excel file, I create the following lists:
FRUITS
VEGETABLES
Apple
Potato
Orange
Celery
[Code]...
Then on a second worksheet, I have my actual inventory, which is this:
ITEM
QNTY
PRICE
CLASSIFICATION
[Code]...
I need a formula for the "Classification" cells of the Inventory. For each "Classification" cell, I want the formula to do the following: Search for the corresponding "Item" in the "Fruits" list; if found, then put "FRUIT" in the Classification field.If not found, search for the corresponding "Item" in the "Vegetables" list; if found, then put "VEGETABLE" in the Classification field.If still not found, put "SPICES" in the Classification field.
So using my toy example:
The formula for the first row would search for "Banana" in the "Fruits" list. Because the desired text sting is found, the formula enters "FRUIT" into the Classification field.The formula for the next row would search for "Cucumber" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is found there, the formula enters "VEGETABLE" into the Classification field.The formula for the next row would search for "Dried Oregano" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is not found there, the formula enters "SPICES" into the Classification field.
If Excel can handle this level of automation, but I suspect there must be a way to do this.
View 2 Replies
View Related
Oct 2, 2008
I am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.
RED
GREEN
YELLOW
PURPLE
RED
ORANGE
BLACK
GREEN
RED
Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output
RED 1
GREEN 1
YELLOW
PURPLE
RED 2
ORANGE
BLACK
GREEN 2
RED 3
Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.
View 8 Replies
View Related
Apr 29, 2006
I am trying to get the following code to work. The instructions of at least one (but only one) of the following "IF" statements should always be run. It runs but does not produce correct results. I believe my logic is correct but the proper code is lacking! I have attached an easy to follow worksheet
Sub Max_Fuel()
If (26850 - E18) <= 1554 Then
Range("e19:h19") = (26850 - E18)
End If
If (26850 - E18) <= 4234 Then
Range("e19:h19") = "1554"
Range("e20:h20") = (26850 - E18 - 1554)
End If
If (26850 - E18) > 4234 Then
Range("e19:h19") = "1554"
Range("e20:h20") = "2680"
Range("e21:h21") = 26850 - E18 - 4234
End If
End Sub
View 5 Replies
View Related
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
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
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