Control arrays and optionbuttons, how do they work? 2

[neiljabba]

Hello all
I have a small problem. I am working in word on a form for users to fill in , almost like a 1 to 3 kind of questionaire, where they select only one option. Unfortunately I need to identify which one has been checked in order to give me the total for the selections i.e. overall opinion.

There are 3 options on each row and each row is named Row1, Row2, Row3 etc. I had orginally tried appending a variable in order to count through certain option buttons e.g.

Public Sub Done_Click()



    For j = 1 To 3 ' boxes

        If SCheckH & j.Value = True Then MsgBox "Hello" Else MsgBox "No"

    Next j

End Sub

However this did not work. How can I solve this, someone mentioned control arrays, but I dont know how to use them/set them up so I cant loop through them. Also when I have selected the right control how do I identify which one it is in order to add the answers for a total.

Many thanks in advance.

Neil

 

[fumei]

Are these option buttons on a UserForm, or ActiveX Controls in the document? As you have this sub as "Done_Click" I will assume this is a UserForm. Although it is possible that you are using an ActiveX Control button right in the document.

You identify controls, for one thing, by giving them explicit names.

I also notice you are not declaring your variables. Generally speaking, it is a good idea to do so.

This could be handled a couple of ways. Please state if these are controls on a userform, or controls in the document.

Gerry

 

[HughLerwill]

neiljabba,

I'm talking VB6 and not VBA here but you may find;

Checkboxes have values of 0 = unchecked 1 = checked and 2 = grayed

Try testing for 1 rather than True(-1)

regards Hugh

 

[HughLerwill]

If you are using CheckBoxes rather than OptionButtons

 

[PHV]

Replace this:
If SCheckH & j.Value
By this:
If Me("SCheckH" & j).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neiljabba]

Cheers guys

To clarify
1. Gerry they are embedded on the word doc as Active X controls.
2. PHV I have tried the Me option, which has worked on UserFroms, but got problems with not supported or object required error. Granted I did
Me.("SCheck" & j) not just Me("SCheck" & j). Hopefully the removal of the dot prompting an object will help or is this a syntax/typo error?

Anyway if any of this changes any suggeestions, please let me know as I am very grateful for your help.

Off to try Me without the dot. Cheers

Neil

 

[neiljabba]

Hello again

Still getting the error using the Me command. I know this works on Userforms even Me.Controls but not in this instance using the embedded controls in the document.

This is a little frustrating as I have used the Me method alot.

Any more ideas?

Many thanks in advance.

Neil

 

[PHV]

Sorry, Me is for UserForm.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neiljabba]

Cheers PH

There in lies the problem these optionbuttons are embedded in the document.

Any other ideas

Cheers
Neil

 

[fumei]

This can be fixed.

#1. ActiveX control IN a Word document can only be actioned with procedures in the ThisDocument module. You can write procedures in other modules, but they have to call procedures in ThisDocument. ActiveX controls are specific to the the document they are in - hence the requirement for event procedures in ThisDocument.

#2. If you only have three, I would not bother with an array. I mean you could, but...

I hope you have each Option button explicitly named. It makes things easier that way.

OK, you need to know which one is "ON" (or True really)?

#1. Do you want to make the others False if one is True? Or do you to be able to have more than one True?

#2. Do you have OTHER ActiveX controls, or other shapes (ActiveX control are part of the InLineShapes collection) anywhere in your document? If yes, it makes it a little more difficult. If no, it makes it a lot easier.

#3. If there are other ActiveX controls, can you clearly determine the order the ones you want to get values from?

EXAMPLE: if there are only the 3

Dim mInlineShape As InlineShape
For each mInlineShape in ActiveDocument.InlineShapes()
If mInlineShape.Value = True Then
' that is....it is ON
' ---do whatever it is that measn for you
End If
Next

Regarding having only one be able to be selected, use the change event

Sub OptionButton1_Click()
If OptionButton1.Value = True Then
    OptionButton2.Value = False
    OptionButton3.Value = False
End If
End Sub

To get the values into an array:

Sub TestArray()
Dim ValueArray(2) As Boolean
ValueArray(0) = OptionButton1.Value
ValueArray(1) = OptionButton2.Value
ValueArray(2) = OptionButton3.Value

MsgBox ValueArray(0) & vbCrLf & _
        ValueArray(1) & vbCrLf & _
        ValueArray(2)
End Sub

There are quite a number of things you can do with these controls, but it depends on your needs. In paryicular is question #1.

Gerry

 

[TonyJollans]

Hi Neil,

I think the answer to your question is that there isn't a collection which can be referenced by a name in a variable; you have to hard code the names of ActiveX controls in a document (unless you're going to generate code on the fly).

As Gerry says, they are members of the InlineShapes collection but they can only be referenced by item number.

If you have many groups of buttons it might be worth your while writing a loop which would either check for the ones you want or build them into your own collection or something similar - the names are in InlineShapes(number).OLEFormat.Object.Name (I think).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

Tony is incorrect. It is true an Activex control are generated as an InlineShape, and it is true that they can only be referenced by index number.

HOWEVER, that is only true when the reference is in a module other than ThisDocument. If you right click an ActiveX control and select properties, and give it an explicit name (eg. "OptionYes" for an option button, "txtLastName" for a textbox...without the "" of course), you can refer to it and its properties, BY NAME, in any procedure in the ThisDocument module.

Gerry

 

[TonyJollans]

I don't disagree at all, Gerry. What I meant was that you cannot refer to them as, say, ThisDocument.InlineShapes("OptionButton1"), which mechanism would allow you to use a variable for the name (or part of the name) as Neil posted in his original question. The only option is to hard code the name with, say, ThisDocument.OptionButton1. At least, as far as I know, it is (apart from, as I said, generating code on the fly)- but I've been wrong before.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[neiljabba]

Cheers guys.

The example code was just that and unfortunately the number of rows and consequently the final total of optionbuttons overall will change from document to document, up to 10 different documents, up to 16 if not more rows eventually on some documents. Having the variable option would alleviate the need for different codes for each document and the size of it!!!

I am currently working in ThisDocument, but havent tried InLineShapes. As for my needs they can only check one out of the group hence the optionbutton choice. Each option is named completely uniquely and fits within a group that indicates the row, e.g. Row1, Row2 etc.

Thanks alot for your help guys, but unless I can select by ("Name" & variable) then I need to code them all. Ill give InlIneShapes a try, but I am currently doing it the long way round in order to stop my head hitting this wall.

Ill let you know how it goes, what I cant understand is how the same code option isnt "allowed" to work in both scenarios, UserForms and as ActiveX.

Have some stars, maybe someone else out there can avoid this crazy problem.

Thanks again.

Neil

 

[fumei]

OK, further on down the road. Tony, in the ThisDocument module you can refer to them WITHOUT using any reference to InlineShapes. With the ThisDocument module, and ONLY within that module, Active controls have direct reference. They are all listed in the object dropdown, and all their events are listed in the event dropdown.

In other words, within any procedure in ThisDocument, you can just use:

OptionButton1.whatever property or method

That is if you have no changed the default name. In other modules; ThisDocument.OptionButton1 will not work.

Which is why, as I posted, if you want to action ActiveX controls, put the procedures in ThisDocument - so you can access the object and properties/methods directly - then CALL those procedures from anywhere you like.

Back to the original post....

You can make an array of ActiveX controls, both their names and their values. The number of controls is irrelevant as it just loops through them all. So it does not matter if you have 16 or 43.

As noted, Option buttons can only have one selected, but the following can certainly be altered to build array of any ActiveX control names and values.

Sub ControlArrays()

Dim myOption As InlineShape
Dim OptionControlValue() As String
Dim OptionControlName() As String
Dim i As Integer
Dim msg As String
Dim var

For Each myOption In ActiveDocument.InlineShapes
  If myOption.OLEFormat.ClassType = _
      "Forms.OptionButton.1" Then
    ReDim Preserve OptionControlName(i)
    OptionControlName(i) = myOption.OLEFormat.Object.Name
        
    If myOption.OLEFormat.Object.Value = True Then
      ReDim Preserve OptionControlValue(i)
      OptionControlValue(i) = "  = True"
    Else
      ReDim Preserve OptionControlValue(i)
      OptionControlValue(i) = "  = False"
    End If
'  Could put an Else with a Select Case
'  to check other .ClassTypes to list
  End If
  i = i + 1
Next

i = 0
For var = 0 To UBound(OptionControlValue)
    msg = msg & OptionControlName(i) & "  " & _
        OptionControlValue(i) & vbCrLf
    i = i + 1
Next
' NOTE: there will be a blank line for each
' a found control is NOT .ClassType Option
' say a ActiveX textbox is present, there will be a blank line
' see note re: adding an Else with Select Case above
MsgBox msg
End Sub

This just displays a message listing all controls (if they are option buttons) and their value, either True or False.

Gerry

 

[fumei]

Sorry, I guess I should have also mentioned that your request for "Name" & variable is easily adjusted into the above. It all depends on:

a) what is your variable? A count of the control? The order of the control within the document? Whatever the criteria, it can be worked into the code.

b) how you are naming the controls.

In any case, with thoughtful, and explicit, naming of the controls and determination of how your variable is to work, yup, you can pretty much do what you want.

Gerry

 

[TonyJollans]

Gerry,

ThisDocument.OptionButton1, and variations on the theme, all work just fine for me.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

Hi Tony, Crap, I must have been writing some syntax blah poop crap - darned, sticking my neck out because I did not functioncorrectlyidiotlazy...in other words...sigh, time for a break again as I am seemed to become more ranting and yet stupid again.

You are right of course.

Gerry

 

[neiljabba]

Thanks again for continuing to look at this. Ill try the posted code see what I get and then customise.

Ill let you know the outcome and post when I complete it.

Cheers

Neil