Open 2nd Workbook From Userform Then Close 2nd Userform?

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


ADVERTISEMENT

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 View Related

Show Userform On Workbook Open

Dec 7, 2009

This is the code I received for showing a userform upon open...but it's not working.

View 10 Replies View Related

Open Workbook Window On Top Of UserForm

Mar 13, 2008

I have a userform with several buttons on, each doing seperate tasks, three of the buttons however are used to load a workbook stored elsewhere in our central data storage drive. Loading these workbooks is not the problem, the problem is when loading these they load up behind the userform and not in a new window.

View 6 Replies View Related

Close Userform Before 2nd Userform Loads

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

Open Another Workbook For Multiple Userform Event Use.

Sep 12, 2009

The use of this userform is to find a customer reference number. Im using two combobox's and a textbox.value that are populated from another workbook.

Though the way it is at the moment it opens the workbook and closes the workbook every time a new value is set to one of the combobox's.

I want to open the workbook on the useform initialize and do everthink the useform need from it. And then on the userform terminate close the workbook. Or somehink to this equlivent so this process of finding the customer referance number goes faster.

View 6 Replies View Related

Search For UserForm TextBox In Another Open Workbook

Jun 9, 2008

I have a Userform with a textbox in it. The user is asked to enter a serial number and press Enter. When the Enter key is pressed, I would like to search for that serial nember in "SourceWorkbook.xls", Sheet1, in column A (or B). If the Serial number is found it should be placed in "DestinationWorkbook.xls", Sheet1 Cell A1 etc. If no serial number is found I would like to display a message about it it. The workbooks are already opened.

View 2 Replies View Related

Open Workbook Via Another Workbook And Not Display Userform

Mar 3, 2009

I have a workbook ("Time") that when opened displays a userform. I also have another workbook ("Master") that opens the "Time" workbook to retrieve information. Is there a way to use the Master workbook to open the Time workbook WITHOUT displaying the userform.

View 2 Replies View Related

Excel 2010 :: Accessing VBA Module From UserForm On Workbook Open

Feb 13, 2013

I am using Excel 2010 and late binding to generate reports. The sheets are blank and unused. I have the UserForm being opened on Workbook_Open and the actual VBA for doing the work in a separate macro in the workbook. All the posts I have found are dealing with accessing cells from the UserForm and I haven't found any dealing with connecting a macro to the UserForm output.

My question is this: I want to return from the UserForm the flags of which reports to run to the (unopened) macro containing the code to do so. How do I open the macro and pass the variables to it?

The UserForm is creatively called "SelectionForm" and the macro is called "DailyFTP" with a Main sub as the entry. I know I can use the code under to capture the radio button and checkbox choices. I have other variables like the names of files set as global variables in the DailyFTP macro. The macro doesn't exist until SelectionForm opens it, so they must be set after the macro is in memory.

View 6 Replies View Related

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

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

Remove UserForm On Close

Dec 18, 2009

I have a function (courtesy of Walkanbach) to create a userform, very useful as it means I keep memory down as I can create the UF's as I go and tweak to suit.

I have been trying to get the userform to be removed when the user closes it. The close action in this case is the X button top right as this userform only has the userform itself and a listbox.

When I set the code for the event procedure it doesn't error, but nothing happens. The microsoft help pages seem to suggest that you can't remove a userform through vba, but I find that hard to believe. I've tried variations on the close procedure and all to no avail so far.

Dim xInt As Integer
With TempForm.CodeModule
xInt = .CountOfLines
.InsertLines xInt + 1, "Private Sub TempForm_Terminate()"
.InsertLines xInt + 2, "ThisWorkbook.VBProject.VBComponents.Remove TempForm"
.InsertLines xInt + 3, "End Sub"
End With

View 9 Replies View Related

Close A Userform When You Go To Another Worksheet?

Apr 21, 2002

Is it possible to close a userform when you go to another worksheet?

View 9 Replies View Related

Default Close On Userform

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

Close Userform Once Click A Button

Mar 16, 2012

I have a User form which had a button which redirects me to a certain sheet, e.g. If I wanted to go to stock sheet I would click cmdViewStock and it would open the stock worksheet meaning it works, but the userform still stays open. Is there a way in which once I click the cmdViewStock it opens the Stock worksheet but closes the userform automatically straight after?

Here is the current code I have for the button:

Code:
Private Sub cmdViewStock_Click()
Sheets("Stock").Select
Sheets("Stock").Range("B4").Select
End Sub

View 2 Replies View Related

Configuring Close Button (x) On Userform

Feb 6, 2007

most of the users always like to click the close (x) button on the top right of the userform ... and when that happens the application that was set hidden will remains hidden and other users in the network cannot access it ... eventhough i've placed an exit button inside the userform, users still prefer to click the x button.

View 5 Replies View Related

Hiding The UserForm Close Box Or Header

Apr 18, 2006

I am not sure if this can be done or not. I am displaying a userform non-modally (Modeless). It will remain in place while the workbook is open. I know how to stop the form from being removed if the user clicks on the close box (the X in the top-righthand corner). What I would like is to stop the X displaying at all. I heard that it might be possible in the Properties box, but cannot see it in Excel 2000.

Even better would be if I could hide the entire header of the user form, so that I could simulate the look of placing controls within an Autoshape. (I am producing a project for users of Excel 2000 onwards, and I understand that placing controls directly in a worksheet can cause problems in other versions, so I must use forms.)

View 6 Replies View Related

Userform Text Box Value Saving On Close

Jun 23, 2007

I have a userform1 with 12 text boxes with values/text and on this userform there is a cmdbutton to bring up userform2 to change the values/text of the textboxes on the userform1, it changes all the values/text on all the textboxes on userform1, however I want it to save these changes when the 1st userform closes & then re-opens?? How do I do this??

View 9 Replies View Related

VBA Code To Close Userform After Data Selection Only?

Sep 11, 2013

I have a list of 10 shops as a list box named lstitems i need the user to click one of the 10 shops and when they click the selected shop it tells the name they selected. then i require a Quit button that transfers the selected Shop to cell D3 & then closes the userform, but if they do not pick a shop it will ask them to pick one before it closes. so they must select or it will not close.

View 9 Replies View Related

UserForm - Excel Has Found An Error And Needs To Close

Sep 13, 2003

when I try to do this:

Private Sub Workbook_Open()
UserForm1.show vbnomode
End Sub

I get one of those "Excel has found an error and needs to close......"

and it crashes down

View 9 Replies View Related

Automatically Close UserForm After Time Period

Feb 15, 2010

I have the following code in a simple userform containing only a label:


Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub UserForm_Activate()
DoEvents
Sleep 4000
Unload Me
End Sub

When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds.

However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel.

If required, I can happily send you the code as an attachment.

View 9 Replies View Related

Retain UserForm Data After Close / Save

Jan 31, 2008

I am trying to accomplish keeping userform data in the userform after saving & closing the workbook. I am not referring to saving the data in a worksheet. Is this possible?

View 3 Replies View Related

Remove X Close On UserForm. Multiple Versions

Mar 2, 2008

I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.

View 6 Replies View Related

Close UserForm When Enter Ket Pressed In TextBox

Apr 6, 2008

I have a userform with a textbox in it. Once you type what you want in the textbox, you click on one of two buttons - either Accept or Cancel. It would be much neater if the userform would close when the user hits the carriage return in the textbox. Anyone know how this can be achieved? Kjartan Auto Merged Post Until 24 Hrs Passes;Doh never mind folks, I figured it out...

Private Sub Userform1Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Userform1.Exit
End Sub

View 2 Replies View Related

Close Calender Userform After Date Selected

Jul 2, 2008

I have a calendar form opened by a control button on an excel userform. Is there a way of closing the calendar form as soon as a date is selected, leaving the initial form open?

View 4 Replies View Related

Check If Workbook Open & Close It

Sep 22, 2007

Code to check if a workbook is open, and close it, if it is.

View 4 Replies View Related

Close Active Workbook And Open Another

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

Macro To Open / Close / Save Workbook?

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

Script To Run Macro On Workbook Open And Close

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

Auto Close Workbook And Open Another After Inactivity

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

Auto Close Workbook If Kept Open For More Than 5 Minutes

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







Copyrights 2005-15 www.BigResource.com, All rights reserved