Cannot Use All Properties While Looping Through Each COntrol Of An UserForm

[angsuman]

Hello All,


I have three text boxes and three command buttons in my userform called "ReqInput". I am trying to perform some action like set enabled properties to False for all command button except first one and set enabled properties to False for all text box except first one and also set focus to first text box. I am using following code

https://onedrive.live.com/?cid=415C...9ADA!13002&parId=415C19A846949ADA!122&o=OneUp

Dim colReqInputCtr As Collection
Dim clsReqInputObject As clsReqInputClass


Private Sub UserForm_Initialize()
   Dim Ctr As Control
   Dim CTbx As MSForms.TextBox
   Dim i As Long
   
   i = 0
   
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "TextBox" Then
         Ctr.Enabled = False
         Set clsReqInputObject = New clsReqInputClass
         Set clsReqInputObject = Ctr
         colReqInputCtr.Add clsReqInputObject
      End If
   Next
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "CommandButton" Then
         If Ctr.Caption = "Next" Then
            Ctr.Enabled = True
         Else
            Ctr.Enabled = False
         End If
         Set clsReqInputObject = New clsReqInputClass
         Set clsReqInputObject = Ctr
         colReqInputCtr.Add clsReqInputObject
      End If
   Next
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "TextBox" Then
         If i = 0 Then
            Ctr.Enabled = True
            i = i + 1
         Else
            Ctr.Enabled = False
            i = i + 1
         End If
      End If
   Next
End Sub

I can use individual command button name to set enabled properties to false and same for text box. But, I am going to handle all these controls using custom event handler and so using for loop.However this gives me error as below. Also I do not see Enabled properties popping up when I type "Ctr.". What is my mistake? Is enabled properties not available when I am using Ctr.



Thanks
Angsuman

 

[Akuini]

If your textbox & commandbutton name are textbox1,textbox2 etc & commandbutton1, commandbutton2 etc, then this code should work.

[FONT=lucida console][COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR]
    [COLOR=Royalblue]If[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Me.Controls([COLOR=brown]"textBox"[/COLOR] & i).Enabled = [COLOR=Royalblue]True[/COLOR]
        Me.Controls([COLOR=brown]"textBox"[/COLOR] & i).SetFocus
        Me.Controls([COLOR=brown]"CommandButton"[/COLOR] & i).Enabled = [COLOR=Royalblue]True[/COLOR]
    [COLOR=Royalblue]Else[/COLOR]
        Me.Controls([COLOR=brown]"textBox"[/COLOR] & i).Enabled = [COLOR=Royalblue]False[/COLOR]
        Me.Controls([COLOR=brown]"CommandButton"[/COLOR] & i).Enabled = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR][/FONT]

 

[angsuman]

Thank you Akuini for your reply. I fond that error is not in this part. It is actually coming from control collection and assigning it to custom event. If you look at the code, you can see I have used following in userform initialize:

[COLOR=#ff8c00]Dim colReqInputCtr As Collection
Dim clsReqInputObject As clsReqInputClass[/COLOR]


Private Sub UserForm_Initialize()
   Dim Ctr As Control
   Dim CTbx As MSForms.TextBox
   Dim i As Long
   
   i = 0
   
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "TextBox" Then
         Ctr.Enabled = False
         [COLOR=#ff8c00]Set clsReqInputObject = New clsReqInputClass
         Set clsReqInputObject = Ctr
         colReqInputCtr.Add clsReqInputObject[/COLOR]
      End If
   Next
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "CommandButton" Then
         If Ctr.Caption = "Next" Then
            Ctr.Enabled = True
         Else
            Ctr.Enabled = False
         End If
[COLOR=#ff8c00]         Set clsReqInputObject = New clsReqInputClass
         Set clsReqInputObject = Ctr
         colReqInputCtr.Add clsReqInputObject[/COLOR]
      End If
   Next
   
   For Each Ctr In ReqInput.Controls
      If TypeName(Ctr) = "TextBox" Then
         If i = 0 Then
            Ctr.Enabled = True
            i = i + 1
         Else
            Ctr.Enabled = False
            i = i + 1
         End If
      End If
   Next
End Sub

And in class I have used following:

Class Name: clsReqInputClass

Option Explicit
Public WithEvents tbReqInput As MSForms.TextBox       'Custom Textbox
Public WithEvents cbReqInput As MSForms.CommandButton   'Custom Command


Private Sub cbReqInput_Click()
   Dim Ctr As Control
   For Each Ctr In CcDataCollect.Controls
   
       MsgBox "Ctr = " & Ctr, vbInformation
   Next Ctr
End Sub

I am getting error as "Object Variable or With block variable not set"

I used similar code in other scenario and it worked. Not sure why is it giving error in this case. Please help.

Thanks
Angsuman

 

[angsuman]

I believe the issue is with Set clsReqInputObject = Ctr.How do I assign a text box to the object when text box is referenced by Ctr

 

[Akuini]

I believe the issue is with Set clsReqInputObject = Ctr.How do I assign a text box to the object when text box is referenced by Ctr

Sorry, I'm not familiar with that method.
Maybe someone else could help.

 

[mikerickson]

clsRegInputObject is a custom object, not a MSForms.TextBox. Therefore you cannot set the value of that object to a TextBox. But you can make the class such that one if its properties is an MSForms.TextBox and assign a TextBox to that property.

If I were to write a class (Class1) to provide handling for a bunch of text boxes. It would look something like

' in Class1 module

Public WithEvents myTextBox as MSForms.TextBox

Private Sub myTextBox_Change()
    MsgBox myTextBox.Name & " has changed value to " & myTextBox.Text
End Sub

(Yes, its a truely annoying class)

To assing that text box to a custom object I would use something like

Dim myObj as Class1
'.....

Set myObj = New Class1
Set myObj.myTextBox = TextBox1

Note that I am not assigning the textbox to myObj, but the myTextBox property of myObj.

 

[angsuman]

Hello Mikerickson,

Thank you for your input. I believe I did the same thing>

In class clsReqInputClass I defined as TextBox and CommandButton as MSFORMS as below:

Option Explicit
Public WithEvents tbReqInput As MSForms.TextBox      
Public WithEvents cbReqInput As MSForms.CommandButton

In Userform my code is:

Option Explicit
Dim colReqInputCtr As Collection
Dim clsReqInputObject As clsReqInputClass




Private Sub UserForm_Initialize()
   Dim Ctr As Control
   Dim i As Long
   
   i = 0
   
   For Each Ctr In ReqInput.Controls
      
      If TypeName(Ctr) = "TextBox" Then
         MsgBox " Ctr = " & Ctb, vbInformation
         MsgBox "Ctr = " & Ctr.Name & " Type = " & TypeName(Ctr), vbInformation
         Set clsReqInputObject = New clsReqInputClass
         Set clsReqInputObject.tbReqInput = Ctr.Name   <----------------I think problem is here. Are you suggesting to assign text box by its name?
         colReqInputCtr.Add clsReqInputObject
      End If
   Next
 End Sub

Thanks
Angsuman

 

[mikerickson]

I would use this in that situation.

Set clsReqInputObject.tbReqInput = Ctr

I've written classes that can take various types of controls. I've found that funneling everything through a Control property helps.

' in Class1

Public WithEvents pTextBox as MSForms.TextBox
Public WithEvents pCommandButton as MSForms.CommandButton
Dim pControl as MSForms.Control

Property Get Control() as MSForms.Control
    Set Control = pControl
End Property

Property Set Control(inControl as MSForms.Control)
    Set pControl = inControl
    Select Case Typename(pControl)
        Case "TextBox"
            Set pTextBox = pControl
        Case "CommandButton"
            Set pCommandButton = pControl
    End Select
End Property

Then the outside code could look like this, but all the "what type of control is it" code goes inside the module.

Dim Obj1 as New Class1, Obj2 as New Class1

Set Obj1.Control = TextBox1
Set Obj2.Control = CommandButton1

 

[mikerickson]

This also lets you mush properties together, like the .Caption and .Text properties of Class1 below

' in Class1

Public WithEvents pCheckBox As MSForms.CheckBox
Public WithEvents pCommandButton As MSForms.CommandButton
Public WithEvents pTextBox As MSForms.TextBox
Dim pControl As MSForms.Control

Property Get Control() As MSForms.Control
    Set Control = pControl
End Property
Property Set Control(inControl As MSForms.Control)
    Set pCheckBox = Nothing
    Set pCommandButton = Nothing
    Set pTextBox = Nothing
    Set pControl = inControl
    Select Case TypeName(pControl)
        Case "CheckBox"
            Set pCheckBox = pControl
        Case "CommandButton"
            Set pCommandButton = pControl
        Case "TextBox"
            Set pTextBox = pControl
    End Select
End Property

Property Get Caption() As String
    Select Case TypeName(pControl)
        Case "CheckBox", "CommandButton"
            Caption = pControl.Caption
        Case "TextBox"
            Caption = pControl.Text
    End Select
End Property
Property Let Caption(ByVal inVal As String)
    Select Case TypeName(pControl)
        Case "CheckBox", "CommandButton"
            pControl.Caption = inVal
        Case "TextBox"
            pControl.Text = inVal
    End Select
End Property

Property Get Name() As String
    Name = pControl.Name
End Property

Property Get Text() As String
    Text = Me.Caption
End Property
Property Let Text(ByVal inVal As String)
    Me.Caption = inVal
End Property

 

[angsuman]

Hello Mikerickson,

Thank you for your help. The issue resolved.

Thanks
Angsuman