Textbox spinbutton problem with duplicate entries

B

BungleNZ

Board Regular
Joined
Sep 9, 2008
Messages
220
Hi, I'm working on a userform in excel 2003 and have hit a bit of a brick wall.

I have a listbox on a userform that shows only unique entries (customers) which are populated off sheet1 (called Names). On the sheet itself, there are customer entries repeated when there is more than one contact stored. I have the listbox working fine to show each customer only once.

My problems comes in here:
On the userform I have a textbox (this textbox in turn will determine specific contact details to be shown in other textboxes for the contact displayed) with a spinbutton that I want to show each contact for a customer (only showing one at a time and change made with spinbutton). I just can't get this to work properly.

Anybody able to help?



<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank">http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="10" bgColor="#0c266b"><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left>Microsoft Excel - Book1</TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: 11.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" colSpan="10" bgColor="#d4d0c8"><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' value="Copy Formula" type=button name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="10" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" bgColor=white align=middle><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION selected value="03-343 0242">A1</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right>=</TD><TD bgColor=white align=left><INPUT value="Account Name" size=80 name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle" width="2%">

</TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>D</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>E</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>F</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>G</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>H</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>I</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Account Name</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Address 1</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Address 2</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Address 3</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Address 4</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Website</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Primary Contact Person</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Contact Type</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 0.5pt solid">Primary Phone</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer A</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">16 Straford Street</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Gore</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Mark Robinson</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">03-208 6750</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer A</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Steve Brown</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">345875433</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer A</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Timmy Jimmy</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">2937753</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer B</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">43 Hawkins Place</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Nelson</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">John Jones</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid"></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer C</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">574 Rumataka Drive</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">New Plymouth</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Steve Bellamy</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">455933222</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" align="middle" width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Customer D</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">4335 Montreal Street</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Sockburn</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Christchurch</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Craig Chappel</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">03-343 0242</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="10"><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left>Sheet1</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
You might want to use a ComboBox instead of a text box.
Code: 
Private Sub UserForm_Initialize()
    With Me
        With .ListBox1: Rem fill list box 
            .List = Range("A1:A10").Value
        End With

        With .ComboBox1
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
            .List = Me.ListBox1.List
        End With

        With .SpinButton1
            .Min = 0
            .Max = ComboBox1.ListCount - 1
        End With
    End With
End Sub

Private Sub SpinButton1_Change()
    With Me
        .ComboBox1.Value = .ListBox1.List(.SpinButton1.Value)
    End With
End Sub
 
Upvote 0
I like your combobox idea with just disabling the dropdown functionailty. There are a few issues with this coding though.

I currently have coding on userform_activate() that returns a list of unique entries into listbox1 (via a private function called UniqueItemList). I have modified your coding to allow for this and it seems to be working ok.

The problem is that the combobox is displaying values (customers) from range A instead of the values (contacts) which correspond to the correct customer in column G.

I've tried to taylor your code to make this work, but haven't had any luck.

Edit: As a side note, is there much difference between using userform_activate versus userform_initialize?

mikerickson said:
You might want to use a ComboBox instead of a text box.
Code: 
Private Sub UserForm_Initialize()
    With Me
        With .ListBox1: Rem fill list box 
            .List = Range("A1:A10").Value
        End With

        With .ComboBox1
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
            .List = Me.ListBox1.List
        End With

        With .SpinButton1
            .Min = 0
            .Max = ComboBox1.ListCount - 1
        End With
    End With
End Sub

Private Sub SpinButton1_Change()
    With Me
        .ComboBox1.Value = .ListBox1.List(.SpinButton1.Value)
    End With
End Sub
Click to expand...
 
Last edited:
Upvote 0
If you could post your code, I could take a look at it.
Do you want the Listbox populated from Column A and the Combobox to show the values from G? or are both from G?
 
Upvote 0
These are the two procedures that determine the contents of the listbox. Together, they allow you to type in a text box named "userfilter" to filter down the displayed listbox. There is also coding in the procedures to show only unique entries in the listbox (e.g. Customer A shows in the listbox only once.)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
So, to answer the questions in your post: I have got the listbox data displaying correctly. What I haven't been able to achieve is to have a listbox, combobox, or textbox (it doesn't matter what type) display the contact details (in column G) for each specific customer (Column A) with an attached spinbutton that will cycle through as many contacts that a customer has (only showing one contact at a time). Is it just a case of using some lookup coding to check and match the listbox1.value down range A on the sheet "Names" and returning the corresponding cells in range G?<o:p></o:p>
<o:p></o:p>
Thanks for your continued help<o:p></o:p>
<o:p></o:p>
Jamie<o:p></o:p>


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Userfilter_change()<br><SPAN style="color:#00007F">Dim</SPAN> Namelist, Entry<br>Customer.ListBox1.Clear<br><SPAN style="color:#00007F">With</SPAN> Sheets("Names")<br>    Namelist = .Range("Customers").Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>    .Comparemode = vbTextCompare<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Entry <SPAN style="color:#00007F">In</SPAN> Namelist<br>        <SPAN style="color:#00007F">If</SPAN> InStr(1, Entry, UserFilter.Value, 1) > 0 <SPAN style="color:#00007F">Then</SPAN> .Item(Entry) = Entry<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> .Count > 0 <SPAN style="color:#00007F">Then</SPAN> Customer.ListBox1.List = .keys<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()<br><SPAN style="color:#00007F">Dim</SPAN> Entry<br>Customer.ListBox1.Clear<br><SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>    .Comparemode = vbTextCompare<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Entry <SPAN style="color:#00007F">In</SPAN> Sheets("Names").Range("Customers").Value<br>        .Item(Entry) = Entry<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> .Count > 0 <SPAN style="color:#00007F">Then</SPAN> Customer.ListBox1.List = .keys<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


mikerickson said:
If you could post your code, I could take a look at it.
Do you want the Listbox populated from Column A and the Combobox to show the values from G? or are both from G?
Click to expand...
 
Upvote 0
I'm assuming that the named range Customers is column A.

This fills a two column list box, where the .Value is the customer name (from column A) and the .Text property is a delimited string of that customer's contatcts (from column G of the sheet)

Selecting a customer name from the list box, fills the combo box's .List with the contacts for that customer.

Pressing the spin button, changes the selection in the Combo Box.

Unfortunatly, my Mac doesn't support Dictionary objects, so I had to use Collections.
Code: 
Private Sub UserForm_Initialize()
    Dim nameList As New Collection
    Dim contactsColl As New Collection
    Dim contactColl As New Collection
    Dim Entry As Range
    Dim i As Long, j As Long
    Dim listArray() As String
    
    On Error Resume Next
    
    For Each Entry In Range("Customers")
        nameList.Add Item:=CStr(Entry.Value), key:=CStr(Entry.Value)
        Set contactColl = Nothing
        contactsColl.Add Item:=contactColl, key:=CStr(Entry.Value)
        contactsColl(CStr(Entry.Value)).Add Item:=Entry.Offset(0, 6).Value, key:=Entry.Offset(0, 6).Value
    Next Entry
    
    nameList.Remove vbNullString
    contactsColl.Remove vbNullString
    
    On Error GoTo 0
    
    Rem make 2d Array for list box
    ReDim listArray(1 To nameList.Count, 1 To 2)
        For i = 1 To nameList.Count
            listArray(i, 1) = nameList(i)
            For j = 1 To contactsColl(i).Count
                listArray(i, 2) = listArray(i, 2) & vbCr & contactsColl(i)(j)
            Next j
            listArray(i, 2) = Mid(listArray(i, 2), 2)
        Next i
        
    With Me
        With .ListBox1
            .ColumnCount = 2
            .BoundColumn = 1
            .TextColumn = 2
            .ColumnWidths = ";0"
            .List = listArray
        End With
        .ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
    End With
End Sub

Private Sub ListBox1_Change()
    With Me
        If -1 < .ListBox1.ListIndex Then
            .ComboBox1.Clear
            .ComboBox1.List = Split(.ListBox1.Text, vbCr)
            .ComboBox1.ListIndex = 0
        End If
        With .SpinButton1
            .Min = 0
            .Max = Me.ComboBox1.ListCount - 1
            .Value = 0
            .SetFocus
        End With
    End With
End Sub

Private Sub SpinButton1_Change()
    With Me
        .ComboBox1.ListIndex = .SpinButton1.Value
    End With
End Sub
 
Last edited:
Upvote 0
Hi,

You're correct, the named range "Customers" is a dynamic range starting at A2 down to last entry in column A.

Forgive me if I get any of the following details wrong, I'm pretty new to VBA.

Do I use this coding in addition to the coding I have already placed to fill the listbox? If it's in place of the coding I have, then I'm not sure this procedure will work. My current coding provides a quick search filter (a textbox) that filters the listbox as you type, and the listbox itself has coding to show only unique entries.

Also, I don't really want to return a 2 column list box, as I don't need to show the contacts in this. Ideally the listbox is only the customers, and the combobox shows only the contacts for the listbox.value.

Thanks

Jamie


mikerickson said:
I'm assuming that the named range Customers is column A.

This fills a two column list box, where the .Value is the customer name (from column A) and the .Text property is a delimited string of that customer's contatcts (from column G of the sheet)

Selecting a customer name from the list box, fills the combo box's .List with the contacts for that customer.

Pressing the spin button, changes the selection in the Combo Box.

Unfortunatly, my Mac doesn't support Dictionary objects, so I had to use Collections.
Code: 
Private Sub UserForm_Initialize()
    Dim nameList As New Collection
    Dim contactsColl As New Collection
    Dim contactColl As New Collection
    Dim Entry As Range
    Dim i As Long, j As Long
    Dim listArray() As String
 
    On Error Resume Next
 
    For Each Entry In Range("Customers")
        nameList.Add Item:=CStr(Entry.Value), key:=CStr(Entry.Value)
        Set contactColl = Nothing
        contactsColl.Add Item:=contactColl, key:=CStr(Entry.Value)
        contactsColl(CStr(Entry.Value)).Add Item:=Entry.Offset(0, 6).Value, key:=Entry.Offset(0, 6).Value
    Next Entry
 
    nameList.Remove vbNullString
    contactsColl.Remove vbNullString
 
    On Error GoTo 0
 
    Rem make 2d Array for list box
    ReDim listArray(1 To nameList.Count, 1 To 2)
        For i = 1 To nameList.Count
            listArray(i, 1) = nameList(i)
            For j = 1 To contactsColl(i).Count
                listArray(i, 2) = listArray(i, 2) & vbCr & contactsColl(i)(j)
            Next j
            listArray(i, 2) = Mid(listArray(i, 2), 2)
        Next i
 
    With Me
        With .ListBox1
            .ColumnCount = 2
            .BoundColumn = 1
            .TextColumn = 2
            .ColumnWidths = ";0"
            .List = listArray
        End With
        .ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
    End With
End Sub
 
Private Sub ListBox1_Change()
    With Me
        If -1 < .ListBox1.ListIndex Then
            .ComboBox1.Clear
            .ComboBox1.List = Split(.ListBox1.Text, vbCr)
            .ComboBox1.ListIndex = 0
        End If
        With .SpinButton1
            .Min = 0
            .Max = Me.ComboBox1.ListCount - 1
            .Value = 0
            .SetFocus
        End With
    End With
End Sub
 
Private Sub SpinButton1_Change()
    With Me
        .ComboBox1.ListIndex = .SpinButton1.Value
    End With
End Sub
Click to expand...
 
Upvote 0
The routine above, which goes in the userform's code module, would replace the existing event code for those events.

It sounds like you are proposing that every time a selection is made from the listbox, that the range be filtered and the results put in the ComboBox.

The approach the above code takes is to perform that filter as it is removing the duplicates from the customer list. The results of the filter are stored in the second column of the listbox. The user never sees this. The filter is performed once rather then every time the user makes a selection from the list box.

If the userform changes the contacts list, "filtering with each list box selection" sounds like the more robust of those two approaches.
If the contact list is static for the life of the userform, filtering once improves speed.
 
Upvote 0
I think I mustn't be clarifying my requirements correctly, so thank you for your patience.

I'll try and give you a clear, concise definition of what I've done and what I'm trying to do (it's a shame I can't paste an image of the userform.)

In terms of relevant coding, I have a listbox, a textbox and a combobox with spinbutton.

The userform (Customer), has coding to get all unique items from the dynamic range "Customers" (posted previously) and uses this range as the input for listbox1.

The textbox (UserFilter) has a change event that contains vba code that on typing in this box, filters the list based on the letters in the box (similar to how the i-tunes search box works if you've used that.) - (i have posted this code earlier as well.)

The combobox is what you're helping me get working correctly. The code you pasted does go part of the way to getting this working - it just is not working correctly due to my other listboxfill and textbox based filter requirements.

I thought that maybe the solution would be to use a vba find to look down column A for the listbox1.value, declare a variable for all the cell values (contacts) in collumn G that correspond to this listbox1.value and use the variable as the input range for the combobox. I'm not sure if this would work though.

I hope this all makes sense to you.

Cheers

Jamie


mikerickson said:
The routine above, which goes in the userform's code module, would replace the existing event code for those events.

It sounds like you are proposing that every time a selection is made from the listbox, that the range be filtered and the results put in the ComboBox.

The approach the above code takes is to perform that filter as it is removing the duplicates from the customer list. The results of the filter are stored in the second column of the listbox. The user never sees this. The filter is performed once rather then every time the user makes a selection from the list box.

If the userform changes the contacts list, "filtering with each list box selection" sounds like the more robust of those two approaches.
If the contact list is static for the life of the userform, filtering once improves speed.
Click to expand...
 
Upvote 0
Bump anyone? I think Mike's code looks really good and should work, I'm just having a lot of trouble modifying it to accommodate my other requirements.

If anyone can help I really would appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
832
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
318
VBAEXCELNew
V
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,982
Messages
6,122,580
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top