Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
I have a User Form that once it is filled in, it updates several other Work
Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
You must write code to save and reload informations like this.
Lots of ideas. Where to save is a question of what's practical. You can write to a sheet in the code file, and save this file if you want it to be there after a restart, or not save if you want it during the session only. You can write it to the registry if you want it to be application-user unique, or a textfile/ini file on a share/a database table, if it shall work in a multiuser environment. You choose what's practical, but you must write the code for this yourself, by default info dies as the userform is unloaded. HTH. Best wishes Harald "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
As Harold points out, it is largely up to the designer as to where the data
is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
My User Form is designed to work in conjunction with 3 other Work Books for
each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
I need to save the Input on the User Form in one of the Wook Books, so that
if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
I know it really makes no sense why I am doing it this way. Look at it like
this: 1: Job comes in from customer 2: Job is Engineered 3: User form is opened and 3 Workbooks open from the User Form 4: User Form is filled in and 3 work Books are Updated off Info in User Form 5: 3 Work Books are saved by Control Buttons on User Form 6: Next Job comes in and process is repeated over again on 3 New Work Books. 7: Now the 1st job has to be Update and Final changes make. 8: User Form is opened and 3 original Work Books are opened by Control Buttons on User Form. User Form has Control Buttons for opening 3 New Work books & opening 3 Exsisting Work Books. 9: When exsisting Work Book "Spec" is opened the User Form fills in from Data Saved in a Hidden Sheet in the "Spec" Work Book. 10: Work Book is Saved again with Different Appendix No. There for creating a new Work Book with it's own Hidden Sheet for the Data. That way I do not have the keep up with all these data sheets trying to figure out which one goes with what job. The Data for each job will be stored with each "Spec" Work Book. I know it sounds difficult but there could be 100-150 jobs per year and thats alot of Sheets to keep up with. So what I did was put the User Form in it's own Work Book. 1: User Form 2: Eng Spec 3: Installer Forms 4: Folder Label Also I have a Flow Chart I am Working from. I took Programing Logic in Collage, but that was 25 years ago and alot has changed since then. "JLGWhiz" wrote: I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
The way you explained it makes sense, if you are storing the UserForm data
in the workbook where it would logically be needed at some future date. There is no great difficulty in retrieving the data from there. Just be sure you specify the workbook when you do the retrieval. That is a good time to make use of the Object Variable. If you have the data stored in workbook SPEC_3, sheet 4 the set up the variable like: Dim wb As Workbook, sh as Worksheet Set wb = Workbooks(SPEC_3) Set sh = wb.Sheets("Sheet4") Then retrieve the data from Range("A1") Private Sub someButton_Click() Me.Textbox1.Text = sh.Range("A1").Value End sub. So you can put the data in any workbook and retrieve it with code from the Userform when the time comes. The code for the button can even be set up with a Select Case statement that will allow the same macro to retrieve the data from the correct workbook based on some criteria that you can build into the code to distinguish between the different files. If you plan the VBA project, it is not difficult, it just takes one step at a time. If you can draw it on paper, you can program it. "Brian" wrote in message ... I know it really makes no sense why I am doing it this way. Look at it like this: 1: Job comes in from customer 2: Job is Engineered 3: User form is opened and 3 Workbooks open from the User Form 4: User Form is filled in and 3 work Books are Updated off Info in User Form 5: 3 Work Books are saved by Control Buttons on User Form 6: Next Job comes in and process is repeated over again on 3 New Work Books. 7: Now the 1st job has to be Update and Final changes make. 8: User Form is opened and 3 original Work Books are opened by Control Buttons on User Form. User Form has Control Buttons for opening 3 New Work books & opening 3 Exsisting Work Books. 9: When exsisting Work Book "Spec" is opened the User Form fills in from Data Saved in a Hidden Sheet in the "Spec" Work Book. 10: Work Book is Saved again with Different Appendix No. There for creating a new Work Book with it's own Hidden Sheet for the Data. That way I do not have the keep up with all these data sheets trying to figure out which one goes with what job. The Data for each job will be stored with each "Spec" Work Book. I know it sounds difficult but there could be 100-150 jobs per year and thats alot of Sheets to keep up with. So what I did was put the User Form in it's own Work Book. 1: User Form 2: Eng Spec 3: Installer Forms 4: Folder Label Also I have a Flow Chart I am Working from. I took Programing Logic in Collage, but that was 25 years ago and alot has changed since then. "JLGWhiz" wrote: I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
Maybe even better than a button with a select case statement would be a
Combobox that you could update as new jobs and new sheets are added. Then you could just use the click or change event of the Combobox to initate the retieval for the selected data source. "Brian" wrote in message ... I know it really makes no sense why I am doing it this way. Look at it like this: 1: Job comes in from customer 2: Job is Engineered 3: User form is opened and 3 Workbooks open from the User Form 4: User Form is filled in and 3 work Books are Updated off Info in User Form 5: 3 Work Books are saved by Control Buttons on User Form 6: Next Job comes in and process is repeated over again on 3 New Work Books. 7: Now the 1st job has to be Update and Final changes make. 8: User Form is opened and 3 original Work Books are opened by Control Buttons on User Form. User Form has Control Buttons for opening 3 New Work books & opening 3 Exsisting Work Books. 9: When exsisting Work Book "Spec" is opened the User Form fills in from Data Saved in a Hidden Sheet in the "Spec" Work Book. 10: Work Book is Saved again with Different Appendix No. There for creating a new Work Book with it's own Hidden Sheet for the Data. That way I do not have the keep up with all these data sheets trying to figure out which one goes with what job. The Data for each job will be stored with each "Spec" Work Book. I know it sounds difficult but there could be 100-150 jobs per year and thats alot of Sheets to keep up with. So what I did was put the User Form in it's own Work Book. 1: User Form 2: Eng Spec 3: Installer Forms 4: Folder Label Also I have a Flow Chart I am Working from. I took Programing Logic in Collage, but that was 25 years ago and alot has changed since then. "JLGWhiz" wrote: I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
The only problem is that each Work Book is saved as a differnt name. The Name
of the Sheet in each Saved Work Book is going to be "Job Data". Once I get the sheet designed I am just going to add all the Text Boxes, Combo Boxes, etc... to the sheet then make it a hidden sheet. Example: 'Save Engineering Spec 11 Control Button Private Sub Save_Engineering_Spec_11_Click() Dim strFile As String Dim fileSaveName As Variant Dim myMsg As String strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value fileSaveName = Application.GetSaveAsFilename _ (InitialFileName:=strFile, _ fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") If fileSaveName < False Then ActiveWorkbook.SaveAs Filename:= _ fileSaveName, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False Else MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the Engineering Spec." & vbCrLf & _ "Engineering Spec was not Saved.", _ Title:="C.E.S." End If End Sub ------------------------------------------------------------------------------------------------ I am just not sure how to add the code to my "Open_Existing_Engineer_Spec_9" Control Button. Here is the code I am using to open the exsisting Work Book. This code works perfect cause it only allows the user to open the correct Work Book name "Spec *.xlsm" ' Open Existing Engineering Spec 9 Control Button Private Sub Open_Existing_Engineer_Spec_9_Click() Dim FileToOpen As Variant Dim bk As Workbook Dim LastBackSlashPos As Long Dim myMsg As String FileToOpen = Application.GetOpenFilename("SPEC (*.xlsm), Spec*.xlsm") If FileToOpen = False Then MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled opening an Engineering Spec", _ Title:="C.E.S." ' MsgBox "User Canceled Operation, No Engineering Spec was Opened", , "C.E.S." Exit Sub End If LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare) If UCase(Mid(FileToOpen, LastBackSlashPos + 1, 4)) < UCase("SPEC") Then MsgBox prompt:=Engineer_2.Value & vbLf & "You can only open an exsisting Engineering Spec", _ Title:="C.E.S." Exit Sub End If Set bk = Workbooks.Open(Filename:=FileToOpen) End Sub ------------------------------------------------------------------------------------------------ One thing I can say is that I have an advantage over most people using the same method as you and I for doing this kind of stuff. I have to assume you are doing your "Critical Path Method" on a bunch of 8 1/2" x 11" paper, where being an Engineer I have a huge Printer that can print 48" x 150' if I need it. I printed my Flow Chart on a 48" x 72" sheet and have it taped on my wall. Then as each Block is complete, I just color it with a high lighter to show it as completed. Nice wall paper , huh? "JLGWhiz" wrote: Maybe even better than a button with a select case statement would be a Combobox that you could update as new jobs and new sheets are added. Then you could just use the click or change event of the Combobox to initate the retieval for the selected data source. "Brian" wrote in message ... I know it really makes no sense why I am doing it this way. Look at it like this: 1: Job comes in from customer 2: Job is Engineered 3: User form is opened and 3 Workbooks open from the User Form 4: User Form is filled in and 3 work Books are Updated off Info in User Form 5: 3 Work Books are saved by Control Buttons on User Form 6: Next Job comes in and process is repeated over again on 3 New Work Books. 7: Now the 1st job has to be Update and Final changes make. 8: User Form is opened and 3 original Work Books are opened by Control Buttons on User Form. User Form has Control Buttons for opening 3 New Work books & opening 3 Exsisting Work Books. 9: When exsisting Work Book "Spec" is opened the User Form fills in from Data Saved in a Hidden Sheet in the "Spec" Work Book. 10: Work Book is Saved again with Different Appendix No. There for creating a new Work Book with it's own Hidden Sheet for the Data. That way I do not have the keep up with all these data sheets trying to figure out which one goes with what job. The Data for each job will be stored with each "Spec" Work Book. I know it sounds difficult but there could be 100-150 jobs per year and thats alot of Sheets to keep up with. So what I did was put the User Form in it's own Work Book. 1: User Form 2: Eng Spec 3: Installer Forms 4: Folder Label Also I have a Flow Chart I am Working from. I took Programing Logic in Collage, but that was 25 years ago and alot has changed since then. "JLGWhiz" wrote: I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Input on User Form to Hidden Worksheet
FYI, if you ever need me to print a "Flow Chart" for you, I would be happy
to. I can print it and mail it to you, so you can see the whole sheet at one time. "JLGWhiz" wrote: Maybe even better than a button with a select case statement would be a Combobox that you could update as new jobs and new sheets are added. Then you could just use the click or change event of the Combobox to initate the retieval for the selected data source. "Brian" wrote in message ... I know it really makes no sense why I am doing it this way. Look at it like this: 1: Job comes in from customer 2: Job is Engineered 3: User form is opened and 3 Workbooks open from the User Form 4: User Form is filled in and 3 work Books are Updated off Info in User Form 5: 3 Work Books are saved by Control Buttons on User Form 6: Next Job comes in and process is repeated over again on 3 New Work Books. 7: Now the 1st job has to be Update and Final changes make. 8: User Form is opened and 3 original Work Books are opened by Control Buttons on User Form. User Form has Control Buttons for opening 3 New Work books & opening 3 Exsisting Work Books. 9: When exsisting Work Book "Spec" is opened the User Form fills in from Data Saved in a Hidden Sheet in the "Spec" Work Book. 10: Work Book is Saved again with Different Appendix No. There for creating a new Work Book with it's own Hidden Sheet for the Data. That way I do not have the keep up with all these data sheets trying to figure out which one goes with what job. The Data for each job will be stored with each "Spec" Work Book. I know it sounds difficult but there could be 100-150 jobs per year and thats alot of Sheets to keep up with. So what I did was put the User Form in it's own Work Book. 1: User Form 2: Eng Spec 3: Installer Forms 4: Folder Label Also I have a Flow Chart I am Working from. I took Programing Logic in Collage, but that was 25 years ago and alot has changed since then. "JLGWhiz" wrote: I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. My suggestion to use the workbook containing the form was based on your statement above. I am assuming the the UserForm and its controlling code are all contained in the same workbook, rather than three separate workbooks. If that is true, then logically, the workbook with the controlling code would be the place to store the data to re-populate the form. All you are doing at that point is putt6ing data back that was there before the workbook closed. The data will most likely change as soon as the other workbooks are opened and users make changes. While the form works with other workbooks, it is controlled for showing, hiding and unloading by code in only one. I don't know what purpose it would serve to have the text box data stored in another workbook. It seems to me that if you are going to use a separate sheet in each workbook to display data to the users, then you are defeating the purpose of the UserForm. But, that is your choice to make because you know how the data will be used.. You are essentially correct about how to transfer the data between a worksheet and the form. A tool that I use when trying to organize a complex project is a flow diagram, similar to CPM or Critical Path Method. I just draw boxes and list the steps I need to perform to logically proceed to the next step until I reach my obective. This allows me to see the decisions that will have to be made where an outside procedure might need to be interjected and where loops might need to occur for user interface.. Then I tackle one step at a time when writing the code. I think this is something like what IBM used to teach back when they were still using punched cards and batch processing, but it is still a useful planning tool and save a lot of headaches and debugging. "Brian" wrote in message ... My User Form is designed to work in conjunction with 3 other Work Books for each Job. 1: Eng Spec 2: Installer Forms 3: Folder Label Since there will alot of Different Work Books, it would alot easier to save the Information from the User Form to A Hidden Sheet in the Work Book (Eng Spec). By doing it that way, when ever that Work Book (Eng Spec) is opened the Information Flows the oppisite direction. As of now the User Form is filled Out and all 3 Work Books are Updated off of it. Then all the Work Books are saved in there own Job Directory. I want to save the information for each Job, with that job. I know this applies if you want to go from the User Form to the Wook Book: .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value I have to assume that if you Reverse the flow would this be the process? Me("Office_1").Value = .Range("A09").Value Me("Address_11").Value = .Range("A10").Value Me("Address_12").Value = .Range("A11").Value Me("City_1").Value = .Range("A12").Value "JLGWhiz" wrote: As Harold points out, it is largely up to the designer as to where the data is stored. If you want ease of recovery, then it would probably be wise to store it in the same file (workbook)as the UserForm is in. You could use a sheet that is not used in the other operations of the project and hide it when not needed. The process would be to copy the data from the UserForm before closing the form. Hide the sheet. Save the file before closing the workbook. Then when opening the workbook, unhide the sheet and use the initialize event of the UserForm to repopulate the form from the data stored on the sheet. then hide the sheet until you are ready to shut down again. "Brian" wrote in message ... I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking to see if a user form is hidden | Excel Programming | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
Efficient User Form vba coding for saving to worksheet | Excel Programming | |||
Saving a file from a command button with user input | Excel Programming | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming |