Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel VBA named range from combobox selection

0

Worksheet name = list.NamedRanges

Worksheet Code Name = ShLI01

Combo box name = Namecb1

Combo Box is populated with a dynamic list of the Activeworkbook named ranges.

I wish a list of values for the named range selected in the combo box. The list starts in cell Q8 then proceeds down column Q. Several named ranges have multiple values. This program is in design mode so I wish to use the code name in formulas because worksheets may be renamed for better understanding.

I would appreciate any assistance.

Answer
Discuss

Discussion

Please follow the rules. They require two things from you.
1. There must be an "own effort", something to show that you tried to find a solution on your own.
2. There must be a question that has an answer that relates to Excel ("Who will do the work for me?" is a question but not related to Excel)
The point is that finding the question is often more difficult than providing the answer. Usually the question will be found while trying to solve the problem. If you find your question in this way you are more likely to understand the answer.
"I wish a list of values for the named range selected in the combo box." permits a fairly intelligent guess at what your question might be. But an "own effort" would come with a workbook that can serve to specify your requirement more precisely and save everybody's time. A real question, like "how do I ...?" would force you to be more specific in what you want to do, also saving everybody's time.
Variatus (rep: 4889) Nov 28, '19 at 7:56 pm
  After trying several solutions found on the internet and each failing i used another macro i had for getting a list of named ranges of the current workbook but with some changes. it would give me a list of values for all the ranges but when a range had several values it listed nothing. I just want an excel vba that will give me a full value list for the name range selected in the combo box.   This is the code I was trying but with no luck…  
Sub Namecb1_Change()
Dim nm As Name, n As Long, y As Range, z As Worksheet
Application.ScreenUpdating = False
Set z = ActiveSheet
n = 8
With z
    .[O5:O155].ClearContents
    .[Q7] = [{"Value"}]
    For Each nm In ActiveWorkbook.Names
        If nm.Name = "namecb1" Then
            .Cells(n, 17) = nm.Value
            n = n + 1
        End If
    Next nm
End With
End Sub

Dean3551 (rep: 4) Nov 28, '19 at 8:35 pm
i may have choosen the wrong button to add to this dicussion (i may have choosen to close this but i dont).
I cannot get it to work for the range selected in the combo box (Namecb1). As your code runs it gives all the named ranges but not their values. I just want the values for the range selected. I also made an error in the worksheet codename it’s not ShLI01, it should be ShLI02. I also made some cell reference changes, so my code is 
 
Sub ListNamedRangevalue2()
    Dim Fun() As String
    Dim i As Integer
    With ActiveWorkbook.Names
        ReDim Fun(1 To .Count)
        For i = 1 To .Count
            Fun(i) = .Item(i).Name
        Next i
    End With
 
    With ShLI02
        .Range(.Cells(8, "O"), .Cells(.Rows.Count, "O").End(xlUp)).ClearContents
        .Cells(8, "O").Resize(UBound(Fun)).Value = Application.Transpose(Fun)
    End With
End Sub

I’m still working with your suggestion, but trying to get the values of the named range selected on combo box Namecb1
Dean3551 (rep: 4) Nov 29, '19 at 9:46 am
Add to Discussion

Answers

0
Selected Answer

Please try this code.

Sub ListNamedRange()

    Dim Fun() As String
    Dim i As Integer

    With ActiveWorkbook.Names
        ReDim Fun(1 To .Count)
        For i = 1 To .Count
            Fun(i) = .Item(i).Name
        Next i
    End With

    With ShLI01
        .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)).ClearContents
        .Cells(1, "A").Resize(UBound(Fun)).Value = Application.Transpose(Fun)
    End With
End Sub

It will write the list to column A of your ShLI01 sheet, starting in A1, after deleting any previously existing list in the sme location.

Discuss

Discussion

I cannot get it to work for the range selected in the combo box (Namecb1). As your code runs it gives all the named ranges but not their values. I just want the values for the range selected. I also made an error in the worksheet codename it’s not ShLI01, it should be ShLI02. I also made some cell reference changes, so my code is 
 
Sub ListNamedRangevalue2()
    Dim Fun() As String
    Dim i As Integer
    With ActiveWorkbook.Names
        ReDim Fun(1 To .Count)
        For i = 1 To .Count
            Fun(i) = .Item(i).Name
        Next i
    End With
 
    With ShLI02
        .Range(.Cells(8, "O"), .Cells(.Rows.Count, "O").End(xlUp)).ClearContents
        .Cells(8, "O").Resize(UBound(Fun)).Value = Application.Transpose(Fun)
    End With
End Sub

I’m still working with your suggestion, but trying to get the values of the named range selected on combo box Namecb1
Dean3551 (rep: 4) Nov 29, '19 at 9:41 am
We have to find a balance here between your need to get the code to work and my principle not to entertain follow-up questions. This is a Q&A forum. Let's stick to that format and avoid entering into correspondence that nobody else is interested in or has the time to read.
You have mastered the code and made the modifications it invites you to make correctly. The problem now is in the formulation of your question. You want to "get the values of the named range selected on a combobox". I understand this to mean that you want the list of names in a combobox (step 1), then select one of them and show the "value" somewhere (step 2). Of course, the combobox could be on a worksheet or on a userform. You don't say. And the value of the named range could be the value of a single cell or an array of a million cells. You also don't say. And you don't say where you want this value displayed. That could be in a cell (where?) or in a textbox (where).
Obviously, there are two questions, neither of them related to this thread. Therefore you need to create two new questions. I suggest that you don't ask anything about step 2 before you have an answer to step 1.
If you want to ask a question about a combobox it's a good idea to attach a workbook with the the Cbx in it to your question. You will make faster progress if you design your questions more carefully.
Variatus (rep: 4889) Nov 29, '19 at 7:51 pm
i took your advice and reposted under 'Excel VBA named range from combobox selection #2' and this time i included a file and tried to better describe my wish/problem.
Thanks for the help
Dean3551 (rep: 4) Nov 30, '19 at 1:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login