Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SELECT, ERASE, EDIT items in listbox
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SELECT, ERASE, EDIT items in listbox
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Multiple Items and return value for Dropdown or listbox | Excel Discussion (Misc queries) | |||
Problem using INDEX to select items in listbox | Excel Discussion (Misc queries) | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming | |||
Items in a Listbox | Excel Programming | |||
Listbox--moving items up or down | Excel Programming |