List Box - Row Source

[sheeeng]

Hi all,

Can I use VBA macro code to change the ListBox properties of RowSource, ColumnCount, Bound Column and ColumnTitle?
It can read the data form Sheet1 automatically until the last row.

Thanks.

[sheeeng]

Hi all,

Can I use VBA macro code to change the ListBox properties of RowSource, ColumnCount, Bound Column and ColumnTitle?
It can read the data form Sheet1 automatically until the last row.

Thanks.

Well, I found the answers. Thank, BTW.



Private Sub UserForm_Initialize()
With UserForm1
.ListBox1.BoundColumn = 1
.ListBox1.ColumnCount = 1
.ListBox1.ColumnHeads = True
.ListBox1.RowSource = "Sheet1!A2:A12"
.ListBox1.ListStyle = fmListStyleOption
End With
End Sub


Could anyone help me with the last row needs? Thx.
:beerchug:

[johnske]

Hi sheeeng,

What do you mean about the last row? (it works) unless you don't want option buttons


Private Sub UserForm_Initialize()
With Sheet1
With UserForm1.ListBox1
.BoundColumn = 1
.ColumnCount = 1
.ColumnHeads = True
.RowSource = "A2:A12"
'.ListStyle = fmListStyleOption
.ListStyle = fmListStylePlain '< alternative
End With
End With
End Sub

HTH,
John

[sheeeng]

Thanks, Johnske.

Here an extract from Brandtrock (http://www.vbaexpress.com/forum/member.php?u=230)'s KB. :thumb
Thanks, Brandtrock (http://www.vbaexpress.com/forum/member.php?u=230).:friends:



Function xlLastRow(Optional WorksheetName As String) As Long
' find the last populated row in a worksheet
If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If
With Worksheets(WorksheetName)
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function


This suit my need.
Thanks.:hi:

[sheeeng]

Can anyone help me with this? :dunno



If (UserForm1.ListBox1.Value <> vbNullString) Then
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=UserForm1.ListBox1.Value
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
MsgBox "Item Deleted"
.AutoFilterMode = False
If (xlLastRow("Sheet1") = 1) Then
UserForm1.ListBox1.RowSource = "Sheet1!A2:A2"
Else
UserForm1.ListBox1.RowSource = "Sheet1!A2:A" & xlLastRow("Sheet1")
End If
End With
Else
MsgBox "Please Select A List Box Item For Delete"
End If



It deletes all the the same name value in column A. How to avoid it? I want it to delete just the data I have selected.

eg.

ListBox1
--------
Lemon <------ delete this one
Grape
Orange
Lemon <------ keep this one

The above code delete both entry, but I just want to select and delete the selected one only.

Please advise.

Thanks.:)

[Bob Phillips]

Can anyone help me with this? :dunno



If (UserForm1.ListBox1.Value <> vbNullString) Then
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=UserForm1.ListBox1.Value
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
MsgBox "Item Deleted"
.AutoFilterMode = False
If (xlLastRow("Sheet1") = 1) Then
UserForm1.ListBox1.RowSource = "Sheet1!A2:A2"
Else
UserForm1.ListBox1.RowSource = "Sheet1!A2:A" & xlLastRow("Sheet1")
End If
End With
Else
MsgBox "Please Select A List Box Item For Delete"
End If

It deletes all the the same name value in column A. How to avoid it? I want it to delete just the data I have selected.

eg.

ListBox1
--------
Lemon <------ delete this one
Grape
Orange
Lemon <------ keep this one

The above code delete both entry, but I just want to select and delete the selected one only.

Please advise.

Thanks.:)

Here is one way using a different approiach


Private fEnableEvents As Boolean

Private Sub ListBox1_Click()
If fEnableEvents Then
fEnableEvents = False
With Me.ListBox1
Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
End With
fEnableEvents = True
End If
End Sub

Private Sub UserForm_Activate()
fEnableEvents = True
End Sub

[sheeeng]

Thnkas, xld. :thumb
xld, you mean your code will delete when clicked on it?
Where should i put it in?

[sheeeng]

Sorry, xld. I can't use it. It cause error on my macro.


Error here...


Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete

in debug...
RowSource = Sheet1!A2:C2
ListIndex=0

Thanks for helping.

[Bob Phillips]

xld, you mean your code will delete when clicked on it?
Where should i put it in?

Yes, clicking the item will delet it from the source list and update tyhe listbox. It is an event associated with the listbox and so goes in the userform code module.

Not the setting of the flag in the activate event, very important.

[Bob Phillips]

Sorry, xld. I can't use it. It cause error on my macro.


Post the workbook.

[sheeeng]

Can you tell me how to work around with it?
Thanks.

[Bob Phillips]

Can you tell me how to work around with it?
Thanks.

It works okay for me. See attached.

[sheeeng]

Thanks for helping!
Can it delete only after I press the delete button?

[Bob Phillips]

Thanks for helping!
Can it delete only after I press the delete button?

Yep.

[sheeeng]

Great !! :thumb :thumb :thumb

But the empty row still exist in list box after delete.
The Year, Month, Day must be the column heads. But later pushed downed after delete.

How to prevent Column A,B,C from appear as column title? :doh:
Can we delete the empty row in list box after delete an item? :doh:

Thanks. :beerchug:

[Bob Phillips]

But the empty row still exist in list box after delete.
The Year, Month, Day must be the column heads. But later pushed downed after delete.

How to prevent Column A,B,C from appear as column title?
Can we delete the empty row in list box after delete an item?

Is this what ou mean?

[sheeeng]

Thanks, xld. :friends: You are marvelous! :thumb
You have the delete part did correctly as what I need. :thumb :clap:

But only the column title with "Year", "Month", " Day" had been removed.

Can you include that also as column title? (As shown in screen shot below.)
The column title must be shown all times, regradless of that process had been done to the data below it. :help

Thanks. :yes

[sheeeng]

Ok. I got the partial solutions.
It is solved by setting the ColumnHeads to True in VBE for ListBox1.

But when I add the Column A, B, C displayed out in Listbox again. How to prevent this?

Thanks.

[sheeeng]

Solved partially. I found it! :rotlaugh:

Just change to this line in Userform codes. (A1 changed to A2)


UserForm1.ListBox1.RowSource = "Sheet1!A2:C" & strLastRow


BUT, the column A, B,C still appear after all data deleted?
Any help on this? :banghead:


Thanks a lot, xld! :friends: You are a valuable helping in VBAExpress!!

[Bob Phillips]

Ok. I got the partial solutions.
It is solved by setting the ColumnHeads to True in VBE for ListBox1.

But when I add the Column A, B, C displayed out in Listbox again. How to prevent this?

Thanks.

Yes I only commented that out as it seemd odd. I obviously misunderstood what you wanted.

[Bob Phillips]

Just change to this line in Userform codes. (A1 changed to A2)


UserForm1.ListBox1.RowSource = "Sheet1!A2:C" & strLastRow


In the code that I gave you, I had already done that.

[Bob Phillips]

BUT, the column A, B,C still appear after all data deleted?
Any help on this?

Replace this code


Private Sub cmdDel_Click()
With Me.ListBox1
If .ListIndex >= 0 Then
Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
.RowSource = "Sheet1!A2:C" & xlLastRow("Sheet1")
End If
End With
End Sub

with this



Private Sub cmdDel_Click()
With Me.ListBox1
If .ListIndex >= 0 Then
Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
If .ListCount = 1 Then
.ColumnHeads = False
.RowSource = ""
Else
.RowSource = "Sheet1!A2:C" & xlLastRow("Sheet1")
End If
End If
End With
End Sub

[sheeeng]

Yes I only commented that out as it seemd odd. I obviously misunderstood what you wanted.

Nevermind bout that! It is good to know that you are a very helpful person. :thumb
I appreciate it.

Thanks. :hi: :friends: