Quit Fullscreen, Hide Sheets, Save, Close On Userform Button

Apr 15, 2008

I have a fairly elaborate close-down routine for my workbook.

1) Quit out of fullscreen (restore initial state of excel before saving)
2) Hide all sheets but one (enforces cant do anything unless macros enabled)
3) Automatically save without a prompt
4) Close the workbook

I've researched it well and implemented it to the point where it works, but only if the user clicks the excel application close button -- the "X" in the titlebar (hence not in fullscreen).

However, if I launch the process via a userform button click, then for some reason the
DisplayFullscreen = False, Sheets hiding, and save codes have no effect (fullscreen persists, sheets are not hidden, no file is saved) in the Workbook_BeforeClose() subroutine....

I repeat: all the above works fine if process was launched by "native" excel button, but does not have any effect if launched from a userform commandbutton. (?!?!) any ideas what's going on?

I'm including my code so you can get an idea how it's structured.... by it's pretty "by the forum" as far as I can tell...

Private Sub CloseButton_Click()
If(1) Then
'Method 1 - close directly from userform command button
With ThisWorkbook
'.RunAutoMacros (xlAutoClose)
.Close
End With
Else
'Method 2 - set a timer to call a function to perform
' "ThisWorkbook.close" in a function outside of the userform
CustMenuCloseTimer = Now + TimeSerial(0, 0, 2)
Application .OnTime CustMenuCloseTimer, "CustMenu_CloseAction"
End If

End Sub

View 3 Replies


ADVERTISEMENT

Conflict Between Auto Save&close Macro And Show/hide Sheets Macro

Oct 16, 2009

I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View 10 Replies View Related

Hide/Erase The "X" Close Button On UserForm

Oct 26, 2008

I would like to modify one of my UserForms so the close button [the "X" in the upper corner] will not be visible. [It does not matter if this must involve hiding the upper the title bar - or not - as long as the little "X" will vanish].

I would like to point out that I DO NOT want to disable the "X" fronm closing the form [that I know how to accomplish] I just want to get rid of it. I have been told to look for a good example in a file called: "NoCloseButton.zip", created by Stephen Bullen, at http://www.bmsltd.ie/Excel/Default.htm but had no success in finding the file.

View 5 Replies View Related

How To Add Save And Close Button

Sep 3, 2013

I know how to add a save and close button but i do not know how to make it instantly available in every new workbook that i open. Currently i click the button and it looks for the macro in the first excel book that i created it in.

how do i make that macro become a default in every workbook?

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

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

VBA To Close Userform Upon Selecting A Toggle Button And Then Running Another Macro

Feb 17, 2014

I have this VBA set for a workbook_open event:

Public Sub Workbook_Open()
PromotionOrNewHireForm.Show
Sheet1.Select
End Sub

...which works, but I would prefer to have the form hide (or unload, exit, etc.) once the toggle button in that userform is first selected. I've tried a few things but none of them have worked. BTW, the userform has two toggle buttons, which run the following:

Private Sub ToggleButton1_Click()
Worksheets("Sheet1").Range("A4").Value = "Promotion"
End Sub
Private Sub ToggleButton2_Click()
Worksheets("Sheet1").Range("A4").Value = "New Hire"
End Sub

Also, once it closes, I'd like it to move on to the next macro depending on whether "Promotion" or "New Hire" is selected. Not sure if a workbook_change event is the best way to handle that.

View 1 Replies View Related

Disable X (quit) Button

Jun 11, 2007

I'm sure there is a way to disable the 'X' (quit) button in the top right-hand corner of the EXcel application (not the 'X' for individual workbooks), or the hook the quit event so that I can run code that shut down the application cleanly? I'm sure a Windows API call is involved but do not know enough about that side of programming to know for sure.

View 4 Replies View Related

Hide Button Or Textbox Based On Caption Across Multiple Sheets

Oct 16, 2009

I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros.
I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.

View 8 Replies View Related

Userform To Use 2 Comboboxes To Hide Sheets

Jun 6, 2013

Let's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).

If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5

I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'

View 1 Replies View Related

Code, That Disables Save, Save As And Close Commands

Nov 2, 2009

Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

View 2 Replies View Related

Disabling "x" To Close & Creating Macro Button To Close

Oct 11, 2006

I need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.

View 2 Replies View Related

VBA - Button Command To Auto-Hide Rows With Value HIDE

Jun 9, 2013

Sub Button294_Click()

If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub

View 4 Replies View Related

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

VBA - Use Command Button To Obtain Save As Screen And Save As PDF

Jun 22, 2014

I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.

When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.

View 3 Replies View Related

Command Button - Create A Save Button In Sheet 1?

Mar 9, 2013

i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format

View 1 Replies View Related

Save And Close In VBA

Nov 22, 2006

I want adding is a bit of code to save the document. When it saves i want the contents of cells c5 and c7 as the name of the saved document.

So it would be saved as c5 - c7

Sub mike()
'
'
Rows("1:1").Select
Range("B1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Cells.Select
Range("B1").Activate
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Windows("HSBC logo.xls").Activate
ActiveSheet.Shapes("Picture 1").Select
Selection.Copy
Windows("Template1.xls").Activate.........

View 9 Replies View Related

Save And Close Workbook Using VBA

Dec 11, 2013

How can i save and close one workbook in excel with vba code.

View 2 Replies View Related

Close And Save Application

Dec 5, 2008

When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View 2 Replies View Related

Save Workbook & Close

Jul 8, 2006

One of my computers pulls info from my cash register once everyday (sales info). Then I have a excel monthly sales sheet that pulls the info from the register info and puts it in the right places. So the computer pulls the register info at 9:20pm everyday, then I have my task manager open excel at 9:25 everyday and it has a workbook open macro to automatically pull the info into the excel spreadsheet. Here's my problem-- I need to close it.

I'm sure I could use a workbook close macro, but when it tries to close it won't it come up with a save, don't save, cancel prompt? Nobody will be there to hit SAVE so it won't close? Is there just a macro that could save it then close it?

View 9 Replies View Related

No To Save On Sheet Close

Jul 24, 2006

I have a macro that has to open another worksheet and get data and close the sheet. The problem is when the sheet closes after I have gotten the data it asks if I want to save it. I don't want the user of my macro to see this option because it slows things down. To avoid this in the past I had the worksheet save itself so that it wouldn't ask me to save. Now the worksheet is large and it takes a while to save and it is getting slow. Is there any way not to save it but close it without having excel asking if you want to save it?

View 2 Replies View Related

Save As In Before Close Event

Jan 16, 2007

I need to use the BeforeClose event to save the workbook with the same name when a user closes it. (82 users and they all seem to want to give it their own name.) I have the following code in the BeforeClose event but would like to eliminate the prompt "this file already exists, do you want to replace it" I have tried using the Application.DisplayAlerts = False but this seems to stop the workbook from saving.
I have a public demention called wbName and is set to the workbook name in an outo open macro

Public wbName
Public Sub auto_open()
wbName = ActiveWorkbook.Name

UserForm2.Show
MsgBox "PLEASE do Not insert rows/columns or enter calculations" _
& Chr(10) & " " _
& Chr(10) & "Enter Only Account Name, Date, and Corresponding_ Calls/Details", vbCritical, "Caution"
End Sub

View 6 Replies View Related

Custom Save On Close Box

Jun 21, 2007

Is it possible to stop excel bringing up the save changes box? or replace it with my own custom box? ive tried this code, but it seems to run the userform twice#~?

Application.DisplayAlerts = False
Before_Save.Hide
ThisWorkbook.Saved = False
ThisWorkbook.Close

i triggered it to load on workbook close, but like i say, it seems to run it twice? maybe i need to tell it to only run once or something?

View 2 Replies View Related

Save As Shared On Close

Aug 14, 2007

When a supervisor goes into one of my sheets and adds some things in, she quite often forgot to share it again. To try and combat this I added the following code:-

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim myworkb As String
myworkb:
myworkb = ThisWorkbook. Name
If myworkb = "SLE.MasterSheet.SLE1.xls" Then
Call NoProtection
ActiveWorkbook. SaveAs Filename:="SLE.Mastersheet.SLE1.xls", accessmode:=xlShared
End If
Call Limpa
Run "DeleteMenu"
End Sub

The code appears to do everything it should, and when I first ran it appeared to be ok. However, now it appears to save it as shared yet when you go back into the file it opens up as exclusive.... Is there a reason why the code wont "share" ?

View 2 Replies View Related

Open, Hide, Unhide, Close

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

How To Disable / Dim Close (X) Button

Oct 4, 2010

Is there anyway to easily disable/dim the Excel Close (X) Button as I want to force my users to close an Excel workbook via a button?

View 9 Replies View Related

Close Xl Using Command Button

May 20, 2006

i have a command button on a userform which closes the work book, i also want it to close xl. i have tried the following:

Private Sub CommandButton2_Click()
ThisWorkbook.Close
xl.close
End Sub

View 8 Replies View Related

Close And Save File After Timeout?

Jun 7, 2013

i would like my sheet to save and close if left inactive after 10 mins, code would be nice

View 2 Replies View Related

Automatically Close File And Save Changes With A New Name

Jun 13, 2009

I am trying to fix is that I have two computers networked together both with multiple users all with access to the report form that I am using which is in the shared file. If I have been working on it (I am still trying to do improvements when I have time), and I have to leave for a fire or forget to close the report, no one can access it other than with read-only.

My idea was to run some type of code that either when the screen saver comes on or after so much time with no activity, it would close and save the file with a new file name(just in case I don't want the changes that I have made in the code or something).

View 3 Replies View Related







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