Checkbox - Sheet. to variable.

I have been using

Sheet1.checkbox1

to get the status of checkbox1. Now I have assigned a variable to sheet1

Set ctrl = xls.ThisWorkbook.Sheets("Control")

but now

ctrl.checkbox1

does not work. How can I get it to work using the variable sheet name?
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Presumably...

a) "xls" is an "Excel.Application" object created in the automation process where your code resides.

b) "checkbox1" is an embedded ActiveX Control that displays =EMBED("Forms.CheckBox.1","") within the MS-Excel Formula Bar.

Has your worksheet changed name from "Sheet1" to "Control" as your question text suggests?


See if replacing Sheets("Control") with one of either...

Worksheets("Sheet1").OLEObjects("checkbox1").Object.Value
or
Worksheets("Control").OLEObjects("checkbox1").Object.Value

produces the result you require.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Very good question. :)

I think this should work...

MsgBox Sheets("Control").CheckBox1.Value

Open in new window

0
Ryan ChongCommented:
or try:
Dim ctrl As Object
    Set ctrl = ActiveSheet.Shapes("Check Box 1")
    MsgBox ctrl.OLEFormat.Object.Value

Open in new window

if value = 1 means the checkbox is checked.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Ryan ChongCommented:
just found that this will work too:
Dim ctrl As Object
    Set ctrl = ActiveSheet.Shapes("Check Box 1")
    MsgBox ctrl.ControlFormat.Value

Open in new window

0
Saqib Husain, SyedEngineerAuthor Commented:
Here is my sanitized file. It will answer all questions.

The first set of assignments works but the second set does not. The second set must use the variable ctrl.
Var.xls
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Ryan

As far as I have understood, Saqib's question is he has declared Ctrl as worksheet and set Ctrl to Sheets("Control") and now he is trying to access the checkbox by using Ctrl.CheckBox1 and which is producing error while if he doesn't declare a sheet variable and just uses Sheet1.CheckBox1, that works.
0
[ fanpages ]IT Services ConsultantCommented:
...The first set of assignments works but the second set does not. The second set must use the variable ctrl.

plotplan = ctrl.OLEObjects("CheckBox1").objects.Value

Should read

plotplan = ctrl.OLEObjects("CheckBox1").Object.Value
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
yup... also found that, it should be as ctrl.OLEObjects("CheckBox1").Object.Value instead.

(no points)
0
Saqib Husain, SyedEngineerAuthor Commented:
Perfect, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.