Can I Change A Setting Somewhere To Make Close Its Default Blank Workbook When I Open An Existing One
Feb 9, 2007
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?
View 9 Replies
ADVERTISEMENT
Mar 7, 2014
I am looking for a vba where by the default colour of the font for new text typed in any cell of a workbook change to green or red and the default black automatic becomes secondary. The existing text in the workbook in its existing font colour must not change i.e stays in black or red.
View 1 Replies
View Related
Jun 30, 2008
I have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
View 9 Replies
View Related
Jun 3, 2014
How to disable the default close button in a workbook not to affect in my command button close.
I tried already cancel = true but my command button close is also been canceled.
View 3 Replies
View Related
Jul 1, 2013
i changed the shape of "Comment" box with edit shape. However when i right click the updated shape and click on Set Autoshape Default it does nothing. When I insert a new comment it is back to old shape.
View 2 Replies
View Related
Apr 9, 2007
How can I make one workbook close another without saving?
These all work in the immediate window, but not in the
Private Sub Workbook_BeforeClose
View 9 Replies
View Related
Mar 26, 2012
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
View 4 Replies
View Related
Dec 7, 2012
Here's my macro:
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.
View 2 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
Jan 23, 2014
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).
View 4 Replies
View Related
Aug 13, 2009
I tried to open an existing Excel workbook, it tells me that it is already open. There is nothing on the screen. I closed Excel (using 2003 version) and restarted computer also. I tried to open the file again - nothing shows up on the screen. When I attempt to open the file again I get a message that says "(file name).xls is already open.
Reopening will cause any changes you made to be discarded. Do you want to reopen (file name).xls?". Even if I reopen the file, it still does not show up on the screen.
View 4 Replies
View Related
Sep 22, 2007
Code to check if a workbook is open, and close it, if it is.
View 4 Replies
View Related
Jan 30, 2008
Two workbooks named"test1.xls"test2.xls
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
Workbooks.Open Filename:=F$
End Sub
View 5 Replies
View Related
Mar 14, 2014
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).
View 7 Replies
View Related
Nov 13, 2011
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).
View 1 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
Jul 31, 2007
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
View 9 Replies
View Related
Jan 10, 2008
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
View 3 Replies
View Related
Mar 12, 2014
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
[Code].....
View 7 Replies
View Related
Jul 26, 2007
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
View 2 Replies
View Related
Jun 13, 2008
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.
openpath = ActiveWorkbook.Path & ""
Set wb = Workbooks.Open(openpath & "Template.xls")
With wb.Sheets("Contractor Info")
. Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select
ActiveCell.Value = Msg1
ActiveCell.Offset(0, 4).Value = Msg2
ActiveCell.Offset(0, 5).Value = Msg3
ActiveCell.Offset(0, 3).Value = Msg5
ActiveCell.Offset(0, 2).Value = Msg4
ActiveCell.Offset(0, 1).Value = Msg6
ActiveCell.Offset(0, 6).Value = Msg7
Application.Calculation = xlCalculationManual..................
View 2 Replies
View Related
Mar 8, 2013
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.
View 8 Replies
View Related
Feb 11, 2013
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.
View 13 Replies
View Related
Apr 27, 2012
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
View 1 Replies
View Related
Jul 5, 2009
The following code opens Word correctly, but has too many tool bars open. I only want the tool bars to be opened, just like when you click on the Word Icon at the bottom of the screen. I do not want the following 3 tool bars to open . . . Drawing, Reviewing & Microsoft Office Live Add-In.
View 2 Replies
View Related
Feb 14, 2007
I want to automatically set default values in a row dependant on the value chosen in a list. Some of the default values need to be lists where the user can chose to update to a non-default value.
I have tried using a double drop down list. However if the default list value is changed then the dependant list value is changed, the default list is not automatically updated.
View 4 Replies
View Related
Nov 20, 2006
I currently have a macro that will work if there is a workbook open. However I would like to change it to, once the macro is sected from toolbar, it opens a blank workbook and then does what has been set up. What line(s) of code would I use to do this at the start?
View 5 Replies
View Related
Feb 13, 2014
When I open my workbook, I'd like to go at the first empty row of the worksheet.
View 14 Replies
View Related
Apr 23, 2006
is it possible to remove the default close (X) on a userform? Or maybe some code with a message to insist the user choose a option first
View 2 Replies
View Related
