+ Reply to Thread
Results 1 to 9 of 9

Combobox combining 2 named ranges

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Combobox combining 2 named ranges

    I have two named ranges “FirstName” & “LastName” and wish a single column combobox (cmbName) to show a concatenation of the two ranges i.e. “Joe Bloggs”.
    What would the form initialization code be?
    Thanks Sandy

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,254

    Re: Combobox combining 2 named ranges

    E.g.:
    Module1 module:
    Option Explicit
    Option Base 1
    Option Private Module
    
    Public tbl() As Variant
    
    Sub tbl_collect()
    Dim r As Long, i As Long
    Dim tbl_tmp() As Variant
    
        tbl_tmp = Range("FirstName").Value
        tbl = Range("LastName").Value
        
        If UBound(tbl, 1) <> UBound(tbl_tmp, 1) Then
            MsgBox "O no no no no !" & vbCrLf & vbCrLf & "Different data ranges for 'FirstName' and 'LastName'", vbOKOnly, "Info"
            End
        End If
        
        r = UBound(tbl, 1)
        
        For i = 1 To r
            tbl(i, 1) = tbl_tmp(i, 1) & " " & tbl(i, 1) '="FirstName LastName"
        Next
        
        Erase tbl_tmp
    End Sub
    UserForm1 module:
    Option Explicit
    
    Private Sub UserForm_Initialize()
        Call tbl_collect
        
        With UserForm1
            .Caption = "TEST WINDOW"
            
            .ComboBox1.ColumnCount = 1
            .ComboBox1.TextColumn = 1
            .ComboBox1.ColumnWidths = "100"
            .ComboBox1.List() = tbl
            .ComboBox1.ListIndex = -1
            
            .CommandButton1.Caption = "Close window"
        End With
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        'If CloseMode <> 1 Then
            Erase tbl
        'End If
    End Sub
    
    Private Sub CommandButton1_Click()
        UserForm1.Hide
        Unload UserForm1
    End Sub
    Sheet1 module:
    Private Sub CommandButton1_Click()
        Range("c1").Select
        Load UserForm1
        UserForm1.Show
    End Sub
    Worksheet "Sheet1":
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by mjr veverka; 12-11-2017 at 09:54 PM.

  3. #3
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Combobox combining 2 named ranges

    Thanks Porucha I will test the code tomorrow and let you know.

  4. #4
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Combobox combining 2 named ranges

    Thanks again Porucha the code worked a treat.
    For some reason I had to comment out the named range test (which included "MsgBox "O no no no no !"")!
    Cheers Sandy

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,254

    Re: Combobox combining 2 named ranges

    ... Oh, no no no no ! ...
    Oh, yes ...

  6. #6
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Combobox combining 2 named ranges

    Where is "drevni ruchadlo"?

  7. #7
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,254

    Re: Combobox combining 2 named ranges


    In the land of a smile, such a Smile Land ...
    Unfortunately, sometimes it is very sad here ...

  8. #8
    Forum Contributor
    Join Date
    02-26-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    286

    Re: Combobox combining 2 named ranges

    Come to New Zealand, land of Hobbits, for a smile!

  9. #9
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,254

    Re: Combobox combining 2 named ranges

    Ok, if I don't get lost, by sailing there in a my miniature four-wheeled submarine ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combining named ranges
    By goels in forum Excel General
    Replies: 9
    Last Post: 11-18-2021, 06:20 AM
  2. Replies: 7
    Last Post: 05-12-2015, 11:34 AM
  3. [SOLVED] Add two Named Ranges in one ComboBox control
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-24-2015, 11:53 AM
  4. Combining Multiple Named Ranges
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2014, 12:11 PM
  5. Combobox with named ranges values to Textboxes
    By Kburges in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2013, 12:42 PM
  6. [SOLVED] Named ranges and combobox
    By bigfoot007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2012, 03:06 PM
  7. Combining Named Ranges
    By dmartindale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2007, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1