Referencing User Form from WorkBook

[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.
I have the following code for when my Work Book is opened:

' Open Existing Eng Spec 9 Control Button
Private Sub Open_Existing_Eng_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:=Engineering_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)
'=====================================================
'Update Data Storage Sheet (Hidden in Job Work Book)
'=====================================================

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")
' Site Information:
Me("CLLI_Code_1").Value = .Range("D02").Value
Me("Office_1").Value = .Range("D03").Value
Me("Address_11").Value = .Range("D04").Value
Me("Address_12").Value = .Range("D05").Value

More Code like above here

' Line 46
Me("Type_Work_723").Value = .Range("C83").Value
Me("Bay_Description_723").Value = .Range("J83").Value
Me("Bay_ID_723").Value = .Range("F83").Value
Me("Description_Work_723").Value = .Range("M83").Value
'=====================================================

End Sub


How would I reference the User Form from the Work Book so that when the Work
Book is opened the Data from the hidden Sheet is automaticaly sent back to
the User Form to fill it back in?

User Form = UserForm1
Work Book = Master Engineering Spec.xlsm
Sheet = Job Data

 

[ryguy7272]

If you want your UserForm to auto-populate, do something like this:
Private Sub UserForm_Activate()
On Error Resume Next

MsgBox "CHANGES TO THIS FORM WILL NOT BE STORED" ‘or whatever message you
want to display…

Sheets("Sheet1").Activate
TextBox1.Text = Cells(1, 1)
TextBox2.Text = Cells(1, 2)
TextBox3.Text = Cells(1, 3)
TextBox4.Text = Format(Cells(1, 4), "#,##0.00") ‘ this will apply number
formatting to the values inside the TextBox…
TextBox5.Text = Format(Cells(1, 5), "##0.00%") ‘ this will apply percent
formatting to the values inside the TextBox…
On Error Resume Next

End Sub

 

[Ryan H]

I don't believe there is a direct way to reference a userform in another
workbook. I would setup a reference to the workbook that contains the
userform and you should beable to use your code with little modification.
Open the workbook you want to reference (the one with the userform) and in
the VBE select the workbook that will reference your userform workbook, then
click Tools>References and select the userform workbook. Now you will be
able to reference the userform from that workbook as if it was in your
workbook.

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")

Userform1.CLLI_Code_1.Value = .Range("D02").Value
'etc.
End With

 

[Brian]

I don't understand. All I have is a hidden sheet in a Work Book (Name of Work
Book varies).

The code I use to open the Work Book is shown below. All I trying to do is
get the information to go from the WorkBook, back to the User Form.

Example:
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 be alot of Different Work Books, it would be 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.

Now I go to open an exsisitng workBook how do i get the information back to
the User Form without retyping it?

 

[JLGWhiz]

Hi Brian, I believe you have buttons on your UserForm that open the various
workbooks where you will store the UserForm TextBox data on hidden sheets.
If this is correct, then you could insert your retrieval code into the click
event code so that as soon as that work book is opened, the data can be
recovered from the hidden sheet. In that case the UserForm could be
referred to as Me.

Workbooks("SPEC").Sheets("Hidden").Visible = True
Me.TextBox1.Text = Worksbooks("SPEC")/Sheets("Hidden").Range("A1").Value

The above example would retrieve the stored value from the hidden sheet cell
A1 and place it in TextBox1. Only if you do not run the code from the
UserForm would you have to make reference to the full object path. i.e.
UserForm1.TextBox1 = etc.

If you try to put the code in the public code module, the the workbooks
will need to be opened first and the UserRorm will have to be active for the
code to work.

 

[Brian]

Very nice to hear from you. I am so glad your helping me with this.

I really did try to do this myself, but I think I broke it again. I put the
code in to send the information from the User Form to the hidden sheet "JOB
DATA" and it works perfect.

Now if I just get the Data to come back when the Work Book is opened. I
guess the problem is that the Work Book name will vary. I figured if I put
the code in with the open exsisting Work Book it would auto fill off that.
How bad did I break it this time?

What I tried to do was to put the code as follwows:

' Open Existing Engineering Spec 9 Control Button
Private Sub Open_Existing_Engineering_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)

'=====================================================
'Update from Data Storage Sheet (Hidden in Job Work Book)
'=====================================================

With UserForm1
' Site Information:

UserForm1("CLLI_Code_1").Value = .Range("D02").Value
UserForm1("Office_1").Value = .Range("D03").Value
UserForm1("Address_11").Value = .Range("D04").Value
UserForm1("Address_12").Value = .Range("D05").Value

More Code for all the Text Boxes (Lines 02-45)

' Line 46
Me("Type_Work_723").Value = .Range("C83").Value
Me("Bay_Description_723").Value = .Range("J83").Value
Me("Bay_ID_723").Value = .Range("F83").Value
Me("Description_Work_723").Value = .Range("M83").Value
'=====================================================
End With

End Sub

 

[Brian]

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?

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]

I am trying to get my head straight on what you are showing.

1. I believe you said you were going to put a hidden sheet in each job
workbook. Did you do that?

2. Is the hidden sheet named "Job Data" in each of the job workbooks?

3. Is the data stored in the same range of cells in each hidden sheet? If
so what are the cell addresses for the range?

Before answering these questions, read on. I assumed that the answer is yes
for the first two and that the ranges are those you show in the posted code.

Again, the UserForm loses all values when the form is Unloaded, so the data
must be stored prior to unloading the form and closing the workbook
containing the form.

Conversely, nothing can be added to the form until the form is initialized
during the Load or Show method. So, the workbook containing the form must
be open and the form must be initialized before any of the stored data can
be recovered and populated into the controls on the form.

You probably were aware of the above, but I want to make sure the bases are
covered as we go through this.

This is the part that confuses me. Your posting says this is the code to
recover the data from the hidden sheet:

With UserForm1
' Site Information:

UserForm1("CLLI_Code_1").Value = .Range("D02").Value
UserForm1("Office_1").Value = .Range("D03").Value
UserForm1("Address_11").Value = .Range("D04").Value
UserForm1("Address_12").Value = .Range("D05").Value

More Code for all the Text Boxes (Lines 02-45)

' Line 46
Me("Type_Work_723").Value = .Range("C83").Value
Me("Bay_Description_723").Value = .Range("J83").Value
Me("Bay_ID_723").Value = .Range("F83").Value
Me("Description_Work_723").Value = .Range("M83").Value
'=====================================================
End With

What I do not see in this code is a reference to the sheet "Job Data" or the
workbook that it is in. Assuming the Object Variable bk is the job workbook
that was opened by a user and the ranges referenced above are, in fact, on
sheet Job Data, then I would write the code like this:

With bk.Sheets("Job Data")
' Site Information:

UserForm1("CLLI_Code_1").Value = .Range("D02").Value
UserForm1("Office_1").Value = .Range("D03").Value
UserForm1("Address_11").Value = .Range("D04").Value
UserForm1("Address_12").Value = .Range("D05").Value

'More Code for all the Text Boxes (Lines 02-45)

' Line 46
Me("Type_Work_723").Value = .Range("C83").Value
Me("Bay_Description_723").Value = .Range("J83").Value
Me("Bay_ID_723").Value = .Range("F83").Value
Me("Description_Work_723").Value = .Range("M83").Value

End With

If you get an error with this, you might need to use the form name instead
of Me. Although Me should work since you are running the code from the form
module.

 

[Brian]

1: Only 1 Hidden Work Sheet "Job Data" in the Wook Book "Spec". All the data
is contained in that 1 Worksheet.

Since all the Data is on the 1 Work Sheet "Job Data I have all the Text,
Combo Boxes already referenced over to the Work Sheet "Job Data". Really all
I need is how to reference the Work Sheet "JOB DATA" to UserForm1

This is how all the References are done:
User Form Work Sheet
--------------------------------------------------------
Me("Location_4").Value = .Range("D26").Value
Me("Address_41").Value = .Range("D27").Value
Me("Address_42").Value = .Range("D28").Value
Me("City_4").Value = .Range("D29").Value
Me("State_4").Value = .Range("D30").Value
Me("Zip_Code_4").Value = .Range("D31").Value



I just tried it and it worked all except for any empty cells gives a
Run-Time Error "424" Object Required

 

[Brian]

Sorry it wa smy Bad. I was missing and = sign between the work Book & Userform.

I have another question I am going to post if you would please look for it.
It has to do with Combo Boxes & Worksheets.

Thanks