I am relatively new to writing script in excel. I have a 34 sheet workbook that includes inputs, intermediate calculations and then reporting outputs.
I am trying to write macros to do the following
1. On opening - all sheets except a contents page (with macro buttons on it) are hidden
2. One of the macros on this page will unhide all sheets (i.e. a 'developer mode')
3. One of the macros will unhide all relevant input and reporting sheets (i.e. a 'user mode')
I originally set up macros to run on workbook opening that hid all sheets except the contents page and this was working but ran into trouble when I opened up the 'User Mode' saved my changes and exited - the macro didnt work when I went to open again (I think because some of the sheets were visible and some werent and it was trying to hide sheets that were already hidden).
To counter this I set up a macro that made all worksheets visible on workbook closing - this works except I keep getting a 'Save' prompt from excel because the location of selected cells (or the sheet I am viewing) has changed from last opening (not to mention the inputs may change).
I am thinking the best way to fix this as follows:
1. Keep the opening macro the same
2. Keep the user/developer macros the same
3. On closing - develop a macro that unhides all sheets after the user has selected whether or not they have made changes to the sheet in a pop up message box - if they choose yes then they are prompted to save as and then the macro to hide all sheets runs on the new version - if they choose no then they are not prompted to save the document (i.e. next time they open they will be opening back at the contents page with the same inputs they started with in the previous session).
I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I have a userform in "text1.xls" workbook when I click on commandButton it validates Staff name and Staff number stored in sheet1 of "test1.xls" workbook and opens test2.xls file which is in different folder. Problem is how do I code to close or unload "test1.xls" after opening test2.xls
Private Sub goButton_Click()
F$ = "D: estFiles est2.xls" ' can I use Path so I don't need to type the full directory path
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???
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!"
I want code to: Open EstimatingSheet.xls SaveAs current workbook as Range B13 &".xls" Open EstimatingSheet.xls workbook from somewhere on C: Then close the one with B13.xls
I have a userform that opens a woorkbook and fills some textbox from that woorkbook. Although that it goes very fast it is still visible (sometime just in the toolbar) and flickers the screen. Is it possible to open it "hidden"?
Private Sub UserForm_Initialize() Application.ScreenUpdating = False On Error Resume Next Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False Workbooks.Open "V:allaBeredningKontrollsystemetKontrollsystemet.xls", ReadOnly:=True Sheets("Calc").Activate Label4 = Range("K7") Label5 = Range("L7") Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False Caption = Now TextBox1.SetFocus Application.ScreenUpdating = True End Sub
I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.
The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?
Here's a sample of one of the menu button's code:
Code:
Private Sub CommandButton18_Click() Dim wb As Workbook Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx") With wb.Worksheets("Sheet1") .Unprotect Password:="pass" ActiveWorkbook.Worksheets("Sheet1").Copy End With
I have create a menu with buttons that are attached to the different projects of vba. This menu will be put in the server of my department so all my colleagues can take profit of the macros. Is a menu in an excel file that only contains the vba projects, not the sheets where the macros are supposed to run. My problem start when you want to open the file. It will always be in the same disk "P:COAP", and then there are more subdirectories (year, month)... so the total path, for example, this month is:"P:COAP2007200706". But every month, it changes, logically.
The problem is that i need to open a file with the name "COAP-200706 Concentració Empresa Emissora" in this case... the only thing that changes is month, and year... I want to close the booke i choose, to avoid errors, if the name of the workbook chosen is different to a part of this string. In this case, i'm trying to close the book if the name of the file or the book don't contains the string "Concentració Empresa Emissora", without saving changes, of course... because sometimes people have chosen badly the files and they've made a disaster. If the file chosen contains this string in the name i want to continue with all the code that is written after.
ChDrive "P:" ChDir "P:COAP2007" NouFitxer = Application. GetOpenFilename If NouFitxer = "" Then Exit Sub Workbooks.Open Filename:=NouFitxer If ActiveWorkbook.Name <> "*" & "Concentració Empresa Emissora" & ".xls" Then Msg = MsgBox("Error in the file opened. Look at the name of the file next time." _ & vbCrLf & "The program will close without finishing the process. Start again. Thanks", _ vbOKOnly + vbCritical, ("END OF PROCESS")) ActiveWorkbook.Close Savechanges:=False Exit Sub End If
What i need to do is Open a Closed workbook (which i have done in the code below)
1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)
2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work. The red is where it hangs up.
I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
I have a folder which has a set of files (Files may be anything like excel,pdf,word..etc).My rrequirement is: When i run the macro the first file to last file should open and wait for 5 sec and then close..I mean first time the first file should open and wait for 5 sec and close..Next 2 nd files shoul open and close after 5 sec..next third files should open and close after 5 sec...Like wise till last file..Is this possible to achive through macro code..
I have created several excel files as I cannot open them all at once since they are too large. So I have to open one excel file whilst closing another.
part1 part2 part3 part4 part5
I want a macro that opens the excel file part 1 and part 2. Then it should close part 1 and after closing part 1 it should open part 3. Once part 3 is open, part 2 should be closed and when part 2 is closed part 4 should open. When part 4 is open, then part 3 should be closed and part 5 should open.
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
I have a workbook that contains 4 macros. This workbook is copied by vb.net application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.
My question:
1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?
2) Is it possible to close the original workbook while executing the macro from the copied workbook?
3) Will that affect the macro in the copied workbook?
4) If possible to close the original workbook, kindly help me with the code.
When I open my workbook it also opens a separate workbook and hides second book. when I close myworkbook I want to unhide the hidden one and close it without saving.
I have the following strange problem. A simple macro 'ActiveWorkbook.Close' causes excel to crash if I use it with a button on my sheet (a form). When I run the macro via developer > macros > run the macro runs as expected. Even if a run the macro with alerts (thanks to jproffer) the problem persist.
i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.
I have a macro on a particular workbook that runs when the workbook closes.
This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.
To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).
How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)
I have a macro that runs when the workbook opens. At the end of my macro I've added a Yes/No box. If 'No' the workbook closes without saving. If 'Yes' I would like to restart the macro. I can close it ok, but I don't know how to restart the macro from line 1 when 'Yes' is selected. Here is what I have:
YesNo = MsgBox("Done! Would you like to run again?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes
'I don't know what to put here :(
Case vbNo ActiveWorkbook.Close savechanges:=False End Select
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form 2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden) 3: user then does work on 2nd workbook, 4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code: Unload Me ActiveWorkbook.save Windows("ABC.xlsm").Activate Sheets("Request Sheet").Activate Call Sheets("Request Sheet").ForceClickOnBouttonXYZ Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!! Windows("xyz.xlsm").Close ABC is the 1st workbook xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code: Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
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 ..................
Code: Sub CopyRow()' 'Copies row to new sheet, highlights it, marks column 'A' as copied. ' Dim cCell As Range Set cCell = Selection.Cells(1, 1) Selection.Copy Sheets("Sheet2").Select Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
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.
