I think the solution is to change the new workbook reference from "Master
Engineering Spec.xlsm" to the following:

strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

Either that or make it look for both.

What do you think?


"JLGWhiz" wrote:

Like I said, I do not believe you have to change any file names. I believe
all you have to do is put a line of code in your SaveAs macro, after the
SaveAs has executed, to open the "Master Engineering Spec.xlsm" again. What
happens when you execute the SaveAs is:

1. The workbook you have just updated "Master Engineering Spec.xlsm"
changes to
strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value
2. At this point, "Master Engineering Spec.xlsm" has, in effect, been
closed. Look at the title bar when the SaveAs macro runs. You will see the
name change. Also check the status bar at the bottom of the screen and you
will no longer see the MES file listed as active.

3. The UserForm is operating from the new workbook saved under 1. above
since you did not specify the file to save as .xlxs. The new workbook
contains the entire code from the MES file. But if you try to reference any
of the sheets from that file you will get the Subxcript out of Bounds error
because the MES file is no longer open.

4. The simple solution is to re-open the file if you need to make a
correction.

5. There is a possibility that you might have to close the UserForm and
re-open it in the
MES file to avoid error messages from that operation, although I am not
completely sure that would occur, Better to be on the safe side. Remember
that after the SaveAs, the UserForm was tied to the new workbook, so by
opening the original MES file, closing unloading the UserForm and re-opening
it, it should again be tied to the MES file.

6. Unless my understanding of how you have programmed this is all screwed
up, you should then be able to make corrections on the form and re-do the
SaveAs routine to update the job file.

If you are now totally confused, go to bed, get some sleep. Come back
tomorrow and read this again. It is not complicated, it just takes a lot of
verbage to cover the details.



"Brian" wrote in message
...
Would changing the file name on the New Workbook "Master Engineering
Spec.xlsm" to

strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

Solve the Problem or would it be better to add another control button to
update the StrFile?


"JLGWhiz" wrote:

BTW, as Dave pointed out, when you run the update and do a SaveAs
operation,
you effectively close the parent workbook, so the cure for the problem
could
be as simple as adding a line of code after the SaveAs command to re-open
the "Master Engineering Spec.xlsm" workbook. Check that out and see if
that
will work.


"Brian" wrote in message
...
Everything works exactly as it should. When you open an exsisiting Work
Book
it fills in the User Form from the Hidden Sheet in the Work Book, but
when
you change or add anything to the User Form and click on Update again
it
gives you a run Time Error 9, Subscript out of range.

'Update Engineering Spec Control Button(Sheet 1)
Private Sub Update_Engineer_Spec_10_Click()

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

The only thing I can think of is that the file name is not ("Master
Engineering Spec.xlsm"). Remember the file name is assigned as follows:

'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 " & CLLI_Code_1.Value _
& Space(1) & TEO_No_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 was wondering if adding another set of update buttons work fix this
problem.
There will be a set for New Work Books and a set for Exsisiting Work
Books.

The New Work Book will Look for the Name "Master Engineering
Spec.xlsm".

The Exsisitng Work Book will Look for the Name
strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

Either that or change the New Work Book to the name as the same varible
as
a
Exsisiting Work Book. That way no matter Which Work Book is open it
will
see
the "Spec" in the name and update it.



"JLGWhiz" wrote:

Brian, I thought that was one of the functions of the button to
repopulate
the form, to allow for updates to previously entered data. Is this
the
part
that is not working? I read Dave's answer about the different
workbook
name
and that would present a problem if you are trying to recall the data
from
that workbook. But I had the impression that you were storing the
data
to
recover in the master workbook and would then resave the updates in
the
job
workbook. Maybe my concept was all screwed up.



"Brian" wrote in message
...
I have this code when I go to update a new workbook, but once the
workbook
is
updated and saved, it will not allow you to reupdate it. I think the
reason
is because it's looking for the workbook "Master Engineering
Spec.xlsm"
and
that is not the workbook name.

Here is the sequence I am following.
1: Open User Form
2: Open New Engineering Spec (Control Button
"Open_New_Engineer_Spec_8")
3: Fill in the user Form
4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10"
5: Save Engineering Spec workbook (I automaticaly assigns the name.

Now here is where the problem is. You forgot to fill in something on
the
User Form. So you go back and put it in, then you click on the
Control
Button
"Update_Engineer_Spec_10" and you get a run Time Error message.

How can I get around this so that it updates the workbook? Do I need
to
add
another Control Button to up date exsisting worksbooks?


'Update Engineering Spec Control Button(Sheet 1)
Private Sub Update_Engineer_Spec_10_Click()

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")
'Job Address Information
.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
.Range("B12").Value = Me("State_1").Value
.Range("C12").Value = Me("Zip_Code_1").Value

More Code Here, but same as above.

End With


.



.



.