Pass Value to Column 2 of a Listbox

[music_al]

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

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

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

 

[Norie]

Try this.

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]

I get an error

Runtime error 70

Could not set the List property. Permission denied.

 

[Norie]

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

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

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

 

[music_al]

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

    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...

VBS.Format(Task_Date)

...but this doesnt change it.

 

[dmt32]

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

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]

If you only mean when the column is updated try this.

list_Tasks.List(i,1) = Format(TaskDate, "dd/mm/yyyy"