I have a Work Book that only contains a User Form in it. This User Form is
for Updating 3 other Work Books. In my User Form I have a Control Button
named "Update_Engineer_Spec_8". When I fill in the information on the User
Form I click the Update Button and it fills in the Cells on the other Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked. What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B

One thing could be this

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")

Needs to include the workbook name and extension. Is it "Master Engineering
Spec.xls"?
--
HTH,

Barb Reinhardt



"Brian" wrote:

I have a Work Book that only contains a User Form in it. This User Form is
for Updating 3 other Work Books. In my User Form I have a Control Button
named "Update_Engineer_Spec_8". When I fill in the information on the User
Form I click the Update Button and it fills in the Cells on the other Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked. What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a "Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form is
for Updating 3 other Work Books. In my User Form I have a Control Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked. What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B

When I added the .xls to "Master_Engineering_Spec.xls" I get and error message

Run-Time Error"9": Subsciprt out of range



"Barb Reinhardt" wrote:

One thing could be this

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")

Needs to include the workbook name and extension. Is it "Master Engineering
Spec.xls"?
--
HTH,

Barb Reinhardt



"Brian" wrote:

I have a Work Book that only contains a User Form in it. This User Form is
for Updating 3 other Work Books. In my User Form I have a Control Button
named "Update_Engineer_Spec_8". When I fill in the information on the User
Form I click the Update Button and it fills in the Cells on the other Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked. What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B

I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a "Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form is
for Updating 3 other Work Books. In my User Form I have a Control Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked. What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.

Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.

No, I have 4 Workbooks open. So no matter which one it is, I still get the
same error message. It has to be one of them.

1: Master_Engineering_Spec.xls (Excel 97-2003 Workbook)
2: Master_Engineering_Spec.xlsm (Excel -Macro-Enabled Workbook)
3: Master_Engineering_Spec.xlsx (Excel 2007 Workbook)
4: Master_Engineering_Spec.xltm (Excel -Macro-Enabled Template)

When I look at the VBAProject the sheets are shown as Sheet01 (Cover Sheet)
On the Tab in the Work Book, the tab say's Cover Sheet

What did I miss here?

"Tim Williams" wrote:

Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.



.

I see your still having problems Brian. Lets see if we can narrow this down.

Make sure the workbook name is absolutely correct. Does it have
under-scores or spaces between the words. Ensure that the workbook is open
if the workbook isn't open you will get a Subscript out of Range Error.If you
get an error indicate teh line the error occurs.

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me.Location_4.Value
.Range("D20").Value = Me.Address_41.Value

End With


Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan


"Brian" wrote:

No, I have 4 Workbooks open. So no matter which one it is, I still get the
same error message. It has to be one of them.

1: Master_Engineering_Spec.xls (Excel 97-2003 Workbook)
2: Master_Engineering_Spec.xlsm (Excel -Macro-Enabled Workbook)
3: Master_Engineering_Spec.xlsx (Excel 2007 Workbook)
4: Master_Engineering_Spec.xltm (Excel -Macro-Enabled Template)

When I look at the VBAProject the sheets are shown as Sheet01 (Cover Sheet)
On the Tab in the Work Book, the tab say's Cover Sheet

What did I miss here?

"Tim Williams" wrote:

Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.



.

Your code as shown should work if there really is an open workbook
with that name.
The only thing I can think of is that all of your files aren't open in
the *same instance* of excel....

Tim


On Dec 22, 4:50*am, Brian wrote:
No, I have 4 Workbooks open. So no matter which one it is, I still get the
same error message. It has to be one of them.

1: Master_Engineering_Spec.xls * *(Excel 97-2003 Workbook)
2: Master_Engineering_Spec.xlsm (Excel -Macro-Enabled Workbook)
3: Master_Engineering_Spec.xlsx *(Excel 2007 Workbook)
4: Master_Engineering_Spec.xltm (Excel -Macro-Enabled Template)

When I look at the VBAProject the sheets are shown as Sheet01 (Cover Sheet)
On the Tab in the Work Book, the tab say's Cover Sheet

What did I miss here?



"Tim Williams" wrote:
Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

* *With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
* * * *.Range("D19").Value = Me("Location_4").Value
* * * *.Range("D20").Value = Me("Address_41").Value

* *End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message

"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

* * * * *.Range("D19").Value = Me("Location_4").Value
* * * * .Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.

"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named *"Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

* *With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
* * * *.Range("D19").Value = Me("Location_4")
* * * *.Range("D20").Value = Me("Address_41")

* *End With

End Sub

Thanks
B

.

.- Hide quoted text -

- Show quoted text -

I am going to try renaming the Work Book to "Spec" and see if that helps.

"Brian" wrote:

No, I have 4 Workbooks open. So no matter which one it is, I still get the
same error message. It has to be one of them.

1: Master_Engineering_Spec.xls (Excel 97-2003 Workbook)
2: Master_Engineering_Spec.xlsm (Excel -Macro-Enabled Workbook)
3: Master_Engineering_Spec.xlsx (Excel 2007 Workbook)
4: Master_Engineering_Spec.xltm (Excel -Macro-Enabled Template)

When I look at the VBAProject the sheets are shown as Sheet01 (Cover Sheet)
On the Tab in the Work Book, the tab say's Cover Sheet

What did I miss here?

"Tim Williams" wrote:

Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.



.

Did it work?
--
Cheers,
Ryan


"Brian" wrote:

I am going to try renaming the Work Book to "Spec" and see if that helps.

"Brian" wrote:

No, I have 4 Workbooks open. So no matter which one it is, I still get the
same error message. It has to be one of them.

1: Master_Engineering_Spec.xls (Excel 97-2003 Workbook)
2: Master_Engineering_Spec.xlsm (Excel -Macro-Enabled Workbook)
3: Master_Engineering_Spec.xlsx (Excel 2007 Workbook)
4: Master_Engineering_Spec.xltm (Excel -Macro-Enabled Template)

When I look at the VBAProject the sheets are shown as Sheet01 (Cover Sheet)
On the Tab in the Work Book, the tab say's Cover Sheet

What did I miss here?

"Tim Williams" wrote:

Sounds like you do not have an open workbook with that name, or if you do,
it doesn't have a sheet with that name.
Assuming you get the error on the "With..." line.

Tim

"Brian" wrote in message
...
I added the .Value

Here is the code

' Update Engineering Spec Button

Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master_Engineering_Spec.xls").Sheets("C over Sheet")
.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

End With

End Sub

I get the following error message, Run-Time Error"9": Subsciprt out of
range

I removed the .xls extension and still the same error message


"JLGWhiz" wrote:

You night also need to add .Value after your TextBox and ComboBox.

.Range("D19").Value = Me("Location_4").Value
.Range("D20").Value = Me("Address_41").Value

I think that if it was the file extension, you would be getting a
"Subscript
out of range" message, unless you have alerts turned off.



"Brian" wrote in message
...
I have a Work Book that only contains a User Form in it. This User Form
is
for Updating 3 other Work Books. In my User Form I have a Control
Button
named "Update_Engineer_Spec_8". When I fill in the information on the
User
Form I click the Update Button and it fills in the Cells on the other
Work
Book.

Control Button name is "Update_Engineer_Spec_8"
User Form Name is "UserForm1
Text Box name is "Location_4"
Combo Box name is "Address_41"
Workbook name is "Master_Engineering_Spec"
Workbook Sheet name is "Cover Sheet"

This is the code I have, but for some reason the Work Book "Master
Engineering Spec" does not Update when the Control Button is clicked.
What
did I do incorrectly?

' Update Engineering Spec Button
Private Sub Update_Engineer_Spec_8_Click()

With Workbooks("Master Engineering Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me("Location_4")
.Range("D20").Value = Me("Address_41")

End With

End Sub


Thanks
B


.



.