PDA

View Full Version : [SOLVED] List Box - Row Source



sheeeng
06-22-2005, 08:30 PM
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
06-22-2005, 09:10 PM
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
06-22-2005, 10:10 PM
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
06-23-2005, 12:53 AM
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
06-23-2005, 12:59 AM
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
06-23-2005, 01:55 AM
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
06-23-2005, 02:20 AM
Thnkas, xld. :thumb
xld, you mean your code will delete when clicked on it?
Where should i put it in?

sheeeng
06-23-2005, 02:27 AM
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
06-23-2005, 02:38 AM
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
06-23-2005, 02:38 AM
Sorry, xld. I can't use it. It cause error on my macro.


Post the workbook.

sheeeng
06-23-2005, 02:51 AM
Can you tell me how to work around with it?
Thanks.

Bob Phillips
06-23-2005, 03:27 AM
Can you tell me how to work around with it?
Thanks.

It works okay for me. See attached.

sheeeng
06-23-2005, 05:08 AM
Thanks for helping!
Can it delete only after I press the delete button?

Bob Phillips
06-23-2005, 07:10 AM
Thanks for helping!
Can it delete only after I press the delete button?

Yep.

sheeeng
06-23-2005, 08:40 AM
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
06-23-2005, 09:21 AM
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
06-23-2005, 06:54 PM
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
06-23-2005, 07:29 PM
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
06-23-2005, 07:44 PM
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
06-24-2005, 03:06 AM
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
06-24-2005, 03:13 AM
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
06-24-2005, 03:14 AM
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
06-24-2005, 07:02 AM
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: