From listbox click to textbox back to cell modify

Topic

New Reply

:confused:I need help making this work for editing listbox items. only the first item/textbox1/column A gets modified. i posted a sample file in another thread but i figure out it’s better to make a smaller request: Could someone please tell me what’s wrong with this code. it does work in a 2 colum listbox but not in this 8 column listbox. my rowsource is defined as : VendorList: =OFFSET(Vendors!$A$1,1,0,COUNTA(Vendors!$A:$A)-1,8)

Thank you.

Sample file: 46868-PO

Code:

Private Sub cmdVendorModify_Click() ‘change button for Vendors

Dim a As Byte


If ListBox4.ListIndex = -1 Then
MsgBox “Choose an item from the list!”, vbExclamation
Exit Sub
End If




If ListBox4.ListIndex  -1 Then
   
  With ListBox4


    Range(“VendorList”)((.ListIndex * 8) + 1) = Me.TextBox1.Value
    Range(“VendorList”)((.ListIndex * 8) + 2) = Me.TextBox2.Value
    Range(“VendorList”)((.ListIndex * 8) + 3) = Me.TextBox3.Value
    Range(“VendorList”)((.ListIndex * 8) + 4) = Me.TextBox4.Value
    Range(“VendorList”)((.ListIndex * 8) + 5) = Me.TextBox5.Value
    Range(“VendorList”)((.ListIndex * 8) + 6) = Me.TextBox6.Value
    Range(“VendorList”)((.ListIndex * 8) + 7) = Me.TextBox7.Value
    Range(“VendorList”)((.ListIndex * 8) + 8) = Me.TextBox8.Value


    End With


End If


Frame4.Visible = True
Call Main ‘Progress Bar
Frame4.Visible = False




TextBox13.Value = “”: ComboBox1.Value = “”
Label15.Caption = “”
ListBox4.RowSource = “VendorList”  ‘for refreshing vendors listbox
ListBox4.ListIndex = -1


For a = 1 To 8
Controls(“textbox” & a) = “”
Next


MsgBox “Item has been updated”
ThisWorkbook.Save
End Sub

Reply | Quote

Replies

Hi abouya

..see attached file.

I only made a few code adjustments.
To see my changes, search for this ‘<<<<< in the code

In the attached file, click the button to show the Form.
Make some changes to a Vendor record, and when you click the [Modify] button, the update will be seen on the worksheet [Vendors] and will also be seen in the Listbox.

zeddy

Reply | Quote

Zeddy,

A week of headache trying to figure out how to fix this is finally gone. I’m so grateful to you. Thank you so much, you really made by day.

Million thanks.

:cheers:

Reply | Quote

I hope you are doing great. Thanks so much for helping out last time with the file Called PO to be able to update the Vendor Information Listbox/rowsource using textboxes.

Unfortunately, there are few things that doesn’t work.

1.Trying to Modify or delete a Vendor after using the search frame. it looks like after searching for a vendor using the textbox and the combobox filter the modify or delete button recognizes it as ListIndex 0 for example while it should be ListIndex=10 for example.

Fixed (Just added WS in .cells for all search cases): 2.The other issue is the search filter. It work fine if the spreadsheet of the rowsource is active/on screen and doesn’t work if i’m on another spreadsheet (only Column A(Vendor Name) search filter work.
I tried looking at a solution to this issue for few days but failed since i’m still learning VBA.

I would really appreaciate if you can help me out with this issue.

Reply | Quote

Hi abouya

I’m checking your code for the search filter..

..will post back when I’ve tested this.

zeddy

Reply | Quote

Hi Zeddy,

attached is a userfom that it similar. I used its code in my userfom. The code in this userfom rely in Find function and doesn’t work if sheet is not active. I thought it might serve us to find a solution to my issue mentioned above.

Again, Thank you so much.

46948-New-Userform-

Reply | Quote

Hi abouya

I simplified your code for the [Search] button on your Form.
I tested the search, and it works when other sheets in the workbook are currently active.
see attached file.

The amended routine is Sub cmdsearch_Click()
I added comments to each line of code to help you follow it.

I modified your search pattern.
Previously, your search pattern would only look for matches that started with your search text entry.
The modified pattern will now look for your search text anywhere within the relevant cells.
For example, if you select Vendor from your dropdown, and enter corp as your search text, the listbox will show all vendor records that have corp anywhere within the Vendor name etc etc etc.

However, it would be even better to modify the search routine again to allow the User to use all pattern matching wildcards e.g.
? for any single character
# for any single digit (0–9)
[ charlist ] for any single character in charlist
[! charlist ] for any single character not in charlist
* for zero or more characters.

So, the User would then be able to enter search text like these examples..
B?T*
*LTD
####
1##
##9
etc etc etc

zeddy

Reply | Quote

Hi abouya

..my posted file in post#7 is based on the post#2 file.
..I’ll have a look at your new userform in post#6 a bit later when I get a chance.

zeddy

Reply | Quote

Hi abouya

..my posted file in post#7 is based on the post#2 file.
..I’ll have a look at your new userform in post#6 a bit later when I get a chance.

zeddy

The file in post 6 is not mine, i just wanted to show you that this file is able to modify or delete items successfully after doing a search. My file which is in post 2 works fine except that if you select an item after doing a search and try to modify it or delete it it doesn’t work good. It delete another row from the table. It’s like listindex gets lost after searching. Just search for A company name and after the listbox shows it, try to delete it or modify it. It will delete another item not the selected one.

That search function you added is great and i was defenitly thinking about it. Thank you so much Zeddy. Your help is much appreciated.

Thank you so much.

Reply | Quote

Hi abouya

The issue is that the listbox index doesn’t correspond to the actual Vendor record row number when you fetch records.
The first matching record found might be from row 426, and the second listbox record found might be from Vendor record datarow 572 etc.
So the solution is to have the current source row number for each Vendor record included in the listbox (hidden if required).

OK, in this attached version, I inserted a new column on your [Vendors] sheet.
This column has a formula =ROW() which gives the current row number for each of the Vendor records.
This column can be hidden.

The Form has been updated to bring the current row number for each Vendor record into the Listbox,
Now, when we use the [Search] button, it will have the actual current row numbers for the matching records found.
So when we delete or update a Vendor record selected from the Form’s Listbox, the correct record row number is used.

I also updated the code for the search-pattern-match.
So now, if you want to search for all records containing corp, you now enter *corp*
If you want to search for four-digit vendor keys that end with 5, use ###5 as your search text etc etc etc.

If you add a new record, the code adds the required formula to column
If you delete a record, the row() formula will update to show current row numbers for remaining records.

zeddy

Reply | Quote

what a great solution. Again, your code is the most elegant, smart and powerful code i ever seen. Thank you so much. you help is so much appreciated.

Reply | Quote