Retain UserForm Data After Close / Save
Jan 31, 2008I 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
ADVERTISEMENT
Retain Data On Userform
Jun 30, 2006i am using the following code to put data into a worksheet which it does perfectly. once the data is put into the sheet it is saved. what i want to do next is to retain the data in the userform and possibly change one or two entries and put data back into the sheet to save again
Option Explicit
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range
Private Sub cmbAmend_Click()
Application. ScreenUpdating = False
Set c = Range("b4").End(xlUp).Offset(3, 0) ' c selected by Find
c.Value = Me.DTPicker1.Value ' write amendments to database
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value
c.Offset(3, 0).Value = Me.TextBox3.Value
c.Offset(12, 0).Value = Me.ComboBox1.Value
Set d = Range("b16").End(xlUp).Offset(1, 0)
Quit Fullscreen, Hide Sheets, Save, Close On Userform Button
Apr 15, 2008I 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
Export Data To Notepad Save And Close
Mar 18, 2014I have a colmun of data that needs to be exported to notepad. I managed to make the routine works with the following codes:
[Code]....
However, there's some improvement that I need:
1) I want to refer my file name from here
[Code] .........
How to embed it?
One thing to note, when i ran the code, the txt file was saved as HS instead of KL AUTHS. I tried with different names and apparently it will capture maximum two characters.
2) Based on the code, it saves to my default saving location. I would like to save the txt file in the same directory as my excel file.
VBA Code To Close Userform After Data Selection Only?
Sep 11, 2013I 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 RelatedCode, That Disables Save, Save As And Close Commands
Nov 2, 2009Each 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.
Userform Will Not Retain Changes
Sep 20, 2006I am building a large Userform and I am going back to edit some of the controls (renaming, resizing, etc). The problem is, after editing, the changes don't stick. I can open another module and then open the form again and the changes will be gone. I can save Excel, close the file, and open it again, and the changes will be gone.
View 4 Replies View RelatedRetain Values Of UserForm Controls Between Sessions
Aug 28, 2006how you can save the status of a userform.
I have a userform that have a series of checkboxes and i want to know if i can save the status the checkboxes when i exit the userform.
Retain OptionButton Choice Between UserForm Calls
Feb 6, 2008I have two option buttons on a userform and when the user selects an option button I would like it to stay selected when the userform is called again.
View 2 Replies View RelatedSave UserForm Data Into Separate Workbook
May 22, 2008I have a form which creates a budget based on user input. I want to save that user input in a separate workbook. My goal is to reduce the file size. I have several modules which perform evaluations/ calculations, and then format the output in a worksheet, but the size is pretty large 450KB.
I was thinking that I could set the control source, to different cells on the worksheet, and then just copy the worksheet to a new workbook, and save the data by itself. Then if I wanted to change the data for a given budget, I would copy the sheet back into the workbook that contains the modules, and load the form again. Is this a good solution? Is there a better way? Please let me know if I can provide more information.
Open 2nd Workbook From Userform Then Close 2nd Userform?
Jul 4, 2012What 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
How Userform Commandbutton Actually Save Data Into Excel Sheet
Feb 21, 2014I have a Userform.Which is working perfectly to send data to excel sheet.
In the userform there is command button (commandbutton1) which is used to send data to excel sheet...This only send data but it actually does not save it into excel sheet.When I close excel sheet it asks do you want to save changes?
But I need when commandbutton1 is press to send data to excel sheet , it should actually save data into excel sheet.
[Code] .....
Save Data To Worksheet Dependent On Userform Dropdown
Jul 14, 2009I'm a little out of my element(normally program PLC's). I have always been lucky on searching others programs and have been real successful at piecing them together to fit the application I needed. The current workbook I am working on for my supervisor will allow him to keep track of personnel's weekly job assignments. The workbook and userform work fine when just using one worksheet.
What i would like to be able to do is use the "assigned to" combobox be the indicator as to which worksheet the data is saved to. I have tried to do it several different ways but have been un-successful. Have spent the last two days reading and searching for a solution but have yet to find or understand one.
Save And Close In VBA
Nov 22, 2006I 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.........
Save And Close Workbook Using VBA
Dec 11, 2013How can i save and close one workbook in excel with vba code.
View 2 Replies View RelatedClose And Save Application
Dec 5, 2008When 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 RelatedHow To Add Save And Close Button
Sep 3, 2013I 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?
Save Workbook & Close
Jul 8, 2006One 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?
No To Save On Sheet Close
Jul 24, 2006I 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 RelatedSave As In Before Close Event
Jan 16, 2007I 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
Custom Save On Close Box
Jun 21, 2007Is 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?
Save As Shared On Close
Aug 14, 2007When 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" ?
Close And Save File After Timeout?
Jun 7, 2013i would like my sheet to save and close if left inactive after 10 mins, code would be nice
View 2 Replies View RelatedAutomatically Close File And Save Changes With A New Name
Jun 13, 2009I 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).
Save Backup Of Workbook Before Close
Dec 15, 2009I call the macro below from a Workbook_BeforeClose sub; however, if the user has not saved changes before they hit the close then the Sub SaveFile runs and then after the backup save they are then asked do you want to save changes.
I initially had this sub run with Workbook_BeforeSave, but I don't really want to run this everytime the user selects save.
Any ideas on how to how the save changes first and then the backup?
Close On Specific Worksheet Before Save
Jan 26, 2010I've seen the code to open a workbook on a specific worksheet, but can this be done when closing. I tried:
View 3 Replies View RelatedVBA - Close And Save File After 5 Minutes?
Jan 24, 2014At work we use a spreedsheet to control our holidays. The problem we are having is the file is stored on a network and some people are opening the file and not closeing it once done with, this is locking out others from using it.
Is there any VBA code that will close the file, if not used in say 5 mins
Save, Close, Then Reopen Workbook
Oct 2, 2007I am testing the following section of code.
Sub Macro1()
Dim NewWB As Workbook, NewName As String
Set NewWb = Workbooks.Add
NewName = Application.GetSaveAsFilename(ThisWorkbook.Name)
NewWb.SaveAs NewName
NewWb.Close False
Workbooks.Open (ThisWorkbook.Path & "/" & ThisWorkbook.Name)
End Sub
It seems like I need to rename the sheet I am working on in order for this code to work. If I do not, it tells me that it "cannot save this workbook with the same name as another open workbook".
I am trying to use this code to get around an error with Excel described here: [url]
I would like to have the sheet save itself (not "Save As"), close, reopen, and continue running the macro. What adjustments do I need to make to this code to do this. I have tried different things, but I must be getting the syntax wrong.
Close Workbook Without Save Changes Dialog
Jan 9, 2009I'm not sure what I'm missing here, but I am trying to exit a spreadsheet using VBA code without saving changes.
The following code, to my mind, should work, but I keep getting a dialog asking if I want to save changes.
'* ensure workbook does not request user to save it...
ThisWorkbook.Saved = True
'* exit Excel...
Application.Quit
Error Handling (Close Without Save)
May 12, 2009I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol
Line using MATCH
On Error GoTo Exiting
where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
With Sheets("Sheet1").Range("1:1")
Set rFound = .Find("Term1")
If Not rFound Is Nothing Then
a = "Term1"
Else
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.