How to get a name of control by name?

I have a simple function where there's a combo box. If combo box's value is equal to "Disable", I'll disable textbox B. There are many combo boxes with their corresponding textbox B, arranged in rows and named by hand. If combo box A is named Product1, textbox B will be named Product1_status

I was thinking something like:

If value_of_a = "disable" Then 
 Dim name_of_b as String
 name_of_b = Me.Combo.Name + "_status"
 get_object_by_name(name_of_b).Enabled = False
End If

How do I do this?

Answers


I'm not sure how you are calling this, but here's a self-contained procedure that should help:

Sub test()

Dim ws As Excel.Worksheet
Dim ProductCombo As OLEObject
Dim ProductText As OLEObject

Set ws = ThisWorkbook.Sheets(1)
With ws
    Set ProductCombo = .OLEObjects("Product1")
    Set ProductText = .OLEObjects(ProductCombo.Name & "_status")
    ProductText.Enabled = ProductCombo.Object.Text <> "Disabled"
End With
End Sub

EDIT: I really hate worksheet controls - I start from scratch every time I program them! Nonetheless, I thought I'd add this subroutine which resets every textbox whose name fits the patter Product#_status, according to its paired combobox. The logic does assume the names start with Product1, Product2, etc., without a gap in the numbering:

Sub test2()

Dim ws As Excel.Worksheet
Dim ctl As OLEObject
Dim i As Long
Dim ProductComboboxesCount
Dim ProductCombo As OLEObject
Dim ProductText As OLEObject
Const ControlPrefix As String = "Product"

Set ws = ThisWorkbook.Sheets(1)
With ws
    For Each ctl In .OLEObjects
        If TypeOf ctl.Object Is MSForms.ComboBox And Left(ctl.Name, Len(ControlPrefix)) = ControlPrefix Then
            ProductComboboxesCount = ProductComboboxesCount + 1
        End If
    Next ctl
    For i = 1 To ProductComboboxesCount
        Set ProductCombo = .OLEObjects(ControlPrefix & i)
        Set ProductText = .OLEObjects(ControlPrefix & i & "_status")
        ProductText.Enabled = ProductCombo.Object.Text <> "Disabled"
    Next i
End With
End Sub

Need Your Help

Is there a way to always execute some code before Exit Function in VBScript

function vbscript exit qtp

I would like to write a function in which there are multiple "Exit Function" statements. Is there a way for me to make sure some clean up code is alway run before doing the exit function?

Database Objects Scripts in Order?

sql-server tsql

When we script all Sql Server Database objects from Management Studio by right clicking on the database, and go to tasks and select "generate scripts", will the generated scripts be in order???

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.