Pass Value to Column 2 of a Listbox

music_al

Board Regular
Joined
Nov 26, 2008
Messages
131
Hi

I have a Listbox with 2 columns. I am populating the Listbox from a named range and this works fine.

When the user clicks on one of the rows of the Listbox an Input Box appears asking the user to enter a date. When they enter the date I would like the date to appear in column 2 of the List Item they clicked. Here is my code so far...

Initialize
Code:
Private Sub UserForm_Initialize()
    Dim TaskInt As Integer
    Me.list_Tasks.RowSource = "Task_List"
    
    Dim i As Integer
    Dim TaskVal As Range
    Dim TaskName As String
    TaskInt = 1
    i = 0
    Do Until TaskInt = list_Tasks.ListCount + 1
    TaskName = list_Tasks.List(i)
    Set TaskVal = ActiveCell.Find(What:=TaskName)
        If TaskVal Is Nothing Then 'If the number isnt found
            i = i + 1
        Else
            list_Tasks.Selected(i) = True
            i = i + 1
        End If
    
    TaskInt = TaskInt + 1 'Increment the Task Int by 1
    Loop


End Sub

Clicking the ListBox
Code:
Private Sub list_Tasks_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim TaskDate As Date
    i = Me.list_Tasks.ListIndex
    If list_Tasks.Selected(i) = True Then
        TaskDate = InputBox("Enter a date for the task", "Task Date", VBA.Format(Now(), "dd/mm/yyyy"))
        
[B][COLOR=#ff0000]    'WHAT DO I PUT HERE TO PASS TaskDate to the Listbox ??????
[/COLOR][/B]
    End If

End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
list_Tasks.List(i,1) = TaskDate
By the way, if the listbox is single select you don't need to check Selected.

All you need to check is that the ListIndex is not equal to -1, if it is -1 then that means nothing has been selected in the listbox.
 

music_al

Board Regular
Joined
Nov 26, 2008
Messages
131
I get an error

Runtime error 70

Could not set the List property. Permission denied.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
You can't directly alter the values in a listbox if you populate it with RowSource, try using List insted.

Code:
    Me.list_Tasks.RowSource = "" ' clear RowSource property as a precaution

    Me.list_Tasks.List = Range("Task_List"").Value
 

music_al

Board Regular
Joined
Nov 26, 2008
Messages
131

ADVERTISEMENT

You can't directly alter the values in a listbox if you populate it with RowSource, try using List insted.

Code:
    Me.list_Tasks.RowSource = "" ' clear RowSource property as a precaution

    Me.list_Tasks.List = Range("Task_List"").Value


Thank you. It works but its entering the date in US format where I want dd-mmm-yy

Ive tried...

Code:
VBS.Format(Task_Date)

...but this doesnt change it.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
its entering the date in US format where I want dd-mmm-yy

Hi,
maybe passing your the cells text to a string array will solve your problem

try

Code:
Private Sub UserForm_Initialize()
    Dim Arr() As String
    Dim Cell As Range
    
    With Range("Task_List")
    ReDim Arr(1 To .Count, 1 To .Columns.Count) As String
    For Each Cell In .Cells
        Arr(Cell.Row, Cell.Column) = Cell.Text
    Next Cell
    End With


    With Me.list_Tasks
        .RowSource = ""
        .ColumnCount = UBound(Arr, 2)
        .List = Arr
    End With
    
    '
    '
    'rest of your code
    '
    '
    '
End Sub

Dave
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
If you only mean when the column is updated try this.
Code:
list_Tasks.List(i,1) = Format(TaskDate, "dd/mm/yyyy"
 

Forum statistics

Threads
1,148,291
Messages
5,745,884
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top