Excel Forum

ListBox Doesnt show last line information in Textboxes

Steven Behr

Member

Members

Power Query

Forum Posts: 85

Member Since:
January 30, 2020

Offline

May 28, 2020 - 8:05 pm

Hi,

Im making a new administration Login module in VBA and I noticed in my listbox in frmAdministration in the listbox shows a list of

people that have got access to various sheets. When I Double click through to various people it populates the information to the combo boxes below. When I select the second last line item (Cody) no information shows up in the combo box except for the ID number

In the Login Tab is where the information is taken where an advanced filter in AJ8 CTL+SHIFT+L. The list range is also on the Login Tab from G7:Q35 and the copy to is AM7:AW7.

Even when I input Cody in AJ8 CTL+Shift+L - it will show in the listbox but still when I click on it - no information in the combo boxes

I also tried another TAB with the same features but received the same information.

When you open the file the Username is : Betty Passcode:1111

I have disables passwords in the workbook, but if the login tab is still with password. Then go to Unprotect "Online".

Hopefully you can tell me what is going on here

Steve

See Answer

Philip Treacy

Admin

Forum Posts: 1514

Member Since:
October 5, 2010

Offline

May 29, 2020 - 1:16 pm

Hi Steve,

Your code is looking for the first occurrence of the ID:

Set findvalue = Sheet19.Range("Q:Q").Find(What:=ID, LookIn:=xlValues).Offset(0, -10)

Because there's a MAX function in Q6 returning the largest ID (8) that's what is found. So the range returned by your Find/Offset is G6 when you actually want G15.

You can remove that MAX function to fix it, or don't reference the entire column in your Find.

Also, your triggering the code that does all of this with a lstLookup_DblClick which then looks for the selected row.

But it is possible to select more than 1 row, and to even have no rows selected but still double click. Your code should take this into account.

Regards

Phil