delete value in cell based on selection in listbox

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
636
Office Version
  1. 2016
Platform
  1. Windows
Hi

I have two listboxes where I can move tickers from left to right. This is synced with a sheet, such that the tickers also are pasted there too.

But if I use the remove button, I can remove tickers from the listbox, but I cannet delete them from the sheet. If I understand right the listbox only refer to what row the selected is, not what the text is...

And then its a bit tricky. I have tried a code where it just add the list one more time, after the itmes has been removed. And that could work if it hadn't been for the second column which also has a text, this column is updated after every ticker has been moved from left to right.

What I would like to have is a function that would know what the text in the row that I delete, or remove.

This is what I have so far:
Code:
Private Sub cmdMoveToLeft_Click()

Dim i As Integer, j As Integer, k As Integer
Dim RowsStart As Integer
Dim LastRow As Long
Dim Rows As Integer

RowsStart = Me.ListBoxX.ListCount

If ListBoxX.ListIndex = -1 Then Exit Sub

    For i = ListBoxX.ListCount - 1 To 0 Step -1

    If ListBoxX.Selected(i) = True Then

'ListBox1.AddItem ListBox8.List(i)

        ListBoxX.RemoveItem i
        Dim lrLR As Long
        lrLR = Sheets("1HourHL").Range("X65536").End(xlUp).Row + 1
        
        Sheets("1HourHL").Range("X" & lrLR).Value = ListBoxX.Selected(i)
        
        End If

    Next i

End Sub


Kind regards
Espen
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi espenskeie, I am not sure I understand exactly what you are asking, but maybe this will help.

ListBox.Selected(i) returns only boolean values of True or False, yes/no.
ListBox.List(i) is used to return a value from the items in the list box when it is a multiselect listbox or combobox. If it is also a multicolumn list or combo box then the List(i, 2) for variable row, fixed column, or List(2, i) for fixed row variable column format is needed to return a value from whichever row or column you are dealing with.

This statement would not return a value from the listbox:

Sheets("1HourHL").Range("X" & lrLR).Value = ListBoxX.Selected(i)

But this one should return the value of the bound coulumn:

Sheets("1HourHL").Range("X" & lrLR).Value = ListBoxX.List(i).Value
 
Upvote 0
Great, that worked. Now I just have to use a search function to look for that value in range "W2:W" & lr and then find the rownumber, and delete both cells v and W, and move the whole list one step up.

:)

Thanks
Espen
 
Upvote 0
This should be close:

Coce:
Set c = Sheets("1HourHL").Range("W2:W" & lr).Find(Sheets("1HourHL").Range("X" & lrLR).Value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, -1).Resize(1, 2).Delete
End If
Code:
 
Upvote 0
Thanks, that was nice. I think that one work :)

I appreciate your help :)

Kind regards
Espen
 
Upvote 0

Forum statistics

Threads
1,203,728
Messages
6,057,005
Members
444,902
Latest member
ExerciseInFutility

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
Back
Top