Inserting a named range based on specific cell value

[cconnor970]

What I am trying to do is use a drop list, and based on the selection, one of 4 named ranges will be inserted into an area on the spreadsheet. I am using a combo box, all is working fine there, but based the selection, I have 4 separate named ranges I want to then fill a space on a sheet (sales quote) with the specific named range tied to the specific selection.

I've tried to find a way to insert a range, no go, tried to have the range change color based on the one cells specific value, nope. Ideas?

 

[pbornemeier]

Use code based on the change of the combo box.

One of these depending on which type of combo box you are using:

Examples assume named ranges NMRG1, NMRG2, NMRG3, NMRG4 exist and are to be copied to the range set in code depending on the value (1, 2, 3,4) selected in either combo box.

Option Explicit

Sub FormsControlDropDownBox()
    'For Forms Control ComboBox
    
    Dim vReturn As Variant
    Dim nrToCopyName As String
    Dim rngDestination As Range
    
    Set rngDestination = Worksheets("Sheet1").Range("T2")
    
    With ActiveSheet.Shapes("Drop Down 1")
        vReturn = .ControlFormat.List(.ControlFormat.ListIndex)
        'I have no clue why they did not use something like
        '  ActiveSheet.Shapes("Drop Down 1").Value instead !!
    End With
    Select Case vReturn
    Case 1: nrToCopyName = "NMRG1"
    Case 2: nrToCopyName = "NMRG2"
    Case 3: nrToCopyName = "NMRG3"
    Case 4: nrToCopyName = "NMRG4"
    End Select
    
    Range(nrToCopyName).Copy rngDestination
    
    Set rngDestination = Nothing
    
End Sub

Private Sub ComboBox1_Change()
    'For ActiveX ComboBox
    'This sub must be located on the worksheet that contains the ActivX ComboBox
    
    Dim vReturn As Variant
    Dim nrToCopyName As String
    Dim rngDestination As Range
    
    Set rngDestination = Worksheets("Sheet1").Range("T2")
    
    vReturn = Me.ComboBox1.Value

    Select Case vReturn
    Case 1: nrToCopyName = "NMRG1"
    Case 2: nrToCopyName = "NMRG2"
    Case 3: nrToCopyName = "NMRG3"
    Case 4: nrToCopyName = "NMRG4"
    End Select
    
    Range(nrToCopyName).Copy rngDestination
    
    Set rngDestination = Nothing

End Sub

 

[cconnor970]

Great, I'll give it a try, thanks!