Solved

Excel vba - specify bound column 2 in List Box

Posted on 2012-03-28
2
381 Views
Last Modified: 2012-03-28
Hello.  I have this handy piece of code which allows me to multi-select values in a list box, and those multiple values store in a cell as a comma separated string.

The problem is this only seems to work assuming the column you want bound is the same column that the list box shows (in this case column 1).  How can I get this code to work if I want the column 1 to show in the list box, but column 2 bound with the list of comma separated values?
Sub ListBox1_LostFocus()
  Dim s As String, i As Integer

  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then s = s & .List(i) & ","
    Next i
  End With
  
  With Range("A1")
    If s = vbNullString Then
      .Value = vbNullString
      Exit Sub
      Else
      .Value = Left(s, Len(s) - 1)
    End If
    On Error Resume Next
    '.Comment.Delete
    '.AddComment .Value
  End With
  
End Sub

Open in new window


Attached is an Excel file containing the code, list box, and named column range.
Multi-Select-in-Excel-List-Box.xlsm
0
Comment
Question by:jobprojn
2 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 37779599
Just add a second parameter to the .List

Sub ListBox1_LostFocus()
  Dim s As String, i As Integer

  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then s = s & .List(i, 1) & ","     'Note the .List(i,1)
    Next i
  End With
  
  With Range("A1")
    If s = vbNullString Then
      .Value = vbNullString
      Exit Sub
      Else
      .Value = Left(s, Len(s) - 1)
    End If
    On Error Resume Next
    '.Comment.Delete
    '.AddComment .Value
  End With
  
End Sub

Open in new window

0
 

Author Closing Comment

by:jobprojn
ID: 37779646
Worked like a charm.  Thanks!!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now