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


ADVERTISEMENT

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

Hiding The "X" Or Close Button On A User Form

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

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

Userform Not Hiding Immediately?

Oct 9, 2008

I have a small userform with a ComboBox (ComboBox1) which, on a change event, kicks off another process. It's working fine, but the problem is that the userform hides immediately, and the open combobox remains visible while the second process starts running. It's only like that for about 5-7 seconds, but it doesn't look overly professional.

I'm using MS Excel 2007 on WinXP.

I tried putting in a sleep command or two, but that doesn't quite do the trick (the sleep is for 1 second).

View 2 Replies View Related

UserForm Still Shows After Hiding

Nov 30, 2006

I have 2 forms (A and B). Form B is initialized with values from a global array created in form A.
When closing the B form it's still visible.
I want it to dissapear.

The B form is opened from the A form


Private Sub cmd_ok_Click()
frm_B.Show
End Sub




When closing the B form it's still visible (modal)

'Within form B
Private Sub cmd_cancel_Click()
Unload Me
End Sub

View 9 Replies View Related

Hiding Userform Whilst Mouse Is Out?

Jan 27, 2012


I'm trying to use a non-modal userform as an application option board. Using API, the initial (Activate event) form transparency is set to 10%, and the Userform_MouseMove event turns it to 100%. The problem is that I need the transparency back to 10% (hiding it would be enough) whilst mouse remains outside the userform, and I've no idea of how I can get it.

View 5 Replies View Related

Hiding Workbook While Showing UserForm

Jun 12, 2013

1. I'm looking for a better way to get a UserForm (frmParts) to show right from the start as soon as I open the workbook while hiding the application. I use the following when I initialize the application:

Code:
Application.Visible = False
frmParts.Show

2. Later on, I add some parts to my inventory, but if I don't make the application visible, it doesn't save the data. So, I have to make it visible and then hide it again. Is there a right way to accomplish the save without having to unhide/hide the application? This is the applicable code I used (ComboBoxes & TextBoxes involved):

Code:
'copy the data to the database
Application.Visible = True
Application.ScreenUpdating = False

With ws 'ws=Inventory

[Code] ..........

View 1 Replies View Related

Set Focus To Application After Hiding UserForm

Sep 21, 2007

I created UserForm with one TextBox and "Close" Button. And it's works like a real-time search with my criteria. He is using AutoFilter to show what he found. And when I'm pressing Close Button the Focus isn't on Application, but on Hidden window (know because when I'm clicking alt+space form's menu shows).

To show window I'm using (running from hot-key):

DoCmd.OpenForm "UserForm1"

to hide

Private Sub CloseButton_Click()
UserForm1.Hide
End Sub

View 9 Replies View Related

How To Show Userform Hiding Specific Workbook

May 19, 2014

I need a code (and where to put it) to open my application excel with only the userform so, with the workbook hidden.

Application.Visible=False doesn't work because it close all excels and moreover I can see the workbook for 1 or 2 seconds before hiding.

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

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

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

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

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

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

Link A Header Or A Portion Of A Header To A Specific Cell

Jun 23, 2008

Is there a way to link a header or a portion of a header to a specific cell?

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

Compile Error On Close Of Excel 2003 But Not On Close Of Workbook

Mar 22, 2012

I am using Excel 2003.

It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.

These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.

I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.

These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.

Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.

View 9 Replies View Related

Return Column Header If Row Header And Value Is Known

Nov 18, 2011

I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.

in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.

In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.

View 4 Replies View Related







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