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?
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