Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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 su 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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. -- Cheers, Ryan "Brian" wrote: 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" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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. -- Cheers, Ryan "Ryan H" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
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" wrote: 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. -- Cheers, Ryan "Ryan H" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wookbook Update from User Form
You need the file extension of Master_Engineering_Spec. I will assume it is
".xls" With Workbooks("Master_Engineering_Spec.xls").Sheets("C over 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. -- Cheers, Ryan "Brian" wrote: 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" wrote: 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. -- Cheers, Ryan "Ryan H" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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" wrote: 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. -- Cheers, Ryan "Brian" wrote: 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 Explicit ------------------------------------------------------------------------------------ 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" wrote: 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 -- Cheers, Ryan "Brian" wrote: 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 su 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update user form labels | Excel Programming | |||
User Form: Cannot Update Text Box | Excel Discussion (Misc queries) | |||
Update Range from User Form | Excel Programming | |||
Find and Update with User Form | Excel Programming | |||
Creating a User Form to update a list | Excel Programming |