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??
I have a macro that opens up a file, copies info from the file then closes it and the macro continues. I don't want the user to have to click on any 'do you want to save changes' prompts.
View 5 Replies View RelatedI read two pages on "Workbook_BeforeClose" trying to find a relative answer. I want to able to close a workbook with out saving and not rely on the user pressing the correct button, can this be done?
View 3 Replies View RelatedUsing excel 2003, I have a ss that uses filters and sorting. I can get to the data I want but must reopen the workbook if I want to start over and extract different information. I tried installing this VBA:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
MsgBox "I'm Back!"
End Sub
This is my first time at inserting VBA. Usually I just use keystroke macros. I have set my Excel VB Book options to: Option Explicit double clicked the sheet I was using and under general inserted the code, closed Ctrl+Q, saved workbook. When I reopened and tried to use it all I get is “I'm Back!” nothing else happens. One time it actually ran and reopened but it appeared that the workbook was saved before closing because it was still sorted. What I need is it to completely unsorted as it is when I first open it so I can start over.
i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.
View 6 Replies View RelatedI have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.
Is there a command line I can use, that will not prompt the user to save?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
I am new to EXCEL and wanted to know if you can have excel startup running a macro and also if you can have a macro close a worksheet without saving and without confirmation. I created the basic macro of what I want to do. I just want it to be able to run unattended. Is this possible with EXCEL?
View 9 Replies View RelatedHow can I put a VBA script to wait for 30 seconds, then close the Excel file (w/o saving it) which its name is stored in CSV_file_name variable, then re-open it?
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
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
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
Is it possible to close a userform when you go to another worksheet?
View 9 Replies View Relatedis 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 RelatedI'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).
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
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 RelatedI 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.)
I have a userform with several textboxes and listview. I have a ticket number with multiple trasaction, when saving the first transaction to temp table(sheet). It was terminated (unload) which it should be back to textbox2 (ticketnumber for additional transaction.
Herewith is my VBA code.
[Code] .....
Is it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 5 Replies View RelatedIs it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 4 Replies View RelatedI have a problem with value's from a userform saving in the sheets as date's. In the userform i fill out e.g. 14-4-12
With the code beneath i save them in mij sheet.
Code:
Sheets("Containerlabels Afdrukken").Range("D6") = TextBox2.Value
When i save this in my sheet, it is saved as text. While i want to calculate with this value as a date, i would like to know how i can save this value as a date.
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 Relatedwhen 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
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.
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 RelatedI'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 RelatedI 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
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 RelatedI 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.
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