Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
I have a user Form with several Check Boxes in it. I would like for the Check
Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") ..Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
Hi Brian, this is probably your problem, but without some explanation I
can't be sure. UserForm1("Office_Package_Preparations_101").Value It looks like a comination of a UserForm code name and a string name. If UserForm1 is named "Office_Package_Preparations_101" then you should not need the UserForm1 as part of the reference. If it is a control on the userform then you need a decimal point after UserForm1 and remove the Parentheses and quotemarks. What is the item that you are trying to return the value of? "Brian" wrote in message ... I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
Re-reading your post, I believe that this would be the correct syntax for
the UserForm1 items. UserForm1.Office_Package_Preparations_101.Value "Brian" wrote in message ... I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
Even though this syntax looks weird to most excel VBA users, it works ok.
I'm not sure if it's common in the Access arena, but it seems like the people who use this syntax are much more familiar with access (based on the few posts like this that I've seen). JLGWhiz wrote: Hi Brian, this is probably your problem, but without some explanation I can't be sure. UserForm1("Office_Package_Preparations_101").Value It looks like a comination of a UserForm code name and a string name. If UserForm1 is named "Office_Package_Preparations_101" then you should not need the UserForm1 as part of the reference. If it is a control on the userform then you need a decimal point after UserForm1 and remove the Parentheses and quotemarks. What is the item that you are trying to return the value of? "Brian" wrote in message ... I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
What goes wrong when you run this?
Do you really have a range (a cell) named "Check Box 01"? If you have checkboxes from the Forms toolbar: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkboxes("check box 01").value _ = UserForm1("Office_Package_Preparations_101").Value end with If you have checkboxes from the Control toolbox toolbar, then I'd rename them to remove the space characters: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkbox01.value = UserForm1("Office_Package_Preparations_101").Value end with Or without renaming the checkbox: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .OLEObjects("check box 01").Object.Value = Userform1("checkbox1").Value End With Brian wrote: I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
On my User Form if the Check box is checked, when I click on Update Forms the
Check box in the workbook checks to match the User Form. I tried to use code similar to that of transfering data from a User Form to a Cell Reference ID. The check boxes in the Forms workbook are only for printing, they do not control anything. When I changed it to the following I get this Error Message. Run-Time Error "1004" Application-defined or object-defined error Sub Update_Installer_Forms1() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") 'Office Package Preparations 1 Code: (Error is in line of code below) ..Range("CB 01").Value = UserForm1.Office_Package_Preparations_101.Value End With End Sub When I removed the UserForm1 Ref from the line of code I get a run time 424 object required. This code is located in a module, not in the User Form Code Window. "JLGWhiz" wrote: Hi Brian, this is probably your problem, but without some explanation I can't be sure. UserForm1("Office_Package_Preparations_101").Value It looks like a comination of a UserForm code name and a string name. If UserForm1 is named "Office_Package_Preparations_101" then you should not need the UserForm1 as part of the reference. If it is a control on the userform then you need a decimal point after UserForm1 and remove the Parentheses and quotemarks. What is the item that you are trying to return the value of? "Brian" wrote in message ... I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
That fixed it!!! It works great and is exactly what I wanted it to do.
Thanks for your help It was the first set of code that did it. I change .range to .checkboxes "Dave Peterson" wrote: What goes wrong when you run this? Do you really have a range (a cell) named "Check Box 01"? If you have checkboxes from the Forms toolbar: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkboxes("check box 01").value _ = UserForm1("Office_Package_Preparations_101").Value end with If you have checkboxes from the Control toolbox toolbar, then I'd rename them to remove the space characters: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkbox01.value = UserForm1("Office_Package_Preparations_101").Value end with Or without renaming the checkbox: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .OLEObjects("check box 01").Object.Value = Userform1("checkbox1").Value End With Brian wrote: I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub -- Dave Peterson . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
Hi Brian, it was the syntax that threw me off. I am not familiar with
Access VBA syntax. Glad you figured it out. "Brian" wrote in message ... That fixed it!!! It works great and is exactly what I wanted it to do. Thanks for your help It was the first set of code that did it. I change .range to .checkboxes "Dave Peterson" wrote: What goes wrong when you run this? Do you really have a range (a cell) named "Check Box 01"? If you have checkboxes from the Forms toolbar: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkboxes("check box 01").value _ = UserForm1("Office_Package_Preparations_101").Value end with If you have checkboxes from the Control toolbox toolbar, then I'd rename them to remove the space characters: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkbox01.value = UserForm1("Office_Package_Preparations_101").Value end with Or without renaming the checkbox: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .OLEObjects("check box 01").Object.Value = Userform1("checkbox1").Value End With Brian wrote: I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Check Box in User Form to Workbook
I'm not really sure it's Access syntax. But the post where I first saw it was
from an Access user. I think I may have "corrected" that poster, too <vbg. JLGWhiz wrote: Hi Brian, it was the syntax that threw me off. I am not familiar with Access VBA syntax. Glad you figured it out. "Brian" wrote in message ... That fixed it!!! It works great and is exactly what I wanted it to do. Thanks for your help It was the first set of code that did it. I change .range to .checkboxes "Dave Peterson" wrote: What goes wrong when you run this? Do you really have a range (a cell) named "Check Box 01"? If you have checkboxes from the Forms toolbar: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkboxes("check box 01").value _ = UserForm1("Office_Package_Preparations_101").Value end with If you have checkboxes from the Control toolbox toolbar, then I'd rename them to remove the space characters: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .checkbox01.value = UserForm1("Office_Package_Preparations_101").Value end with Or without renaming the checkbox: With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .OLEObjects("check box 01").Object.Value = Userform1("checkbox1").Value End With Brian wrote: I have a user Form with several Check Boxes in it. I would like for the Check Boxes in the User Form to Check or Uncheck boxes in a different Workbook worksheet. I tried the following code, but for some reason it does not work. Can anyone please help me? Sub Update_Forms1() With Workbooks("Forms.xlsm").Sheets("Install Pack Con") .Range("Check Box 01").Value = UserForm1("Office_Package_Preparations_101").Value .Range("Check Box 02").Value = UserForm1("Office_Package_Preparations_102").Value .Range("Check Box 03").Value = UserForm1("Office_Package_Preparations_103").Value .Range("Check Box 04").Value = UserForm1("Office_Package_Preparations_104").Value End With End Sub -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Check Boxes in User Form does not change worksheet | Excel Programming | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
user form check box vba | Excel Programming | |||
Check box in user form | Excel Programming | |||
Check For Open Workbook by another User | Excel Programming |