Saving Input on User Form to Hidden Worksheet

[Brian]

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?

 

[Harald Staff]

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

 

[JLGWhiz]

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]

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]

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]

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]

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.

 

[JLGWhiz]

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]

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

 

[Brian]

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.