CODEWITHSUNDEEP
excelvbacombobox
AJZlindsay93
AJZlindsay93

Reputation: 31

Referencing a combobox without knowing its number

I had posted previously for help with the below code:

Sub Checkbox_AfterUpdate()
Dim ws As Worksheet
Dim Br As Range 'Bend Input Sheet
Dim Sr As Range 'Straight Input Sheet
Dim T As Object
Dim R As Range


Set Br = Worksheets("Hidden 1").Range("A2:D15")
Set Sr = Worksheets("Hidden 1").Range("A18:D31")
Set sht1 = ActiveSheet


Set T = sht1.Shapes(1)
Set R = ActiveCell.Offset(1)

If T.ControlFormat.Value = 2 Then
            Br.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
    
    End If
    
    If T.ControlFormat.Value = 3 Then
            Sr.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
End If

Forgive me for this long winded explanation. My project is to specify a number of pipes and assess their effect on a piece of equipment. I enter n number of pipes and it inserts n number of dropdown comboboxes pasted from a hidden sheet. The above code is assigned to each dropdown combobox and inserts the correct orientation based on the selection from the drop down. As can be seen from the code it refers to Shapes(1). This works great for the 1st dropdown, however for the rest of the dropdown it inserts the selection from the 1st dropdown (obviously due to the (1)). Is there anyway to have the assigned macro code reference the combobox that your using, rather than needing to know the number of it before hand ?. Again apologies for the very long winded question. I have pasted my attempt below

Sub Checkbox_AfterUpdate2()
Dim ws As Worksheet
Dim Br As Range 'Bend Input Sheet
Dim Sr As Range 'Straight Input Sheet
Dim T As Object
Dim R As Range
Dim Nme As String
Dim UserSelection As Object

Set Br = Worksheets("Hidden 1").Range("A2:D15")
Set Sr = Worksheets("Hidden 1").Range("A18:D31")
Set sht1 = ActiveSheet
Nme = ActiveSheet.Shapes(Application.Caller).Name

Set T = sht1.Shapes.Range(Array("Nme"))
Set R = ActiveCell.Offset(1)

If T.ControlFormat.Value = 2 Then
            Br.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
    
    End If
    
    If T.ControlFormat.Value = 3 Then
            Sr.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
End If
End Sub

This returns "Run-time error '1004' The item with the specified name wasn't found"

Upvotes: 1

Views: 47

Answers (1)

AJZlindsay93
AJZlindsay93

Reputation: 31

Figured it out

Sub Checkbox_AfterUpdate2()
Dim ws As Worksheet
Dim Br As Range 'Bend Input Sheet
Dim Sr As Range 'Straight Input Sheet
Dim T As Object
Dim R As Range
Dim Nme As String
Dim UserSelection As Object

Set Br = Worksheets("Hidden 1").Range("A2:D15")
Set Sr = Worksheets("Hidden 1").Range("A18:D31")
Set sht1 = ActiveSheet
Nme = ActiveSheet.Shapes(Application.Caller).Name

Set T = sht1.Shapes(Nme)
Set R = ActiveCell.Offset(1)

If T.ControlFormat.Value = 2 Then
            Br.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
    
    End If
    
    If T.ControlFormat.Value = 3 Then
            Sr.Copy 'select the range you want to copy
            R.Insert Shift:=xlDown
End If

Upvotes: 2

Related Questions