Open Only The User Form ... Or Close Everything
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
ADVERTISEMENT
Oct 10, 2008
how you can remove the close 'X' on the title bar of a user form please? I have used this for a password input box but if the user selects the 'X' on the title bar then it closes the password box without them having to enter a password and allows them into the workbook.
View 2 Replies
View Related
May 10, 2007
How do I define the variable in a text box so that it stays in that text box after the close of the dialog box. For example, I enter "Test" in TextBox1 then close UserForm1. I want to be able to open UserForm1 back up and still have the same user defined text in there.
View 9 Replies
View Related
Feb 1, 2008
Is there a way for me to save the values inside a userform to public variables when a user closes the form?
View 9 Replies
View Related
Oct 23, 2009
I have a userform, every time the user clicks on the start button it recodes there name, today’s date and their start time when they hit finish it records their end time.
The next time the user hits start it will record the users name, today’s date and start time on the next row (below their last entry)
The problem I have is that if the user clicks on start and then does not click on end button, but instead exits excel, then reopens the user form and hits start. The start will go on the row below, but now when they click on the end button the end time will go into the previous row for the end time, as they did not put it in if you know what I mean.
User NameDateStartEndTotal TimeTime InactiveClaire Watts
23/10/0915:04:2515:04:420:00:170:00:17Claire Watts
23/10/0915:04:4715:04:540:00:070:00:05Claire Watts
23/10/0915:04:5515:05:060:00:110:00:01Claire Watts
23/10/0915:05:0815:06:100:00:150:00:02Claire Watts
23/10/0915:05:55
#######Claire Watts
23/10/0915:06:18
15:06:18
I want to stop the user from being able to exit excel if they had clicked on start
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "You have clicked on Start and therefore mus Click the End button before you can exit excel.", vbCritical, "Cannot Close"
End Sub
This disables the X in the top right hand corner, I have aready disabled the one in the user form. However the following code does not let the user exit via File and Exit. Is there a way to put an IF statement into this that say if CmdStart has been clicked then the user can not exit, How every if it has not then they can exit.
I have read threads on this forum as well as on others and this does seam to be and issue.
OR is there a way of Hiding the X button that is in the top right hand corner. However this will still allows the user to hit start button and then exit excel, which will continue with the problem.
View 10 Replies
View Related
Sep 29, 2009
If i have a userform: frmUpdateInfo
How do i have it auto open or at least be ready for use when going to a particular sheet in a workbook (Sheet name: "Matches")
View 6 Replies
View Related
Jan 15, 2010
Am working on a spreadsheet which has two sheets, one for the visual interface and another for the data, the data is displayed on a form, the form is opened by clicking on the cells.
Currently this is working for the top row of A5 to AA5 or 1-001 to 1-024, however when I try to include A8 to AA8 or 1-025 to 1-048 it fails.
See attached spread sheet for code
The problem parts are in;
Sheet1(Layout)
Private Sub Worksheet_BeforeDoubleClick.......
Form (Ports)
Private Sub UserForm_Activate()
How to include A8:AA8 and more when needed?
View 6 Replies
View Related
Aug 2, 2008
How to display the userform when the workbook is opened?
View 9 Replies
View Related
Mar 8, 2003
I need to hide the "X" or close button on a user form so that users have to use the next or ok buttons that i have added.
View 9 Replies
View Related
Jun 3, 2006
find the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
View 7 Replies
View Related
Feb 9, 2010
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
View 2 Replies
View Related
Jan 14, 2009
I have created a registration workbook for this year's youth sports league. All of the information is entered into a User Form and separated onto it's appropriate sheet designated by the child's age. Next year, I would like to use this year's workbook to look up returning players.
Will it be possible to add a "lookup" button into my form, or create a lookup program, that once the registrar clicks on the correct player, the information is plugged into the User Form, the registrar adjusts the age and any necessary info, presses enter, and the information is copied into the appropriate category in the new workbook? I haven't worked with User Forms long enough to know if they can be filled in that way, but if this can be done, you are the people who would know.
View 3 Replies
View Related
Dec 2, 2008
I'm using a series of user forms for data entry to a workbook, some of them open next stage user form on completion (OK cmd button).
All that is working fine, but I'd like the initial form to close on showing the next one. I've tried adding Unload and Hide commands following the frmInsertEntry.Show (Next stage form), to no avail, but I'd like this user form to close or hide at the end of the sub.
Private Sub cmdContinueType_Click()
ActiveWorkbook.Sheets("Records").Activate 'Select starting cell in record sheet
Range("N3").Select
Do
If IsEmpty(ActiveCell) = False Then ' Search for next empty cell
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
If optDrillType = True Then
frmDrillEntry.Show
Else
frmInsertEntry.Show
End If
End Sub
View 9 Replies
View Related
Oct 8, 2008
I found this code and I liked to use it, however it does not close the form.
Private Sub cmdBtMain_Click()
frmMain.Show
frmFenster.Close
End Sub
View 9 Replies
View Related
Dec 13, 2006
Is there any easy way to disable the close (X) button at the top right of a form.
I've been trying...
Private Sub UserForm_Terminate()
UserForm1.Show
Unload UserForm2
End Sub
but i get a problem when re-opening the userform2??
View 2 Replies
View Related
Mar 24, 2007
I have the following code that displays a form at a user defined time and if the user does not press "Stop" then the workbook saves and closes. The user can press stop then the workbook remains open.
Here is what I have where:
Admin_Auto_Shutdown = Yes or No
Admin_Auto_Shutdown_Time = 3:34pm or user defined time (This doesn't seem to work??)
'Auto Shutdown CloseandSave
If UCase(wb.Worksheets("Admin"). Range("Admin_Auto_Shutdown").Value) = "YES" Then
Application .OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
End If
Sub AutoShutdown()
Application.OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
Auto_Shutdown_Form.Show
End Sub
Now, my question is about a timer that I can show on a form. When the form is displayed I would like to give the user 30 seconds to press stop (and keep the workbook open) or to press proceed and save and close or to not do anything and the workbook would close and save when the timer reaches zero.
Code for user form which is missing most everything...
Private Sub Halt_Click()
'If user whats to continue without closing
Auto_Shutdown_Form.Hide
End Sub
Private Sub Proceed_Click()
'If user whats to save and close
Auto_Shutdown_Form.Hide
How do I add a timer to this code where it will run this at the end of the timer?
Auto_Shutdown_Form.Hide
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
View 4 Replies
View Related
Jul 16, 2007
I have a useform that loads up with two buttons and I waat to force the user to chose one of the buttons. However, I have noticed that the user can just press the cross in the top right corner of the form to close the window and therefore not press either button.
Is there anyway I can stop the close form button working?
View 4 Replies
View Related
Jun 6, 2008
I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
a = MsgBox("Do you want to Log-Out?", _
vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True
End Sub
View 9 Replies
View Related
Jan 25, 2007
I have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
View 3 Replies
View Related
Jun 25, 2008
I have a macro that runs when the workbook opens. At the end of my macro I've added a Yes/No box. If 'No' the workbook closes without saving. If 'Yes' I would like to restart the macro. I can close it ok, but I don't know how to restart the macro from line 1 when 'Yes' is selected. Here is what I have:
YesNo = MsgBox("Done! Would you like to run again?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'I don't know what to put here :(
Case vbNo
ActiveWorkbook.Close savechanges:=False
End Select
View 3 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
Nov 13, 2008
We have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.
View 2 Replies
View Related
Oct 13, 2008
how do i go about setting a message box to pop up every 10 mins or so prompting the user to close the workbook.? i want it to be as irritating as possible! iv'e had a look at ontime but cant get my brain into gear!also don't some of these timers fire even if workbook closed?
View 4 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
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
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
