SELECT, ERASE, EDIT items in listbox
 

Besides the multiple access in excel(still havn't solve the problem..
), I got another question want to throw to this group.. :<

I got 2 button in a userform... 1 is Erase button, and 1 is Edi
button. Both of this 2 button will delete the row that I choose i
listbox, how am I going to program this 2 button? Below is the cod
that used to select item and display which item u choose... But I dunn
how to delete the row that I choose... The rowsource for the listbox i
"ListBox1.RowSource = "Sheet1!range" "



Private Sub EraseButton_Click()
Msg = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then _
Msg = Msg & ListBox1.List(i) & vbCrLf
Next i
MsgBox "Are you sure u wana delete this Selected row: " & _ vbCrL
& Msg
...
...
End Sub

Please somebody can help me to program this?

And for the Edit button, I want to do such that when I select the ro
in listbox, and click on edit button, the data that stored in th
spreadsheet will appear in the textbox in the userform respectively...


Hope to hear from others.... as I already took so much time in doi
such a userform...My boss is unhappy with tat... :

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

really no one can help me on this?

:

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Sorry.... But really nobody can solve my problem..

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Perhaps anyone got any links that can sent to me for my reference..?

I'm desperately need it.... THanks..

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

http://tinyurl.com/25nvq

--
Regards,
Tom Ogilvy


"unplugs " wrote in message
...
Perhaps anyone got any links that can sent to me for my reference..?

I'm desperately need it.... THanks...


---
Message posted from http://www.ExcelForum.com/

SELECT, ERASE, EDIT items in listbox
 

Thanks Tom! Anyway, I had work it out wif the solution u provide las
time..

Private Sub CommandButton1_Click()
'Delete Selection
Set rng = Range(ListBox1.RowSource)
idex = ListBox1.ListIndex
If idex = -1 Then Exit Sub
ListBox1.RowSource = ""
rng(idex + 1).EntireRow.Delete
Set rng1 = Worksheets("Sheet1").Range("A1").CurrentRegion
Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1)
ListBox1.RowSource = rng1.Address(external:=True)
End Sub

Your code works great... And it can delete my selected items i
listbox! Anyway, the problem is, after I delete the items in a row, al
of the range that I define had modified!

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),10)
change to the below:

=OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!$A:$A),10)

After that, my program gave me error message because of the range ha
been modified...! U got any idea of this? Or anyone got any idea abou
this

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Happy Weekend!

I'm very pleased coz can log in to this forum now.. Since this foru
had been closed for yesterday for upgrading purpose..

I hope somebody can help me on this matter... I'm wondering why
execute the code that I stated above, all of the range that I defin
will change automatically..?

Can somebody explain to me why I delete an item from a listbox, it wil
change my range from $A$1 to !#REF! ??

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Private Sub EraseButton_Click()
response = MsgBox(" delete " & ListBox1, vbOKCancel)
If response = vbCancel Then Exit Sub
Range("RangeName").Select
For Each c In Range("RangeName")
If c.Value = ListBox1 Then
c.EntireRow.Delete
Exit For
End If
Next
End Sub


I had changed my code to this... And It works great! Anyway, th
problem of automatically change my define Range still happen when
select the first item in my listbox to erase. Other then the firs
item, it works great.... Why will it happen? Is it any bug in the code

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Private Sub EditButton_Click()

response = MsgBox(" Edit this record? ", vbOKCancel)
If response = vbCancel Then Exit Sub

With Me.ListBox1
Me.ComboBox1 = .List(.ListIndex, 0)
Me.TextProjectName = .List(.ListIndex, 1)
Me.TextProjectElement = .List(.ListIndex, 2)
Me.TextHoursSpent = .List(.ListIndex, 3)
End With

End Sub


The above is the code that allow me to placed the items that I selecte
in the userform in the respective textbox when I click on Edit button
Anyway, when I click on "Submit" button, it will treat it as anothe
new entries, and add it to my spreadsheet.

How am I goin to do so that user can edit their entries

--
Message posted from http://www.ExcelForum.com

SELECT, ERASE, EDIT items in listbox
 

Ok...I figure out 1 solution... That is, when user click on the EDIT
button, it will delete the row of the previous entries, before the user
submit the editted entries.

Any solution besides this..? I think this is not the best way to do
so...

About the Erase method, still no one can solve for me.. ? When I erase
the first entries in the listbox, it will change all my defined range
from:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),10)
change to the below:

=OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!$A:$A),10)

I hope someone is willing to shed the light for me...

Thanks...


---
Message posted from http://www.ExcelForum.com/

SELECT, ERASE, EDIT items in listbox
 

I still can't solve my Erase Problem.... Any suggestion.. ?

When I erase the first row of my entries, it will messed up all of m
define range...
:<

Hope someone can share his knowledge here.

--
Message posted from http://www.ExcelForum.com