Populate Checkbox Based on Cell Content

[Mort]

I know enough about Excel VBA to shoot myself in the foot and get lost in the woods so be merciful...

I have an Excel spreadsheet with a forms drop-down on sheet 1 that contains employee names (3 total). On sheet 2 I have a forms checkbox for each employee. What I want to do is have the corresponding checkbox "checked" for the employee's name chosen from the drop-down on sheet1. In other words, if I choose Bob from the drop-down on sheet 1 I want the checkbox for Bob on sheet 2 "checked."

The drop-down is over cell C4 and I have the drop-down populate cell C4 with the employee's name chosen from the drop-down. I have tried all kinds of Case, If, etc referencing cell C4's value but have not found the right command to check the checkbox. :banghead:

Please help!!!

Thanks and be blessed,
Mort

[Philcjr]

Hey Mort,

Any chance of you uploading your file so we can see what you are doing.

Phil

[malik641]

Mort,
First off, welcome to the Forum!! :thumb

Second...I got your answer...After trying to figure it out for like an hour between other things at work, I did a little Google-ing and I think I have what you need.

I made a list of Employee names (five) and named the list "Employee". I set the combo box to display those values on sheet 1. I placed five check boxes in sheet 2, each with an employee name. I named Bob's checkbox BobBox (just so you know to read the code easier).

Check it out:

Private Sub EmpBox_DropButtonClick()
Dim Lst As Variant
Lst = ThisWorkbook.Sheets("Sheet1").Range("Employee")
EmpBox.List = Lst
End Sub

Private Sub EmpBox_Change()
Dim Bob As Object
Set Bob = Worksheets("Sheet2").OLEObjects("BobBox")
If EmpBox.Value = "Bob" Then Bob.Object.Value = 1
End Sub

Hope this is what you were looking for!
:thumb

and...

Any chance of you uploading your file so we can see what you are doing. Yeah. If you want mort we could help you out a little easier if you upload a file for us :thumb

[Mort]

Philcjr and malik641,

Thanks for the responses. I guess I am like some others in that I asked a question without providing all the helpful information, like the worksheet. It is a pricing/profit worksheet and fairly busy so I have tried not to send it.

I did try a sample sheet like malik641 suggested and thought I had followed all the right steps but the sample won't work for me. I have attached what I did so you can see where I screwed up. I am not ashamed of screwing up just ashamed if I don't try.

I am running MS Excel 2002 (10.6501.6735) SP3

Again, thanks for your help.

Be blessed,
Mort :bow:

[malik641]

Mort,
The reason why it's not working because the check boxes you used to create this are not OLEObjects...and I'm not sure why (not TOO familiar with objects and object manipulation)

Still trying to figure it out for ya...:think:

[malik641]

Hey Mort,
This works, but I don't like it too much...:think:

Here's the code:

Private Sub EmpBox_Change()
Dim Bob as Shape
Dim EmpBox As Shape

Set EmpBox = Worksheets("Sheet1").Shapes("EmpBox")
Set Bob = Worksheets("Sheet2").Shapes("BobBox")

If EmpBox.OLEFormat.Object.Value = 1 Then 'Value of 1 is the First Employee name in Employee Range
Bob.OLEFormat.Object = True
Else
Bob.OLEFormat.Object = False
End If
End Sub
I don't like how you have to say "If EmpBox.OLEFormat.Object.Value = 1" instead of "If EmpBox.OLEFormat.Object.Value = "bob" "....and I can't figure out a way to read text and not numeric values (which 1 = "bob" and 2 = "sam" ...in your file)

Still working on it :think:


EDIT: If you want my original code to work, you should use the Control Toolbox toolbar for the Combo Box and the Check Boxes instead of Shapes

[Mort]

This works!!!! You are great!!! Can you recommend a good book related to Excel and VBA that explains well and gives good examples?

Be blessed,

[malik641]

This works!!!! You are great!!! Can you recommend a good book related to Excel and VBA that explains well and gives good examples?

Be blessed,Thanks Mort http://vbaexpress.com/forum/images/smilies/023.gif Glad to help

Because I work with Excel 2000 at my job, I bought 2 Excel 2000 books:
Microsoft? Excel 2000 Power Programming with VBA
Excel 2000 Formulas

Both books by John Walkenbach. Excellent read. Found them at Amazon.com for CHEAP!!!

And just so you know, my post about the shapes I looked on the internet for. The VBA book I bought didn't explain shapes and shape manipulation much (at all, actually)...but still an excellent book http://vbaexpress.com/forum/images/smilies/023.gif