Userform Listbox to select a named range

Question

I've got a set of named ranges on a worksheet which hold summary data from other tabs (one for each month). The ranges are named JAN / FEB / MAR etc. My file contains various reports which compare one month with another and to make this dynamic I need users to be able to compare any two months.

The reports run off a separate sheet which has a pasted (values) version of which ever months summary data you need, so essentially I want a macro with a user form that allows a user to select a month then it will find that range, copy it and paste it in the sheet that drives the report. I've managed to do something similar based on sheet names (see code below) but I can't get anything to work for named ranges.

Any help gladly appreciated, I'm very much a self educated amateur at all this.

Private Sub CommandButton1_Click()
    Dim i As Integer, sht As String
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            sht = ListBox1.List(i)
        End If
    Next i
    Sheets(sht).Range("A4:C15").Copy
    Sheets("Sheet1").Select.Range("N1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End
End Sub

Private Sub CommandButton2_Click()
    Unload UserForm2
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ListBox1.AddItem (ws.Name)
    Next ws
End Sub

Answer 1

First you'll need a UI control that will allow the user to select a month, a ComboBox probably. Then, based on the selected value you can pick to which named range to select. You select that range and iterate through it with a simple for-each cycle.

In the end, it could look something like this:

Sub Main(selectedMonth as Integer)
    Dim referenceName As String
    Dim monthRange As Range
    Dim cell As Range

    Select Case selectedMonth
        Case 1
            referenceName = "JanuaryRange"
        Case 2
            referenceName = "FebruaryRange"
        ' etc
    End Select

    If referenceName <> "" Then
        Set monthRange = Range(referenceName)

        For Each cell In monthRange
            ' Add cell.Value as item to your listbox
        Next cell
    End If
End sub

You maybe need to add some more error handling.

PS: You should really use descriptive names for your objects, CommandButton2 is maybe fine if you have two of them, once you have more you're gonna have a bad time.

Answer 2

You can load all monthnames in a combobox:

Private Sub Userform_Initialize()
 combobox1.List = Application.GetCustomListContents(4)
end sub

You can copy the selected monthrange:

Private Sub Combobox1_Change()
 With ThisWorkbook.Names(combobox1.value)
  sheets("sheet1").cells(1,14).Resize(.RefersToRange.Rows.Count, .RefersToRange.Columns.Count) = .RefersToRange.Value
 End With
End Sub

Answer 3

Thanks both for your quick responses (and good advice with the descriptive names Marek)

The combo box worked a treat but I also managed to get my original listbox working as follows (not sure it's the most technically sound way of doing it but seems to work OK)...

I did have to activate the sheet in order to paste which I know isn't best practice but it wasn't happy without that written in so I compromised and let it have its way!

Dim rng As Integer

Private Sub CommandButton3_Click() Unload UserForm1 End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub CommandButton1_Click() Dim i As Integer, rng As String For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then rng = ListBox1.List(i) End If Next i Range(rng).Copy Sheets("DRIVE").Activate Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Sheets("REPORTS").Activate End

End Sub

Private Sub UserForm_Initialize()

With ListBox1

.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"

End With

End Sub

Private Sub CommandButton2_Click()

Dim i As Integer, rng As String
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        rng = ListBox1.List(i)
    End If
Next i
Range(rng).Copy
Sheets("DRIVE").Activate
Range("A43").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Sheets("REPORTS").Activate
End

End Sub