Wookbook Update

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

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"?
 
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.
 
When I added the .xls to "Master_Engineering_Spec.xls" I get and error message

Run-Time Error"9": Subsciprt out of range
 
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("Cover 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
 
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
 
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?
 
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("Cover 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.
 
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
 
Back
Top