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
ADVERTISEMENT
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
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 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
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
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
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
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
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
Apr 30, 2009
Cells in Column M have a data validation drop-down list. If the user selects 'Closed', I want this code to run:
View 12 Replies
View Related
Feb 12, 2007
I have this code in my workbook, however it's not executing when the workbook is closed
NB - It deletes all the cells, as the workbook_open event is multiple inputs via input box
Private Sub Workbook_BeforeClose()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Cells.Select
Selection.Delete
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Feb 14, 2013
I currently have a set of ListBox controls on a worksheet tab. They are all configured as multi-select and i have the values populated via the ListFillRange properties. Each listbox has an '{All}' option; when the user clicks on this value I want to de-select all other previously selected values. The code for this is straightforward enough, but I cannot get it to fire using the _Click event. Why this is not working?
Additionally, I attempted to use the _Change event but quickly got caught in recursive loops which obviously is not going to cut it.
View 9 Replies
View Related
Mar 20, 2008
I have a manually calc'd workbook with the following code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("VAL1CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("VAL2CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("CHOICE")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("$L$36")) Is Nothing Then Me.Calculate
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
End Sub
Everything works as it should other then the part that is
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
When the user selects VAL1CELL This is cell B2 and is a drop down, I want VAL2CELL which is C2 and also a drop down to show what is in Y41 (i.e the first name that appears in the drop down...not a thing happens ? is there a flaw to my code ?
View 9 Replies
View Related
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
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
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
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
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
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
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
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
Jan 14, 2014
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
View 9 Replies
View Related
Mar 24, 2009
I've been developing a form in excel (not userforms, just the usual conditional formatting, validation and macros to show/hide rows etc). It all works fine now except one thing was noticed when a colleague was testing it. If she opens the form from the email, and she already has excel open, then the form will not work when she makes a selection from the dropdown list, which unhides the rest of the form. If she closes down all of excel and opens the form from fresh, it works fine.
View 9 Replies
View Related
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
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
Sep 27, 2006
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
View 3 Replies
View Related
Aug 3, 2009
I have a 'Summary' workbook which collates values from a series of 'sub-workbooks', (and can also update values in those sub workbooks).
the subworkbooks are setup so that when they are saved they also copy certain values out to another 'backup' file.
So there are 2 possible routes that the files will be used in:
1) SubWorkbook opened directly
- User opens SubWorkbook and makes some changes.
- User closes the SubWorkbook saving changes, or clicks the save button. The BeforeSave event opens the "restore" workbook, copies over the key values from the subworkbook, then saves & closes the "restore" workbook.
2) SubWorkbook opened remotely
- User / Admin opens the "Summary" workbook and changes an option.
- The summary workbook opens a subworkbook and makes changes, then saves it. The subworkbook should (as before), then open the restore workbook and save the key values etc.
The problem is in the second scenario the 'restore' workbook doesn't get opened.
I have created a set of 3 workbooks to illustrate the problem here.
Book1.xls = Summary workbook
Book2.xls = Subworkbook
Book3.xls = Restore workbook.
If you open book2 and then save it the 'BeforeSave' event will cause it to open book3 and write the output of 'Now()' to the next free row of book3-columnA, before then closing and saving book3,......
View 3 Replies
View Related
May 1, 2013
I have been running a macro on a continuous loop to collect data for months. Yesterday I added something small to the macro and now it does not select cells. I tried closing excel, opening a new page and have a simple macro
Sub Please_Work ()
Range('A4').Select
End Sub
If I have VBA open and step through it (F8), it does NOT work. If I run the sub (play button), it does NOT work. If I close VBA, and run the macro it DOES work.
View 9 Replies
View Related
Jun 26, 2014
I have a code in file A that opens several files (B,C,D&E), copies some data from them, then closes the files. That part of the code works fine, but each of the files that are opened (B,C,D,&E) have a Workbook Open event that causes the file to save automatically every 30 seconds. (I know this is not recommended, but this is what the user wants.) The files also have a Workbook Before Close event that is supposed to stop the timer so the file will close without reopening. These each run fine on their own.But if I run code A, the workbook Before Close event in file B (C,D, & E) does not seem to run and the files reopen after 30 seconds to save. When I step through the code it works fine and goes through the Before Close event in each file and the files remain closed.
View 3 Replies
View Related
Jun 16, 2007
Is it possible have code in an addin that fires when, X happens in any workbook?
For example,
Any time a user tries to print a worksheet a message box pops up asking, "are you sure you want to print that"
View 9 Replies
View Related
