CODEWITHSUNDEEP
exceluserformvba
Sgdva
Sgdva

Reputation: 2800

Get Function() result as Control

Background:
I needed a function to correlate Controls efficiently based on a STD name and the text analyzed. IG:
a) Some other input throws the variable "mytext"
b) If the ListBox1.Value has "mytext" then I have to relate it with ToggleButton1
Approach:
I made the following function which partially works
Code:

Private Function RelateControl_ToggleVsList(ToggleCtrl As Control) As Control
Dim ItemControl As Control
Dim myControl As Object
    For Each ItemControl In Me.Controls
    If TypeName(ItemControl) = "ListBox" Then ' 1. If TypeName(ItemControl) = "Label"
    'text lenghts const 13 for ListBox_TimeXX and 22 for ToggleButton_PriorityXX
    If Mid(ItemControl.Name, 13, 2) = Mid(ToggleCtrl.Name, 22, 2) Then Set RelateControl_ToggleVsList = ItemControl: Exit Function
    End If ' 1. If TypeName(ItemControl) = "Label"
    Next ItemControl
End Function

Problem:
I get a null property when Setting the result:

Set RelateControl_ToggleVsList = ItemControl 'This is nullSet

enter image description here

Debugging process: enter image description here Question:
How can I set a Control as a result of this function?

EDIT:
Per request I add the whole Debugging in order to see where it is being called form
Calling Code 

Private Sub ToggleButtons_Active()
Dim ItemControl As Control
Dim ItemTextBox As Variant
Dim TxtControl As String
    For Each ItemControl In Me.Controls
    If TypeName(ItemControl) = "ToggleButton" Then ' 1. If TypeName(ItemControl) = "ToggleButton"
    TxtControl = CStr(RelateControl_ToggleVsList(ItemControl).Value)
    If InStr(TextBox_Notes.Value, TxtControl) > 0 And TxtControl <> "" Then ItemControl.Value = True
    End If ' 1. If TypeName(ItemControl) = "ToggleButton"
    Next ItemControl
End Sub

enter image description here

Upvotes: 0

Views: 67

Answers (2)

Sgdva
Sgdva

Reputation: 2800

Solution:
The object itself is calling a property which does not belong to it (as intended).
ListBox Properties does not show "Value" as its text, the real thing should have called it as:

TxtControl = CStr(RelateControl_ToggleVsList(ItemControl).List(0))CStr(RelateControl_ToggleVsList(ItemControl).List(0))

Furthermore:
Thanks to the solutions provided and the debugging process, I could notice even the object is Set in the function, it is shown as the "value" property of it while debugging.

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Your error is occurring on your line which says

TxtControl = CStr(RelateControl_ToggleVsList(ItemControl).Value)

because the returned Control's Value property is currently Null which can't be cast to a String.

I recommend that you change TxtControl to be a Variant type, then say

TxtControl = RelateControl_ToggleVsList(ItemControl).Value
If IsNull(TxtControl) Then
    TxtControl = ""
Else
    TxtControl = CStr(TxtControl)
End If

Or you could define a Control object and then use it:

Dim MyControl As Control
MyControl = RelateControl_ToggleVsList(ItemControl)
If IsNull(MyControl.Value) Then
    TxtControl = ""
Else
    TxtControl = CStr(MyControl.Value)
End If

Upvotes: 3

Related Questions