Type Mismatch with Set instruction

[Middlemarch]

I'm wanting to use this to get the caption of the current Frame item

Dim C As MSForms.Control
  Set C = Me.ActiveControl
  If TypeOf C Is MSForms.Frame Then Set C = C.ActiveControl
  Me.Tag = C.Caption

But it's not the active control. It's a frame called "frmSheet" I've tried various combinations but all result in Type Mismatch error.


What would be the correct syntax for Set C= Me.frmSheet

[Micron]

Forgetting any code changes to that for the moment, what is the "caption of the current frame item"?

[Middlemarch]

Does the text matter ? I'm still to finalise that. The Frame contains 5 radio buttons with captions and one will (always) be selected

[Micron]

It matters if I don't understand the statement. AFAIK, an option group can only accept numbers, so there's that.
Or maybe you want the caption of the label. Or maybe the name of the control that is selected. So if option buttons in a group can't use text for values, then I have no idea what the caption thing is all about.

The only way I know of to determine which option in a group is selected is to compare the frame value to the known option value, which is an indirect method but should work. Once you know the selected option control you can get the label caption if that's what you're looking for.

[orange]

Can you post a graphic of the "frame" involved? And provide more detail on the Caption you are trying to identify and record in the form's Tag property. Maybe tell us why you are doing this for context.

[Middlemarch]

Sure, image attached. The caption text may change but surely no matter
The problem line is Set C = Me.ActiveControl and the question was how amend that to work with my Frame
The code is how originally written and found online. I won't be using the Tag.

[Micron]

Seems that the "caption" is the label caption. If the label is attached, consider the likes of

Select Case Frame0
     Case 2
          MsgBox "label caption is " & Me.Option2.Controls(0).Caption
     Case 4
          MsgBox "label caption is " & Me.Option4.Controls(0).Caption
     Case 6
          MsgBox "label caption is " & Me.Option6.Controls(0).Caption
End Select

where the option values are 2, 4 and 6

[orange]

For what it's worth (or not), I took a form I had with an OptionGroup

and some code to print the "option captions".



This png shows some of the properties. The options and captions in the optionGroup/Frame are all labels.
The caption you're seeking seems to be a label caption.


The code:

Private Sub Form_Load()
    Dim c As Control, opt As Control
    For Each c In Me.Controls

        If TypeOf c Is Access.OptionGroup Then
            For Each opt In c.Controls
                If TypeOf opt Is Access.Label Then
                    Debug.Print opt.name & " " & opt.Caption
                End If
            Next opt
        End If
    Next c
End Sub

The result:

Label5 Dates
Label8 DateCreated
Label10 DateModified
Label12 DateLastAccessed

[Gicu]

@Middlemarch: I see you declare your control as MSForms.Control and use

If TypeOf C Is MSForms.Frame

. Is this in Excel?

Cheers,

[Middlemarch]

Thanks all for help and examples, but I must have worded the question badly. I'm already reading the caption with

 = Switch(frmSheet = 1, "A", frmSheet = 2, "XP", frmSheet = 3, "LP", frmSheet = 4, "B", frmSheet = 5, "19")

What I was after was how to see if the instruction Set C = Me.ActiveControd could be modified towork (for my Framel), or not. @Gici the code in Msg1 was shown as for Access (not Excel) and found on Google.

[Gicu]

But it is not for Access but for Excel....

[Micron]

I'm wanting to use this to get the caption of the current Frame item

I still think the method is as per post 7 except you'd use Case Me.ActiveControl. Not understanding the point of the exercise though, but that's OK.

[Micron]

But it is not for Access but for Excel....

Microsoft Forms 2.0 controls are for use in Access as well. See the list of ActiveX controls in the extended toolbox. I have never used them in Access.

[June7]

I've never used ActiveX controls in a production db but have played with some. Just today tried a Listview, unsatisfactorily. From another Middlemarch thread https://www.accessforums.net/showthread.php?t=87344

I don't see a MSForms listed in ActiveX controls. I do see Microsoft Forms 2.0 Frame and other controls.

[Gicu]

@June: yes, those are the ones, you can qualify them in VBA with MSForms.Frame and so on. Never used them as the Access built-in controls (in this case the option group) were satisfactory for my needs.

Cheers,