Wookbook Update from User Form

[Brian]

I have a User Form with several Control Buttons on it. This user Form has
about (50-60) Text Boxes & Combo Boxes on it. I would like to be able to fill
in the User Form and click on the Control Button and it updates the Workbook
automatically.

Control Button name is "Update_Enginering_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"

If someone would help me do the first one or two boxes, I can do the rest, I
am just not sure exactly how to do this. I would like to put each Control
Button in it's own Module to make it easy to trouble shoot later on if I need
to.

I need to use the Wookbook Name Reference, due to the fact that this User
Form Updates 3 Different Work Books. Each Work Book Update has it's own
Control Button. If I can get the first Workbook done the others are the same
just different Name and Cell Ref.

I think the code is something like this, but not sure:
Workbooks("Master_Engineering_Spec").Sheets("Cover Sheet").Range("D19") _
=UserForm1("Location_4")

Workbooks("Master_Engineering_Spec").Sheets("Cover Sheet").Range("D20") _
=UserForm1("Address_41")

Any help would be greatly appriecated.

Thanks

 

[Ryan H]

Put this in your control buttons Click Event. You will need to specify the
range addresses and the rest of your textboxes/comboboxes. Hope this helps!
If so, let me know, click "YES" below.

Option Explicit

Private Sub Update_Enginering_Spec_8_Click()

With Workbooks("Master_Engineering_Spec").Sheets("Cover Sheet")
.Range("A1").Value = Me.Location_4.Value
.Range("A2").Value = Me.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

 

[Brian]

I cut and pasted it in my code as it is. When I did the Option Explict whet
to the code above the line.

End Select

End Sub
Option Explici
------------------------------------------------------------------------------------
Private Sub Update_Enginering_Spec_8_Click()

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

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

Also when I look at the WorkBook properties VBA Project, the page is shown
as follows: Sheet01(Cover Sheet). Does it make a difference it the page
designation?

 

[Ryan H]

Option Explicit should go at the very top of the module. It basically
ensures you have all you variables and control names correct. No code should
be with it in your application. For example,

Option Explicit
__________________________________
Private Sub Update_Enginering_Spec_8_Click()

With ThisWorkbook.Sheets("Cover Sheet")
.Range("D19").Value = Me.Location_4.Value
.Range("D20").Value = Me.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub
_________________________________
Sub AnotherSubHere()

End Sub
_________________________________


You need to put the exact sheet name in this line.

With ThisWorkbook.Sheets("Cover Sheet")

If you don't know the exact sheet name you can look at the spelling on Tab
starting at the bottom left of you screen or just make sure the "Cover Sheet"
is the activesheet and put this in the Immediate Window to get its name.

Msgbox ActiveSheet.Name

Hope this helps! If so, click "YES" below.

 

[Brian]

I posted the code in the main code window. Ok, I need to create a module and
post the code inside it.

How do I reference the module from the button?

 

[Ryan H]

There are two ways you can run this code. You can put the code directly into
the Click Event:

Private Sub Update_Enginering_Spec_8_Click()

With ThisWorkbook.Sheets("Cover Sheet")
.Range("D19").Value = Me.Location_4.Value
.Range("D20").Value = Me.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

Or do what you want to do and call the sub located in a Standard Module.
You just need to make sure you declare the procedure to be Public.

Private Sub Update_Enginering_Spec_8_Click()
Call Update Stuff
End Sub

Public Sub UpdateStuff()

With ThisWorkbook.Sheets("Cover Sheet")
.Range("D19").Value = Me.Location_4.Value
.Range("D20").Value = Me.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

Hope this helps! If so, click "YES" below.

 

[Ryan H]

Oops! Correction:

If you put the code into a standard module you will have to pass the
userform object (Me) to the UpdateStuff procedure. Do that like this.

Private Sub Update_Enginering_Spec_8_Click()
Call Update Stuff(Me)
End Sub

Public Sub UpdateStuff(Frm As Object)

With ThisWorkbook.Sheets("Cover Sheet")
.Range("D19").Value = Frm.Location_4.Value
.Range("D20").Value = Frm.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

Hope this helps! If so, click "YES" below.

 

[Brian]

1. I double clicked on the UserForm1
2. The UserForm Appears
3: Double clicked on the Control Button named "Update_Engineer_Spec_8"
4: Pasted the following code in the Code Window

Private Sub Update_Engineer_Spec_8_Click()
With Workbooks("Master_Engineering_Spec").Sheets("Cover Sheet")
.Range("D19").Value = Me.Location_4.Value
.Range("D20").Value = Me.Address_41.Value

' list the rest of your ranges and textboxes/comboboxes here
End With

End Sub

5: Added ("Master_Engineering_Spec") to the code
6: Clicked on Run, Run
7: User Screen came up
8: Got Error Meaasage
Run_Time Error "9" Subscript out of range

9: Removed ("Master_Engineering_Spec") from code
Compile error: Method or data member not found

I think I broke it!

 

[Ryan H]

You need the file extension of Master_Engineering_Spec. I will assume it is
".xls"

With Workbooks("Master_Engineering_Spec.xls").Sheets("Cover Sheet")

You may not need to reference the workbook. If the code is contained in the
workbook you can use something like this.

With ThisWorkbook.Sheets("Cover Sheet")

Or

With Sheets("Cover Sheet")

Hope this helps! If so, click "YES" below.