Open Userform When You Close A Spreadsheet
Mar 5, 2007
I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.
When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).
View 9 Replies
ADVERTISEMENT
Jul 4, 2012
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
View 7 Replies
View Related
Mar 20, 2008
Problem: I have textbox entries that are part of a Userform that opens using a macro on a speadsheet button. Once this form is open, I can no longer actively work in the spreadsheets.
Need: I need a way to minimize or "put on hold" the Userform so that I can freely move around in the spreadsheet. This could be in the form of a button on the Userform. Then, I need a way to bring back this userform to the point I was at before being put on hold so that I can continue to input entries into the textboxes (again, a buttton that could bring it back). There should be a way to toggle between both worlds.
View 2 Replies
View Related
Dec 15, 2012
I can't close out of my spreadsheet (xlsm). I press on the x in the upper right and nothing happens. I've been using the spreadsheet for a long time, but it just started happening yesterday.
View 2 Replies
View Related
Oct 27, 2009
I have a spreadsheet that on open needs to open a 2nd spreadsheet minimised and delete 2 named sheets in the 2nd spreadsheet.
On close of the first spreadsheet it should then copy 2 sheets with the same name from itself into the 2nd spreadsheet.
This is to start a spreadsheet that will track project risks and issues that I will post further details for as other functions are required.
View 9 Replies
View Related
Oct 22, 2008
I have a macro that opens up a file, copies info from the file then closes it and the macro continues. I don't want the user to have to click on any 'do you want to save changes' prompts.
View 5 Replies
View Related
Apr 14, 2008
Can I take
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
and do something like this?
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls") Not _
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
I have a userform that I would like with a click of a button to open the workbook and then click the same button to close the workbook after it has been viewed.
View 9 Replies
View Related
Aug 5, 2008
I have a large userform (Main) that the users enter data into. At the end of the data entry the users click OK and the data is transferred to a spreadsheet. I then ask the users if they want to add another record. If they click "Yes" all is OK. If they click "No" the userform should close and another "switchboard" type userform should open.
My problem is that try as I might I cannot get the (Main) userform to close. It only does so after the switchboard closes.
If I don't load the switchboard, main closes without issue.
I have left out the bulk of the data transferred to the "data" sheet in the code below.
Private Sub Main_OK_Button_Click()
Application.ScreenUpdating = False
Sheets("Data").Visible = True
Sheets("Data"). Range("F2").Value = Surname.Text
Dim result As VbMsgBoxResult
result = MsgBox("Do you wish to enter another record?", vbYesNo + vbInformation, "Continue...")
If result = vbNo Then
Main.URN.SetFocus
Unload Me
Else
View 3 Replies
View Related
Jan 16, 2009
When i open excel it give an error and it close. My computer up to date about office upgrades. And i try to repair my office but it doesnt solve my problem.
View 7 Replies
View Related
Jun 3, 2014
Below is the code to open a csv file and paste it in the workbook "ResultFile". When i click on a button, a csv file will open to copy the values and paste it in "Resultfile". Now, i want to close the csv or excel file to be closed once copying has done.
where to put the close file.
[Code] ....
View 2 Replies
View Related
Jul 27, 2006
I'm creating a template that will be opened by users and saved to a new name based on info provided by the user. I have macros triggered when opening and closing the workbook. The Before Closing sub does some data transfer between workseets. I'm running into a problem with the Open sub, however. I wanted to give the user a chance to abort the opening, giving a dialog that allows canceling. I wanted to just close the workbook.
However, if I include an Active.Workbook.Close line in the Open sub, it of course triggers the Before Close sub, but it leaves the On Open sub hanging. The workbook closes, but the next time I open it, the dialog in the Open sub doesn't come up. Close again, and it works ok or quit Excel between openings and it is ok. If I let the Open macro complete, bypassing the remainder of the macro if the user indcates canceling, how to I trigger closing within VBA? If the Open macro completes without an Activeworkbook.close statement things will just sit there until the user initiates closing.
Is there a way to terminate the Open sub from within the Before Closing sub if I create a flag to indicate closing started during the open sub or some other way around this problem?
View 5 Replies
View Related
Feb 25, 2008
I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).
For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb
Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?
View 3 Replies
View Related
Apr 10, 2008
When i open an excel file, i need it to print 3 copies and then close itself without saving. This is because i have a file that must have 3 copies printed everyday. I know its lazy =) but i'm sure it will be very useful and i have seen it on a file before however i cannot successfully replicate it.
View 3 Replies
View Related
Feb 1, 2010
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
View 11 Replies
View Related
Jul 10, 2012
I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.
Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit
Loop
View 3 Replies
View Related
Feb 11, 2013
I have a batch script which runs the following line to open my excel sheet:
start m:exportad.xls
when this sheet opens it automatically refreshes from a csv file.
The problem is that I want this sheet to open, refresh and close. To close Excel I have been using the taskkill command in my batch script, but I have found that using this method doesn't update the sheet properly - the sheet only updates properly if I close Excel in the correct way, but this means manual input and I want this to be automatic.
View 3 Replies
View Related
Feb 21, 2013
I opened a file, by file I mean a "file" with no extension that has commas separating it.
So I recorded a macro to open up the file and this works fine.
How do I close it? I tried
wk1 = ThisWorkbook.Name 'Main Worksheet
...
Workbooks(wk1).Activate
ActiveWindow.Close
But the problem is that "wk1" is not set equal to the name, probably because the file is not an excel file, it has no extension.
View 2 Replies
View Related
Apr 30, 2013
I know the shortcut for grouping columns in excel is Alt + D + G + G or Shift + Alt + Right Arrow. Is there a shortcut to actually open and close grouped columns (i.e., the equivalent of clicking on the + sign at the top) without using the mouse?
View 1 Replies
View Related
Aug 6, 2013
I use the following code to open an existing open workbook in a new instance of Excel...
Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...
Public Sub Re_open_workbook()
Application.DisplayAlerts = False
ThisWorkbook.Save
[Code].....
View 1 Replies
View Related
Apr 10, 2014
Is there a way to automatically open or close all grouped rows on a specific worksheet or for specific rows?
View 5 Replies
View Related
Mar 22, 2006
Assume that the workbook Book1.xls opens a user form named myUserForm.
I'm trying to show only the user form on the desktop when I open Book1.xls, with Book1.xls minimized on the Taskbar.
And , if the Cancel button on the form is clicked, then unload the form and close Book1.xls.
That simple!
In ThisWorkbook, I have the event:
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
ShowTheForm
End Sub
On the Form, I have the Cancel button:
Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
End Sub
The above procedure shows the form, with Microsoft Excel blank window in the background, and produces a small bar at the bottom left of the screen for Book1.xls
Click the Cancel button, and both the Form and Book1.xls close, but the blank Excel window remain open !
I suppose I would not be able to edit this version of Book1.xls, but that is fine for now!
View 9 Replies
View Related
Oct 22, 2008
this has to be the easiest thing ever. I want to (on opening one file) open another, and hide it (the second file), and then when closing the first file, I want to close the second file as well....
View 9 Replies
View Related
Feb 25, 2009
I need to check if a .txt file is open. If yes, need to close it.
View 9 Replies
View Related
Dec 29, 2006
I am trying to append 365 text files into a single Excel sheet. The files have unique names corresponding to the day of data that they represent (i.e. 0101.txt, 0102.txt, 0103.txt etc.) I am looking for some code that will loop through a folder, open each file, copy all data, and append it without the headers into the single Excel sheet. Does anyone have some VBA code that would do this?
View 6 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
Oct 22, 2009
When I close my worksheet excel asks me if I wish to save the file one last time, if I click 'yes' I would like some more code to be executed and then have the spreadsheet save itself and close definitively, how can this be done using vba?
View 9 Replies
View Related
May 16, 2014
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..
View 7 Replies
View Related
Jul 18, 2014
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.
View 1 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
