I am running a script that populates about 70 or so workbooks based on information from the database. Naturally this script is quite slow but what seems to take the most time is opening and closing workbooks. Is there a way to "hide" the workbook opening/closing operation on the screen? I have tried using the
Application. ScreenUpdating = False
this sped the code up but you still see the workbooks opening on the screen.
I do not understand why my code returns a Run Time Error"9" Subscript out of range. With the following code, I opened a workbook and then wish to close that same workbook without saving. Eventually I will be pulling information from the workbook but for now I just want it to open and then close right away.
Private Sub cb_NewDate_Change()
ListIndex = cb_NewDate.ListIndex
If ListIndex = 0 Then
Application. ScreenUpdating = False
Workbooks.Open Filename:="I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls"
Application.Workbooks("I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls").Close False
End Sub
I have a problem, where every time I close or open any workbook, Excel prompts me to "Enable or Disable Macros" in one particular workbook, wether I have tried to open it or not. In the said workbook, I have a macro that automatically updates a pivot table upon selecting the worksheet where the pivot table resides.
The code for it is as follows:
Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub
Private Sub Worksheet_Activate()
Call UpdateIt
End Sub
There is something in the code that calls UpdateIt any time a workbook is opened, closed or selected.
I have a great bit of code for a spreadsheet I'm working on by a board regular called Norie;
The code is;
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveSheet.Name "ThankYou" Then
Worksheets("ThankYou").Visible = xlSheetVisible
Application.Goto Worksheets("ThankYou").Range("A1"), True
Cancel = True
UserForm1.Show
[code]....
When I open the workbook it shows for about 1 second another sheet in the workbook rather than just instantly showing the "START" tab.
Is there any way to get over this to make this code 100% perfect?
I just really would love upon opening this workbook for the sheet that 1st comes into view to be the START SCREEN TAB, without any flash preview of another sheet.
I have a workbook that has worksheets that serve as data input pages for the user.
I have control boxes created by the forms toolbar.
I created a splash screen that is to be launched when the file is opened.
Instead, often the last sheet that the user was on when they last saved the file (usually but not always the worksheet called "Main Menu") is seen first.
I understand that not all the sheets can be hidden - that one must be unhidden.
So I created a sheet called "Background" that is a solid color with no gridelines.
Objective:
When the file is opened, I want the "Background" sheet to be seen first and then the splash screen to be initiated.
Now - when the user opens the file, the Main Menu worksheet is seen for a few seconds (or whatever the last sheet was on before it was last saved), then it disappears and the solid background screen is shown with the splash screen. When the splash screen ends, the "Background" sheet disappears.
Here is the code that I have in the workbook
Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook
If wks.Name "Background" Then
wks.Visible = False
End If
Next wks
End Sub
What has to be changed so that the first sheet that is seen upon opening the file is the "Background"; then the splash screen happens; then it returns to the Main Menu sheet?
I have a userform on a worksheet. Now, I open anothe workbook, the userform still stays on top of all the workbooks. However, if I open a new workbook(new instance of excel), then it does not show. I need the userform to be a part of the excel which I open. not for all workbooks.
Few comments before I complete: This was working as intended. All of a sudden, I messed up with the code and it burnt out. Secondly, the ShowModel of userform is set to False as I need to hide the workbook when userform opens. Hence, I cannot set that to true.
Hi everybody, I'm doing my first excel application program and everything is going good,
but I have one problem.
I want to click on a command button in one workbook that is supposed to lead me to a specific sheet in another workbook and to close the first workbook.
I used the following ....
I know that the shortcut to create a group is Shift + Alt + Right arrow but is there a shorcut to open and close the group?
View 1 Replies View RelatedI have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies View RelatedI was wondering if there is any code using an on-click method. When clicking a button i need excel to exit the file/sheet without saving (no prompts) and then re-open the exact same file/sheet once it has closed.
In a way this is like a refresh button.
When I attempt to open a file I get a blank screen. If I try to close the window it asks if I want to save the file so it is must be open but not visible. Have I inadvertently clicked on an icon with which I am not familiar?
View 1 Replies View RelatedI have code in VBA that takes a source Excel file and parses it to multiple re-formatted worksheets. Each formatted worksheet is different depending on a variable in the source. There are n of each type of data (1:n Water Sources, 1:n Water Treatment, 1:n Water Storage). Each item needs a copy of a blank re-formatted worksheet appended to the end of the existing data-type worksheet. For example: Water Source #2 is appended to the bottom of Water Source #1 on the same worksheet.
Currently, I am opening the blank re-formatted workbook each time an additional variable of same type is found in the source file. Meaning, if there are 8 Water Sources, I am opening and closing the blank re-formatted workbook 7 times to copy a blank Water Source to the bottom of the Water Source worksheet and then populate the data. AND if there are 3 Water Treatment, I would open and close the blank formatted workbook 2 more times to copy a blank Water Treatment to the bottom of the Water Treatment worksheet. The same goes for Water Treatment.
Obviously, a huge waste of resources and performance. I would rather open the blank re-formatted workbook once (first time an additional worksheet is needed), use the worksheets as needed, and close it at the end.
Attached is my current, working code of the above.
How should I re-write it to check if the blank workbook is already open use it, if not open it?
Code:
Sub Add_Facility(ByVal Worksheet_Name, Copy_Range, Max_Limit)
Dim Last_Row As Integer
Dim Source_Range As Range
Dim Dest_Range As Range
Dim wkb As Workbook
Set wkb = ActiveWorkbook
[Code] ........
I have made an excel spreadsheet. The area with data exactly fills my screen.
When I send it to other colleagues, it perfectly fills their screen too. But for some colleagues the data area is either too big for their screen or takes up a small proportion of their screen based on their screen resolution. I would like the area with data to perfectly fill the screen of all people who will access the file.
I am thinking that I would do an automatic event so that I can change the settings upon opening the file. I have Excel VBA programming for Dummies which explains about automatic events but I have no idea what sort of sub procedure to use.
Excel will hang up as if it's gone into a continuous loop (Although no looping macros are active) when you open or close a user form. The task bar in Excel will flash Ready Calculate. Visual basic reports no code is running but excel either crashes or becomes very unresponsive. I have to exit and reopen excel in order to fix the problem . It also doesn't happen at any one specific point it can vary. I have various VLOOKUPs around the workbook and userforms display the correct information. I use the INDEX formula in the control source of textboxes. I also use the offset formula to make sure DTPICKER displays correctly.
I am using 'Option Explicit' at the start of all my code. I have tried a 3rd party code cleaner. No visual Basic references are MISSING
[URL]
I wrote the following code to hide & password protect an excel sheet :
[Code] .....
How could I alter it so that it hides and protects this sheet automatically before closing the workbook ?
I've got this function where you can select from a list of names, and when you press go it unhides the sheet and goes to it, problem with that was after closing the sheet it was no longer hidden. So now, I have
[Code]......
So when you press go, it unhides the sheet, goes to it, and hides it again, but then the sheet is hidden it pushes the user back to the page they was on, so it would appear you can't have a hidden sheet open.
Is there a way for excel to determine when the sheet has been closed, to be able to hide it when the user navigates away?
So I have two dates:
Opening Date: 29/07/2014 13:27
Closing Date: 29/07/2014 14:42
These are formatted in DD/MM/YYYY and HH/MM
I need to subtract the dates and find the time taken from the opening and closing time.
I have two forms...clicking a specific button on the first form should display the second form which works fine.
VB:
Private Sub Image_Employees_Click()
Form_Main.Hide
Form_Employees.Show
[Code].....
When I do this twice, however, the second form seems to freeze/hang and I have to close Excel and then re-open it. I feel like I'm overlooking something obvious...
I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
[url]
So the command is this:
Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
View 2 Replies View RelatedThe other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Here-> http://www.mrexcel.com/forum/showthread.php?t=312938
Now Id like to be able to hide the columns only if row B of the colum contains the text "hide me". Please not the cell may contain other text, such as "Q1, hide me"
Anyone know if its possible?
eg I want to hide Cols B and D
1ABCD2Shop items3dateQ1, hide meItemSupplier, hide me401/01/2006q1 2006JuicePepsi505/01/2008q1 2008crispswalkers
I have a perfectly functioning piece of code. There is still one snag, however. In the process of running the macro, three new workbooks are opened. I would like to close all three of those workbooks at the end. I am using the following procedure for each of the sheets:
To open the worksheet
Code:
Dim wb As WorkbookMsgBox "Please select the Stakeholder File"SHFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Please select the Stakeholder Workbook")If SHFile "False" Then Set wb = Workbooks.Open(Filename:=SHFile)
To close the worksheet at the end:
Code:
If Not wb is Nothing Then wb.Close SaveChanges:=False
The only problem is that I am getting bugs when closing multiple workbooks (3). I have assigned the names wb, vb and ab to the three workbooks.
I have some VBA code which opens another workbook, copies data from the first sheet in there and pastes into the current sheet before closing the workbook it got the data from. Is there any way of suppressing any messages associated with closing the other workbook? i.e. do you want to save - or possibly automatically generating a no message to the prompts?
View 3 Replies View RelatedI'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
I have 4 employees who at the end of their shifts have to email me an excel workbook. The issue is that some of them are forgetful and after placing numerous post-it notes on their monitors one particular employees just forgets.
What i want to do if possible is that when the workbook gets closed, a pop-up window shows up on their screen reminding them to send the workbook. Even better would be if their was a button that they could click that would email the workbook as an attachment in the pop-up box.
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
Is it possible to pop up a message when closing the workbook?
if so, could the message only popup if a certain cell is negative?
For example, This is what i would like to happen.
When workbook close is activated:
If cell A1=(-20), then pop-up "Please check your work and correct".
else, close workbook
How do I SAVE and CLOSE Workbook and it's instance of Excel.
I have other instances of excel open that I do not want closed.
I thought that I had this before, but I can't find it if I did. I do keep a list of all the requests where I ask for help and it's not in this list.
I wrote this macro where, from a userform, I click a button and it will fillout another form with certain data from the original WB to a different WB.
All is working fine and exactly the way I want it except, it won't close and save the other sheet. It will go back to the userform, as I want it to, but it will leave the other file in the background open.
This is the code:
Code:
Private Sub CommandButton6_Click()
'Allocation file fill out
Dim alloc As String
Dim allwb As Workbook
Dim ABA As Workbook
Dim Prompt As String
'Dim fd As FileDialog
Set ABA = ThisWorkbook
[code]....
Well I seem to be having an issue in a form i created. What happens is that when the user clicks a button it saves a worksheet of workbook as its own file, problem is that it will actually open the new worksheet as a workbook when I really just want it to save without opening a new instance. Here is the code
Dim IntWirefilename As String
Worksheets("Intwires").Select
Worksheets("Intwires").Copy
IntWirefilename = "International Wires Database"
Worksheets("Intwires").SaveAs FileName:="File Path" & IntWirefilename & (".xls")
Workbooks("File PathInternational Wires Database.xls").Close
I think the problem lies in the last line or it could be from the select and copy part.
I do not want it to open a new file after it saves it. Also can anyone help create a part that will bypass the overwrite existing file thing so that this file will just continuously save as the same file name rather than prompting the user constantly to Overwrite the file.