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


ADVERTISEMENT

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

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

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

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

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

Disable Only Close Button In Excel Workbook?

May 16, 2006

Sample code on disabling the close button in Excel workbook?

I tried the Tool -> Protect -> Check Windows method. But, it will disable the maximize and minimize buttons as well. Moreover, it will minimize the workbook.

View 2 Replies View Related

400 Error When Excel Close Button Is Pressed

Jan 10, 2014

Why I receive a 400 error when the 'excel close' button is pressed in the attached sample.

View 2 Replies View Related

Attach An Event To The DataForm Close Button

May 30, 2009

I want a list to be sorted when a user closes the Excel DataForm. I have the code to execute the sort but how do I attach it to the DataForm Close button?

View 9 Replies View Related

Force Users To Close Via Macro Button

Sep 12, 2009

I have a spreadsheet that has a button named Exit Requisition Program on it to close the application with. When the button is used everything is wonderful. All of the data on the sheet is saved and the application closes. I have a problem with some users clicking the red X without saving changes and that creates a big problem.

What I need to do is any one of several things.
1. Remove the red X completely.
2. Deactivate the red X.
3. Have a message box pop up instructing the user to use the Exit Button when the red X is used. Any one of the three methods listed above would work for me.

View 2 Replies View Related

Stop Close Form Button Working On Userforms

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

Created Userform And Command Button But Data Entry Not Allowed In Userform

Jul 16, 2012

I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes

This is the code

Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")

[Code] ......

View 1 Replies View Related

Excel 2010 :: Disable Default Close Button In Workbook

Jun 3, 2014

How to disable the default close button in a workbook not to affect in my command button close.

I tried already cancel = true but my command button close is also been canceled.

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

Excel - Auto Sorting Data After Clicking Close Button On DataForm

Mar 25, 2014

I have a problem on auto-sorting my data after clicking the "x" or "Close" button on DataForm. Im creating a command button that trigger a macro to open an autofill DataForm. I want a code that everytime i ADD or EDIT using the DataForm, it will automatically sort the Column A after clicking the "x" or "Close" button. Below is the code of my macro:

Code:
Sub OpenForm()
Sheets("DATA").Select
ActiveSheet.ShowDataForm
End Sub

View 1 Replies View Related

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

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







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