I have a workbook that, when opened, sets the view to full screen. When it is closed, I want the view reset to normal.
The problem is that the prompt to close full screen appears rather than the view automatically resetting to normal.
What additional codes is needed to fully automate this?
I have:
ActiveWorkbook.Save
Application.Workbooks("PCAR Log.xls").Close (0)
Application.DisplayFullScreen = False
Recently I come across a file with only 1 sheet, in which is a table only occupied an array of A1:Q49. In Normal View I can see the row / column header of A-Q and 1-49 as usual, but nothing outside that range (only grey colour). Also, the empty cells outside A1:Q49 are all grey too. How can I do that?
View 2 Replies View RelatedI printed my graph after editing it while in normal view. It did not appear on paper like it looked on the screen. I switched to page break preview to see if I could see the problem and noticed that the printed version matched the page break version. I am using text boxes for some of my data on the graph itself, and the boxes, moreso than the bars, appear differently between views. This has never happened before, and I print this graph every month after changing the parameters and data. To my knowledge, I did not change anything out of the ordinary.
View 2 Replies View RelatedI am using Excel 2003.
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 have 1 workbook which summerizes 3 other workbooks. My question is, is there a way that if I hide a column(s) in 1 workbook (by either using Hide or Outline), the corresponding column(s) will also automatically be hidden in the Summery workbook?
Or, is there a way that I can make the Summery Workbook only display / Link to cells that is been displayed in the source workbooks? I hope to do this dynamically, so if something is hidden by Outline or Hide in one of the workbook, the affected cells in the Summary workbook will also go in hiding.
I am hoping to do this by some magical Excel Function, if not then by VBA (I'm not that good a VBA programmer), if not even that, then maybe some free Excel Tool someone knows?
I have an Excel 2007 Workbook that has a variety of different reports. I’m trying to find a way that will allow me to reset reports back to original setting after the report has been completed. Sometimes a report may take a couple days to complete, so closing and saving is a must. Any thoughts how I may achieve this.
View 9 Replies View RelatedI am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
Is there a way, either in Excel or using VBA, to reset xlLastCell without having to save the workbook?
View 4 Replies View RelatedI need to be able to have a time counter on my excel sheet that whenever I open my sheet, it automatically starts counting the number of seconds, minutes and hours spent. I also need my sheet to be able to satrt from where it stopped and also have a reset function built in.
View 3 Replies View RelatedI 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 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?
View 4 Replies View RelatedI have an excel document with about 7 worksheets in it. The users of these worksheets can save the document but they never save it with the page view starting from the top. How can i ensure that whenever this document is open the default view for all the contained worksheets is at the top (ie:cell a1).
View 2 Replies View RelatedIs there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
View 2 Replies View RelatedI have a workbook that is accessed through a network by many users. The one problem that occurs with certain users is that they leave the autofilter on when then are in the spreadsheet. It has an autofilter on each title, and the user initials are an option to filter...certain users leave the filter on when they save the sheet so the next time someone else goes in, it only displays the previous users results.
Is there a way to reset the autofilter for the user initials each time the sheet is opened?
I cannot make this work in XL2010.
I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.
I don't EVER want to save changes on either workbook.
I need to leave excel open as the macro created a 3rd workbook which the user will need.
[Code]....
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.
I have two workbooks that are used in conjunction with each other.
One is a template that creates a quote, the other is a workbook that has data which is used to populate the newly created quote. The data workbook is opened and closed by the opening and closing of the template. But beside the need for the user to see the data when they are selecting which items to add to the quote, there is no other reason for them to see it on the "View", "Switch Windows" for you 07 users, can't recall 03, or on the "Windows Status bar" at the bottom/side or where ever it's placed.
Is there anyway to hide this from the user, and or prevent the user from saving any changes on the data workbook.
Came across this code on MSDN but not sure how to incorporate it or if it's even applicable. [url]
Visual Basic (Declaration)
< BrowsableAttribute(False)> _
Public Property IsAddin As Boolean
Visual Basic (Usage)
Dim instance As Workbook
Dim value As Boolean
value = instance.IsAddin
instance.IsAddin = value
I have an excel file as attached. I have two departments Digital and Industrial. Under this I have done a drop down option for different quarter for different projects.
When I open my excel file I just want to see the Quarter column under Digital but when I double click on Digital all other columns appear.
Second this is it possible to create a master filter or sorting for Quarter above the table so that if I select Q1 in it it will displays all the project and corresponding values in both Digital and Industrial.
Attachment 47491A.xlsx
I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.
View 5 Replies View RelatedI have hide workbook in view and hide option, when i unhide the same it should ask password. Is it possible.
View 1 Replies View RelatedUsing Excel 2010 - I have a large workbook with multiple worksheets - been using it succesfully every day for a long time. All of a sudden every empty cell, and any cell not specifically formated in every worksheet has a default cell format of Time. Any new workbook created is OK.. defaults to 'General' - But, if I add a new blank worksheet to this particular workbook it defaults to 'Time' format. How did the default cell format for this workbook become set to 'Time' from 'General' and how do i fix it?
View 2 Replies View RelatedI'm having a problem creating a Reset All command button that will remove user entered unlocked cell values from an entire workbook save for one specific worksheet. So for example, I have five tabs labeled as "DTF", "Week 1", "Week 2", "Week 3" and "Week 4". I want one single command button (placed in the "DTF" worksheet) to remove all of the unlocked cell values from "Week 1" to "Week 4".
View 9 Replies View RelatedI snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.
What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.
Private Sub CommandButton4_Click()
Dim sAnswer As String
Dim i As Integer
' ****************
' ** Exit Button**
' ****************
When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..
How can i save and close one workbook in excel with vba code.
View 2 Replies View RelatedHow can I make one workbook close another without saving?
These all work in the immediate window, but not in the
Private Sub Workbook_BeforeClose
I have the below code in a macro attached to a button. Basically, if i choose yes to the message box at the beginning then the macro copies the all the cells on the sheet 'selections', opens up a new workbook, pastes into here, deletes some unused cells, rows and columns, then sends the sheet as an attachment on an e-mail.
What i also need to do is when i have sent the e-mail, the macro closes the new workbook without saving changes.
I know this is the code to close a workbook
View 5 Replies View RelatedI 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.
I somehow need to add to the section of code below a event to have the "(Public)Archive.xls" to automatically close 15 minutes without saving everytime it is opened.
Code:
Kill "H:AllMaterial Prep Archive(Public)Archive.xls"
Columns("A:H").Select
Selection.Copy
[Code].....
I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.
here is the code I have on open that works fine: -
Private Sub Workbook_Open()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub
I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!
Here it is: -
Private Sub Workbook_BeforeClose()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
ThisWorkbook.Save
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub
So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)