Is it possible to insert a range into a sheet based on the chosen drop down value from a combo box-CodePudding

Is it possible to insert a range into a sheet based on the chosen drop down value from a combo box. I want to choose "bend" or "straight" from the drop down and then insert the relevant range that is held in another sheet. This range must be directly below the combo box. I have pasted by attempt below. Any input would be greatly appreciated.

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 ' Value in drop down

Set Br = Worksheets("Hidden 1").Range("A2:D15")
Set Sr = Worksheets("Hidden 1").Range("A18:D31")
Set T = Worksheets("Hidden 1").DropDowns(6).Value
If T = "Bend" Then
        Br.Copy 'select the range you want to copy
        sht1.Range("A45").Insert

End If

If T = "Straight" Then
        Sr.Copy 'select the range you want to copy
        sht1.Range("A45").Insert

End If
End Sub

CodePudding user response：

I modified your code a bit. Assuming your combobox is the 6th Shape in worksheet:

Sub Checkbox_AfterUpdate()
    Dim ws As Worksheet, sht1 As Worksheet
    Dim Br As Range 'Bend Input Sheet
    Dim Sr As Range 'Straight Input Sheet
    Dim T As Shape ' Value in drop down
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sht1 = ThisWorkbook.Worksheets("Hidden 1")
    
    Set Br = Worksheets("Hidden 1").Range("A2:D15")
    Set Sr = Worksheets("Hidden 1").Range("A18:D31")
    
    'Add items to ComboBox
    Set T = ws.Shapes(6)
    With T.ControlFormat
        .AddItem "Bend"
        .AddItem "Straight"
    End With

    If T.ControlFormat.Value = 0 Then
            Br.Copy 'select the range you want to copy
            sht1.Range("A45").Insert
    
    End If
    
    If T.ControlFormat.Value = 1 Then
            Sr.Copy 'select the range you want to copy
            sht1.Range("A45").Insert

End If
End Sub

"Quick and dirty", but compiled just fined and does what you're after. Note that T.ControlFormat.AddItem appends item to the list associated with Shapes(6), so running it multiple times will add "Bend" and "Straight" again.

I changed Dim T as Object to Dim T as Shape to get access to syntax.

The key to getting your If-statement to work, is to references the value in the list-array element:

If T.ControlFormat.Value = 0 Then ...

Meaning that we check if the user has selected array element 0, aka. "Bend". You should also add some error-handling to handle what happens when user ahs not selected an entry.

CodePudding user response：

Ok so using some of the above answer i have written this code which works

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

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

Set T = sht1.Shapes(1)

If T.ControlFormat.Value = 2 Then
            Br.Copy 'select the range you want to copy
            sht1.Range("A45").Insert
    
    End If
    
    If T.ControlFormat.Value = 3 Then
            Sr.Copy 'select the range you want to copy
            sht1.Range("A45").Insert
End If
End Sub

However. This combobox then gets pasted x number of times into another sheet but the macro above is no longer assigned. Is there a way to fix this ?