[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Use a constructed string to reference a checkbox

I am using the solution from the article "How to use a constructed string to reference a combobox" to return a control (Name) for text boxes and list however I have hit a problem when trying to use this method for an array of checkboxes. In the code below the control is found however c is then set to 0, the index of the control I think, not the name of the control.

Public Function getControlByName(ByVal controlName As String) As Control
   
    Dim c As Control
   
    For Each c In MyFrm.Controls
        If c.Name = controlName Then
            Set getControlByName = c

            Exit Function
        End If
    Next
    MsgBox controlName, vbCritical, "No matching control found"
   
End Function

Thank you
Debs
0
debrafry
Asked:
debrafry
  • 2
1 Solution
 
JR2003Commented:
I think zero is the value of the default property of the checkbox (the value vbUnchecked = 0).
It is actually returning a reference to the control as you expect. Try this:

Public Function getControlByName(ByVal controlName As String) As Control
   
    Dim c As Control
   
    For Each c In MyFrm.Controls
        If c.Name = controlName Then
            Set getControlByName = c
            If TypeOf getControlByName Is CheckBox Then
                Debug.Print getControlByName.Name
                Debug.Print getControlByName.Index
                Debug.Print getControlByName.Value
            End If
            Exit Function
        End If
    Next
    MsgBox controlName, vbCritical, "No matching control found"
   
End Function
0
 
zzzzzoocCommented:
The function does not include matching indexes so it'll only return the first name that matches (despite the index).

Try...

Private Sub Form_Load()
    Dim objCheck As CheckBox
    'Get Check2(2)...
    Set objCheck = getControlByName(Form1, "Check2", 2)
    objCheck.Value = vbChecked
End Sub
Private Function getControlByName(ByRef cForm As Form, ByVal cName As String, Optional ByVal cIndex As Integer = -1) As Control
    Dim objCtrl As Control
    For Each objCtrl In cForm.Controls
        If objCtrl.Name = cName Then
            If cIndex > -1 Then
                If objCtrl.Index = cIndex Then
                    Set getControlByName = objCtrl
                    Exit Function
                End If
            Else
                Set getControlByName = objCtrl
                Exit Function
            End If
        End If
    Next objCtrl
End Function
0
 
debrafryAuthor Commented:
Unfortunately neither of the above answers gives me what I need.
a bit more information to help (I hope :-))

Below is the function that is calling the getControlByName function. Although I  know my control name I need build it up from a string, as you are unable to set a control to be a string (so to speak) I call the getcontrolbyname function.  Although with the function you can reference getcontrolbyname.name you can't set the function to return this. So unfortunately both of the suggestions do not return me back a named control i.e If the string I passed into the function is STchkRNWon then is control that I would like back is STchkRNWon not 0 as my function currently does.

The reason the prefix is added to the name (see below for clarity) is that there are 6 controls on the form with almost the same name ie ??chkRNWon and the one that needs to be populated is the one for the current department.

Sorry to be difficult :-( and thank you for your help
Debs
***************************************
Public Function ReasonNotWon(department As String)
   
   Dim i As Integer
   Dim pos As Integer
   Dim nextpos As Integer
   Dim myCheckbox As String
   Dim myTextbox As String
   Dim myChkControl As Control
   Dim myTxtControl As Control
   If department = "" Then department = pMyDeptID
   myCheckbox = department & "chkRNWon"
   myTextbox = department & "txtstrRNWon"
   '***********************************************
   Set myChkControl = getControlByName(myCheckbox)
   Set myTxtControl = getControlByName(myTextbox)
   
   For i = 0 To myChkControl.count - 1
      myChkControl.Item(i).value = 0
   Next
   If Len(myTxtControl.text) > 0 Then
     myTxtControl = Trim$(myTxtControl)
        pos = InStr(myTxtControl, ",")
     If pos > 0 Then
        i = Left$(myTxtControl, pos) - 1
        nextpos = InStr(pos + 1, myTxtControl, ",")
        myChkControl.Item(i).value = 1
        While nextpos <> 0
           i = Mid$(myTxtControl, pos + 1, 2) - 1
           myChkControl.Item(i).value = 1
           pos = nextpos
           nextpos = InStr(pos + 1, myTxtControl, ",")
        Wend
        i = Right$(myTxtControl, Len(myTxtControl) - pos) - 1
        myChkControl.Item(i).value = 1
     Else
        i = myTxtControl.text - 1
        myChkControl.Item(i).value = 1
     End If
   End If
End Function

 
0
 
JR2003Commented:
You are getting back a reference to one of the controls in the control array. Your problem is you want access to all the elements of that control array. You could write a new function 'getControlArrayByName' that returns an array of controls and then use it as a normal array: e.g:

Private Sub Command1_Click()
    Dim MyControl() As Control
    MyControl = getControlArrayByName(department & "chkRNWon")
    Dim i As Long
    For i = LBound(MyControl) To UBound(MyControl)
        MyControl(i).Value = vbChecked
    Next i
End Sub


Public Function getControlArrayByName(ByVal controlName As String) As Control()
   
    Dim c As Control
    Dim cArray() As Control
    Dim iControlCount As Integer
    For Each c In Me.Controls
        If c.Name = controlName Then
            ReDim Preserve cArray(iControlCount) As Control
            Set cArray(iControlCount) = c
            iControlCount = iControlCount + 1
        End If
    Next
    getControlArrayByName = cArray
   
End Function
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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