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 2016 VBA Copy to Textbox

0

Hi

Is it possible to copy a named range off a sheet and place in a Textbox.   Single column textbox only, not part of userform.

Does this sound ok?

Private Sub UserForm_Initialize()    
Dim Rng As Range       
Set Rng = Range("Destination")   / named range on sheet1    
With ListBox1            
List = Rng.Value    
End With
End Sub
Answer
Discuss

Answers

0
Selected Answer

This code will do the job.

Private Sub UserForm_Initialize()
    ' 15 Apr 2018
    
    Dim Arr() As Variant
    Dim i As Long
    Arr = ActiveSheet.Range("Destination").Value
    With ListBox1
        .Clear
        .TextColumn = 1
        For i = 1 To UBound(Arr)
            .AddItem Arr(i, 1)
        Next i
    End With
End Sub

Reading the entire range into an array and then the array into the ListBox is faster than accessing the sheet multiple times. However, if the entries are few it probably doesn't matter.

More important is the definition of the worksheet. Your code leaves its choice to chance, implicitly. Mine points out that it is left to chance (whichever happens to be active at the time). Could be that the sheet is determined by how the form is called and therefore not left to chance but it's a point worth paying attention to if one considers the possible consequences of not doing so.

Here is a variation of the above which leaves the ListBox's existing list in place.

Private Sub UserForm_Initialize()
    ' 15 Apr 2018 Var 1.0
    
    Dim Arr() As Variant
    Dim Idx As Long
    Dim i As Long
    
    Arr = ActiveSheet.Range("Destination").Value
    With ListBox1
        Idx = .ListCount
        For i = 1 To UBound(Arr)
            .AddItem Arr(i, 1), Idx
            Idx = Idx + 1
        Next i
    End With
End Sub

Note the relationship between ListCount (1 based) and ListIndex (0 based). Therefore in this variation the new item is added to ListIndex + 1, meaning an item that doesn't exist yet or is just being created.

Observe that I removed the line .TextColumn = 1. I previously added it to show that you can set all kinds of properties at that point. Actually, in a single column ListBox it's superfluous.

Discuss

Discussion

Hi Variatus
Thanksfor your post.  Forgot to mention one thing earlier,  I want previous text in the textbox to remain and the named range  "Destination" is   12 rows.  I can leave out clear, but how do I get the last row used +1  to place the new entry.
Now swatting up on ubound to see what it does.  the named range will be used about 10 times, 120 rows,  before a macro will clear. Doesnt need to be in this macro.  When I set up the textbox I will include scrolling.
Thankyou
Charles
sswcharlie (rep: 2) Apr 15, '18 at 3:09 am
Should I be using a Form Listbox or a ActiveX Listbox ?  Need 2 columns.  Can not find the setting to make 2 columns in either.

I want to try with a Textbox the same thing, 2 columns - found that setting

Thanks Charles
sswcharlie (rep: 2) Apr 16, '18 at 4:10 am
Userform_Initialize is the event procedure that runs when a user form is started. If your ListBox is on a worksheet I suggest you give the procedure a less misleading name.
FormControls are there for backward compatibility. I suggest you use ActiveX controls on your worksheet.
Please differentiate between ListBox and TextBox. The latter has no columns.
For a multi-column ListBox you need to set the ColumnCount property. You may also wish to set the ColumnWidths property, like 60 pt; 0pt to make the first column 60 points wide and hide the second. The TextColumn property determines which column you get when you refer to ListBox1.Text. No need to set if you don't refer.
To set the second column, use this code
 .List(Idx, 1) = Arr(i, 2)
Insert it before Idx is advanced in the code published above. I'm presuming that Arr has a second column. Of course, you can also use another source to set the value.
Variatus (rep: 4889) Apr 16, '18 at 5:43 am
Add to Discussion


Answer the Question

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