?
Solved

CHECKBOXES in Word and Excel VBA

Posted on 2003-03-21
14
Medium Priority
?
1,431 Views
Last Modified: 2008-02-01
CHECKBOXES in Word and Excel VBA

Hello to All -

I'm trying to complete a project using MS Office (2000/2002) but can't seem to figure out how to perform the following tasks.
Any help you may be able to provide will be greatly appreciated.

Thanks,
jm8540
_________________________

[Control Checkboxes in Excel cells]
In Microsoft Excel: How to pass a checkbox as an argument to a Sub or Function

I have tried passing ByVal as well as ByRef, but can't seem to figure out how this works.
Errors include "Object required" and "ByRef mismatch"...

now:

Private Sub box1_Click()
  doSomethingWithBox1
End Sub
_________________________

Private Sub box2_Click()
  doSomethingWithBox2
End Sub
_________________________

Sub doSomethingWithBox1 ()
  If CheckBox1.Value = True Then
    CheckBox1.Enabled = False
  End If
End Sub
_________________________

Sub doSomethingWithBox2 ()
  If CheckBox2.Value = True Then
    CheckBox2.Enabled = False
  End If
End Sub
_________________________
_________________________

would like it to be:

Private Sub box1_Click()
  doSomething  box1
End Sub
_________________________

Private Sub box2_Click()
  doSomething  box2
End Sub
_________________________

Sub doSomething(cb As CheckBox)
  If cb.Value = True Then
    cb.Enabled = False
  End If
End Sub
_________________________
_________________________


[Control Checkbox in Word Table cell]
In Microsoft Word: How to access checkbox activity in a new document created from a template
_________________________

Function isChecked() As Boolean
  isChecked = (ThisDocument.CheckBox1.Value = True)
End Function
_________________________

Sub TestForCheck()
  If isChecked Then
    doThis
  Else: doThat
  End If
End Sub
_________________________

This works in the original template, but not in a document created from that template.
The checkbox itself seems to be functioning normally in both document and template.

Thanks again.
<<>>
0
Comment
Question by:jm8540
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 2

Accepted Solution

by:
lvngstn earned 800 total points
ID: 8181842
In Excel, all ActiveX controls are all contained on the OLEObject class.  In order to pass the objects by reference you must use the OLEObject "get" method, similar to every other container class "get" method in Excel. For example:

Private Sub CheckBox1_Click()
  IsChecked OLEObjects(Me.CheckBox1.Name)
End Sub

Sub IsChecked(ByVal oOle As OLEObject)
  If TypeName(oOle.Object) = "CheckBox" And oOle.Object.Value = True Then
    MsgBox oOle.Name & "is true"
  End If
End Sub

Make sure that you always test the type of an OLEObject in a generic object sub.  Using its value without knowing what type it is can lead you to unexpected results.

I'm still working on the word thing...

lvngstn
0
 
LVL 3

Expert Comment

by:Moliere
ID: 8182618
For Excel, try the following

Private Sub box1_Click()
 doSomething  me.box1
End Sub
_________________________

Private Sub box2_Click()
 doSomething  me.box2
End Sub
_________________________

Sub doSomething(cb As CheckBox)
 If cb.Value Then cb.Enabled = False
 End If
End Sub

--------------------------
--------------------------

As for Word, try the following:

Function isChecked() As Boolean
 isChecked = (Me.CheckBox1.Value = True)
End Function

Also, make sure the name CheckBox1 is constant...
0
 
LVL 3

Expert Comment

by:Moliere
ID: 8182636
For the Word part, you can optimize it using the following:

Function isChecked() As Boolean
 isChecked = (Me.CheckBox1.Value)
End Function

Also,make sure that the checkbox can only be checked/unchecked, so there is no third state.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Author Comment

by:jm8540
ID: 8186511
Thanks for the replies -
So far I still can't get anything to work, here are the results of my tests for each suggestion:

The Excel suggestion from lvngstn seems to make perfect sense but I can't make it run --

Compile Error:
User-defined type not defined

In either Excel 2000 or 2002 I do not have a Reference to an "OLEObject" library. Selecting several other References did not change the error.

The suggestions from Moliere were something I'd already tried but I built the tests again using his exact code.
In the Excel routines I get:

Run-time error '13'
type mismatch

In Word the code runs -- but again, when I create a new document from the template it does not work. No error messages, but whether or not the box is checked the test returns False.

I hope there is more to come, and thank you for your help.
jm8540
<<>>
0
 
LVL 2

Expert Comment

by:lvngstn
ID: 8187104
Can you post your code and tell me what line you're getting the error on?
0
 
LVL 1

Author Comment

by:jm8540
ID: 8188195
Hello again, lvngstn -

Can't explain it, but after posting my last message this morning I had to run some errands, so...turned off this PC, went out, came back a few minutes ago, turned on the PC, ran the same code and now it works; no Compile Errors...gremlins?

Anyway, thank you for this information; if you don't mind I'm going to wait a little while and see how these points should be distributed -- I may need to increase in order to show my appreciation.

Thanks again,
jm8540
<<>>
0
 
LVL 1

Author Comment

by:jm8540
ID: 8190077
Regarding my Word Checkbox problem

Right now I'm working around my inability to retrieve the checkbox status by polling the checkbox directly:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
  setValue "Accept"
Else: setValue "Decline"
End If
End Sub

This does work in both template and new document, but I still am interested in solving my problem as originally stated.

Thanks,
jm8540
<<>>
0
 
LVL 2

Expert Comment

by:lvngstn
ID: 8191421
if that does work then what's stopping you from calling your original doThis/doThat subroutines?

lvngstn
0
 
LVL 1

Author Comment

by:jm8540
ID: 8191736
That's a good point lvngstn, but I'm sure you recognize that there is a difference.
My original plan was to poll the checkbox status to determine which one of two values to store in an Excel worksheet when the Word document data is ready.

Because I cannot poll the checkbox status I am instead storing the value in Word when the check event occurs, and then retrieve it later when I need to put it in Excel.
Not a big deal, but it would still be better if I could simply test the checkbox state and go on from there.

Thanks again for you help and time,
jm8540
<<>>
0
 
LVL 1

Author Comment

by:jm8540
ID: 8210812
Still hoping for additional suggestions - - -
jm8540
<<>>
0
 
LVL 2

Expert Comment

by:lvngstn
ID: 8211237
Well, if you want to poll the checkboxes in word, its a sligtly different story than in excel.  I don't believe that there's a holding cell for all the checkbox objects, so I think that you have to call them out explicitely.  This could become very cumbersome if you have more than a few.

You can accomplish this one of two ways:

1) Code inside the document object
Sub PollCheckboxes()
  If Me.CheckBox1.Value = True Then MsgBox "1 is true"
  If Me.CheckBox2.Value = True Then MsgBox "2 is true"
  If Me.CheckBox3.Value = True Then MsgBox "3 is true"
End Sub

2) Code in a module that refers to the document
Sub PollCheckboxes2()
  If ThisDocument.CheckBox1.Value = True Then MsgBox "1 is true"
  If ThisDocument.CheckBox2.Value = True Then MsgBox "2 is true"
  If ThisDocument.CheckBox3.Value = True Then MsgBox "3 is true"
End Sub

This way there are no extra values stored and you can refer to them at any time, not just when a value is changed.  You could, though, trigger the PollCheckboxes sub on the change of a checkbox, but that's your call...

lvngstn
0
 
LVL 1

Author Comment

by:jm8540
ID: 8211472
Hi lvngstn -

Thanks for the information.
My problem continues to be that I cannot retrieve the state of a control checkbox from a new document created from a template.

In the template I can poll/test in any way I wish; in the new document I cannot. That's the gist of my problem -- Me.* or ThisDocument.* or ActiveDocument.* don't seem to work.

Regards,
jm8540
<<>>

PS - I'm not sure, but this may be complicated by the fact that the control checkbox is in a Word table cell.
0
 
LVL 2

Expert Comment

by:lvngstn
ID: 8212410
The docs that are created by the template:  do they contain the same code that was originally inside your template document?  Are you putting that code in manually or are you relying on the template to do the same?  I'm not really that familiar with how a template creates itself from the original, but is there a reference that can be passed back to the original document on the new document created event?  That way you can refer to the new document through that object, instead of trying (and failing) to reach the new doc through the ThisDocument call.  

I've pretty much exhausted most of my ideas on the Word portion of your question, John.  If what we have here doesn't solve your question entirely, you may want to ask CS to reduce the points of this question (leaving only the points for the Excel portion), close this question, and open a new question regarding just the Word checkboxes.  You will certainly grab more attention to the problem if you created a new question, pertaining only to that topic.  I'm just looking to help you get the right answer...

lvngstn
0
 
LVL 1

Author Comment

by:jm8540
ID: 8225041
Thanks again lvngstn for your help and your time -

I'm going to learn more about file dependencies and project references, which seem to underly my questions.

Regards,
jm8540
<<>>
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question