Linking Check Box in User Form to Workbook

[Brian]

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

 

[JLGWhiz]

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?

 

[JLGWhiz]

Re-reading your post, I believe that this would be the correct syntax for
the UserForm1 items.

UserForm1.Office_Package_Preparations_101.Value

 

[Dave Peterson]

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).

 

[Dave Peterson]

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]

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.

 

[Brian]

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

 

[JLGWhiz]

Hi Brian, it was the syntax that threw me off. I am not familiar with
Access VBA syntax. Glad you figured it out.

 

[Dave Peterson]

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>.