Multipage controls - proper reference syntax when calling procedure.

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
I have labels on both a userform and on a multipage control (6 pages) that I have tied into a class. It's a simple task that when you hover over any of these controls that the backcolor goes vbgreen.

This technique works when the label is one the userform but throws and error "Object doesn't support this property or method" when you hover over the labels on the multipage. Note: I only want this 'vbgreen' behaviour on the multipage controls. I only used the userform labels to check the code - and it works for the userform labels. This leads me to think I have the control reference wrong.

The error is being thrown when I try to access a public procedure back on the userform.

Userform
VBA Code:
Option Explicit
'userform module varibles
Private AdjustVar() As New AdjustVarClass

Private Sub UserForm_Initialize()
Call PopulateControlsArray
End Sub

Private Sub PopulateControlsArray()
Dim ctrl As Control
Dim lControlCount As Long
For Each ctrl In Me.Controls
If ctrl.Tag = "Adjust" Then
lControlCount = lControlCount + 1
ReDim Preserve AdjustVar(1 To lControlCount)
Set AdjustVar(lControlCount).AdjustVarGroup = ctrl
Set AdjustVar(lControlCount).Parent = Me
End If
Next ctrl
End Sub

Public Sub ResetColor()
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.Tag = "Adjust" Then
ctrl.BackColor = vbWhite
End If
Next ctrl
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Call ResetColor
End Sub

Class Code

VBA Code:
Option Explicit

Public WithEvents AdjustVarGroup As MSForms.Label

Private mfrmParent As Object
'--public properties
Public Property Set Parent(frmParent As Object)
Set mfrmParent = frmParent
End Property

'--event procedures
Private Sub AdjustVarGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'This clears resets all the color
Call AdjustVarGroup.Parent.ResetColor '<---- ERROR OCCURS HERE!
'After everything is cleared, this changes the color
AdjustVarGroup.BackColor = vbGreen
End Sub

Sorry for the identation, don't know how to paste it with proper formatting.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
In your AdjustVarClass module, the AdjustVarGroup is the placeholder for each individual label, and not for the class itself.
The labels on your userform do have a parent property, namely the userform, and that parent does have a method called ResetColor, so your code runs as expected.
The labels on the multipage also do have a parent property, namely the multipage, but the multipage lacks the ResetColor method, so an error is thrown.
At this point I hear you thinking: "my class takes care of that" but actually it doesn't, due to the "cripple" Parent property, as half exposed. Its value could be written bot not read.

Rich (BB code):
Option Explicit

Public WithEvents AdjustVarGroup As MSForms.Label

Private mfrmParent As Object
'--public properties
Public Property Set Parent(frmParent As Object)
    Set mfrmParent = frmParent
End Property

Public Property Get Parent()
    Set Parent = mfrmParent
End Property

'--event procedures
Private Sub AdjustVarGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'This clears resets all the color

'    Call AdjustVarGroup.Parent.ResetColor '<---- ERROR OCCURS HERE!
    Call Me.Parent.ResetColor
  
    'After everything is cleared, this changes the color
    AdjustVarGroup.BackColor = vbGreen
End Sub
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Ahhh - I see the distinction. I thought it was going to be about how the control is referenced. Thanks for the clarification and fix.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Regarding my previous post I would like to add some comments, since it's a textbook example of a QAD** solution. Why? Because the use of names does not cover the meaning and the code could put you on the wrong track at a later stage. I wouldn't want to burden you with that.

The custom Parent property procedure from your post #1, extended by me in the post #2 code, may give the impression that it is the parent of each individual Active-X control represented by that class, but in fact it is not. Actually it is a way to expose the public methods and properties of your Userform class in general, and in your scenario to expose the ResetColor method. I would prefer to expose those procedures in a different way (with a different procedure name), instead of packaging it as a property whose name does not actually represent that property. Besides that the property is implemented with a read/write possibility, which originally is ment to be read-only (after all you cannot switch Parent); btw, in your version it was even the other way around, the "parent" could only be assigned, not read.

Although the code as in post #2 runs without throwing errors (it therefore doesn't hurt in that way) I want to offer you a better alternative.

Explanation / the most striking differences:
- Both the class module and the With Events variabele is given a more appropriate name;
- The PopulateControlsArray procedure within the Userform has been changed accordingly;
- I've dropped the Parent property, an Init method takes its place. Although most classes may offer an _Initialize event procedure (your custom class could do that too) it lacks the ability to pass arguments to that event when it's triggered, so we have to implement such an ability ourselves;
- Within the MouseMove event procedure, the ResetColor procedure is called directly and no longer in an indirect (and kind of obscure) way;
- I've added a ResetBackColor method to the class to demonstrate an alternative way in accessing "native" properties of the involved Active-X control;
- Finally, within the Userform I modified the ResetColor procedure in a way that only the Label controls are checked for the presence of a text filled Tag, instead of examining all controls present on the userform.

As mentioned, these changes do not change the effect of the code, but imo the code creates a little more clarity.

**quick and dirty

Userform module:
VBA Code:
Option Explicit

Private aryLabels() As New Class_LabelGroup

Private Sub UserForm_Initialize()
    Call PopulateControlsArray
End Sub

Private Sub PopulateControlsArray()
    Dim ctrl As Control
    Dim lControlCount As Long

    For Each ctrl In Me.Controls
        If ctrl.Tag = "Adjust" Then
            lControlCount = lControlCount + 1
            ReDim Preserve aryLabels(1 To lControlCount)
            With aryLabels(lControlCount)
                Set .LabelGroup = ctrl
                .Init argHostUsf:=Me
            End With
        End If
    Next ctrl
End Sub

Public Sub ResetColor()
    Dim Ctl As Variant
    For Each Ctl In aryLabels                       ' <<< only labels are involved in the next Tag comparison
        If Ctl.LabelGroup.Tag = "Adjust" Then
            Ctl.ResetBackColor                      ' <<<  performs exactly the same as the next code line
            Ctl.LabelGroup.BackColor = vbWhite      ' <<<  performs exactly the same as the previous code line
        End If
    Next Ctl
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call ResetColor
End Sub


Class_LabelGroup module:
VBA Code:
Option Explicit

Public WithEvents LabelGroup As MSForms.Label

Private moUSF As Object

Public Sub Init(ByVal argHostUsf As Object)
    Set moUSF = argHostUsf
End Sub

Public Sub ResetBackColor()
    LabelGroup.Caption = ""
    LabelGroup.BackColor = vbWhite
End Sub

Private Sub LabelGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    moUSF.ResetColor
    LabelGroup.BackColor = vbGreen
    LabelGroup.Caption = "Parent: " & LabelGroup.Parent.Name    ' << just for demo
End Sub


EDIT:
Re-reading the above post the conclusion can be drawn that this code line (and the associated End If) can be dropped in the ResetColor procedure:

VBA Code:
         If Ctl.LabelGroup.Tag = "Adjust" Then

After all, only the tagged controls are included in the group during initialization of the Userform. I've overlooked that :cry:
 
Last edited:

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Hey there, thanks for the post. I'm reading it intently trying to get your gist. May have some follow-up :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,248
Messages
5,600,536
Members
414,385
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top