Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1503
  • Last Modified:

CHECKBOXES in Word and Excel VBA

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
jm8540
Asked:
jm8540
  • 7
  • 5
  • 2
1 Solution
 
lvngstnCommented:
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
 
MoliereCommented:
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
 
MoliereCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jm8540Author Commented:
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
 
lvngstnCommented:
Can you post your code and tell me what line you're getting the error on?
0
 
jm8540Author Commented:
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
 
jm8540Author Commented:
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
 
lvngstnCommented:
if that does work then what's stopping you from calling your original doThis/doThat subroutines?

lvngstn
0
 
jm8540Author Commented:
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
 
jm8540Author Commented:
Still hoping for additional suggestions - - -
jm8540
<<>>
0
 
lvngstnCommented:
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
 
jm8540Author Commented:
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
 
lvngstnCommented:
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
 
jm8540Author Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now