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
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
E.g.:
Module1 module:
UserForm1 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
Sheet1 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
Worksheet "Sheet1":![]()
Private Sub CommandButton1_Click() Range("c1").Select Load UserForm1 UserForm1.Show End Sub
Last edited by mjr veverka; 12-11-2017 at 09:54 PM.
Thanks Porucha I will test the code tomorrow and let you know.
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
... Oh, no no no no ! ...
Oh, yes ...![]()
Where is "drevni ruchadlo"?
In the land of a smile, such a Smile Land ...
Unfortunately, sometimes it is very sad here ...
![]()
Come to New Zealand, land of Hobbits, for a smile!
Ok, if I don't get lost, by sailing there in a my miniature four-wheeled submarine ...![]()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks