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>
 
Sort by date Sort by votes
Something like this for the full version:
Code: 
Private Sub ListBox1_Click()
   With Me.ListBox1
      If .Value <> "" Then FillContactList .Value
   End With
End Sub
Private Sub Userfilter_change()
   FillCustomerList Userfilter.Value
End Sub
Private Sub UserForm_Activate()
   FillCustomerList
End Sub
Private Sub FillCustomerList(Optional strFilter As String = "")
   Dim Namelist, Entry
   Dim objDic As Object
   Me.ListBox1.Clear
   Namelist = Sheets("Names").Range("Customers").Value
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      For Each Entry In Namelist
         If Len(strFilter) > 0 Then
            If InStr(1, Entry, strFilter, 1) > 0 Then .Item(Entry) = Entry
         Else
            .Item(Entry) = Entry
         End If
      Next Entry
      If .Count > 0 Then Me.ListBox1.List = .keys
   End With
End Sub
Private Sub FillContactList(strCustomer As String)
   Dim Namelist, ContList
   Dim lngIndex As Long
   Dim objDic As Object
   Me.ComboBox1.Clear
   With Sheets("Names").Range("Customers")
      Namelist = .Value
      ' Contacts in col G, 6 cols to right of customers
      ContList = .Offset(, 6).Value
   End With
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      ' Loop through customer array (which is 2-dimensional as assigned from worksheet range)
      For lngIndex = LBound(Namelist, 1) To UBound(Namelist, 1)
         If StrComp(Namelist(lngIndex, 1), strCustomer, vbTextCompare) = 0 Then _
                                    .Item(lngIndex) = ContList(lngIndex, 1)
      Next lngIndex
      If .Count > 0 Then Me.ComboBox1.List = .items
   End With
End Sub
should work, I think.
HTH
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
That coding is excellent. Just a couple of things to tidy up if you could help.

I want to disable the dropdown button so the combobox looks just like a textbox (i.e. only one contact can be seen at a time.)

Because of this, it would be good if the combobox automatically displayed the first contact for a customer in a range.

I am also trying to tie this comboxbox with a spinbutton that cycles through the combobox entries.

I've got the following code from an earlier post by Mike, but just can't get it to work properly with your excellent code.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_activate()<br>    <SPAN style="color:#00007F">With</SPAN> Me<br>        <SPAN style="color:#00007F">With</SPAN> .ListBox1 <SPAN style="color:#007F00">' Presumably I don't want this procedure anymore</SPAN><br>            .List = Range("A1:A10").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>        <SPAN style="color:#00007F">With</SPAN> .ComboBox1<br>            .ShowDropButtonWhen = fmShowDropButtonWhenNever<br>            .List = Me.ListBox1.List<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>        <SPAN style="color:#00007F">With</SPAN> .SpinButton1<br>            .Min = 0<br>            .Max = ComboBox1.ListCount - 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><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><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SpinButton1_Change()<br>    <SPAN style="color:#00007F">With</SPAN> Me<br>        .ComboBox1.Value = .ListBox1.List(.SpinButton1.Value)<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>



rorya said:
Something like this for the full version:
Code: 
Private Sub ListBox1_Click()
   With Me.ListBox1
      If .Value <> "" Then FillContactList .Value
   End With
End Sub
Private Sub Userfilter_change()
   FillCustomerList Userfilter.Value
End Sub
Private Sub UserForm_Activate()
   FillCustomerList
End Sub
Private Sub FillCustomerList(Optional strFilter As String = "")
   Dim Namelist, Entry
   Dim objDic As Object
   Me.ListBox1.Clear
   Namelist = Sheets("Names").Range("Customers").Value
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      For Each Entry In Namelist
         If Len(strFilter) > 0 Then
            If InStr(1, Entry, strFilter, 1) > 0 Then .Item(Entry) = Entry
         Else
            .Item(Entry) = Entry
         End If
      Next Entry
      If .Count > 0 Then Me.ListBox1.List = .keys
   End With
End Sub
Private Sub FillContactList(strCustomer As String)
   Dim Namelist, ContList
   Dim lngIndex As Long
   Dim objDic As Object
   Me.ComboBox1.Clear
   With Sheets("Names").Range("Customers")
      Namelist = .Value
      ' Contacts in col G, 6 cols to right of customers
      ContList = .Offset(, 6).Value
   End With
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      ' Loop through customer array (which is 2-dimensional as assigned from worksheet range)
      For lngIndex = LBound(Namelist, 1) To UBound(Namelist, 1)
         If StrComp(Namelist(lngIndex, 1), strCustomer, vbTextCompare) = 0 Then _
                                    .Item(lngIndex) = ContList(lngIndex, 1)
      Next lngIndex
      If .Count > 0 Then Me.ComboBox1.List = .items
   End With
End Sub
should work, I think.
HTH
Click to expand...
 
Upvote 0
Shouldn't be a problem but it will have to be tomorrow as it's late here and my brain is fried from ante-natal classes! :)
 
Upvote 0
Here you go - note that I would simply disable the combobox's dropdown in Design View (change the ShowDropButtonWhen property in the Properties window to 0) rather than changing it at runtime:
Code: 
Private Sub ListBox1_Click()
   With Me.ListBox1
      If .Value <> "" Then FillContactList .Value
   End With
End Sub
Private Sub SpinButton1_Change()
   Me.ComboBox1.ListIndex = Me.SpinButton1.Value
End Sub
Private Sub Userfilter_change()
   FillCustomerList Userfilter.Value
End Sub
Private Sub UserForm_Activate()
   FillCustomerList
End Sub
Private Sub FillCustomerList(Optional strFilter As String = "")
   Dim Namelist, Entry
   Dim objDic As Object
   Me.ListBox1.Clear
   Namelist = Sheets("Names").Range("Customers").Value
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      For Each Entry In Namelist
         If Len(strFilter) > 0 Then
            If InStr(1, Entry, strFilter, 1) > 0 Then .Item(Entry) = Entry
         Else
            .Item(Entry) = Entry
         End If
      Next Entry
      If .Count > 0 Then Me.ListBox1.List = .keys
   End With
End Sub
Private Sub FillContactList(strCustomer As String)
   Dim Namelist, ContList
   Dim lngIndex As Long
   Dim objDic As Object
   Me.ComboBox1.Clear
   With Sheets("Names").Range("Customers")
      Namelist = .Value
      ' Contacts in col G, 6 cols to right of customers
      ContList = .Offset(, 6).Value
   End With
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      ' Loop through customer array (which is 2-dimensional as assigned from worksheet range)
      For lngIndex = LBound(Namelist, 1) To UBound(Namelist, 1)
         If StrComp(Namelist(lngIndex, 1), strCustomer, vbTextCompare) = 0 Then _
                                    .Item(lngIndex) = ContList(lngIndex, 1)
      Next lngIndex
      If .Count > 0 Then
         With Me.ComboBox1
            ' fill combo
            .List = objDic.items
            ' select first item
            .ListIndex = 0
         End With
         With Me.SpinButton1
            ' reset min and max values for spinbutton
            .Min = 0
            .Max = Me.ComboBox1.ListCount - 1
            ' set spinbutton to first item
            .Value = 0
         End With
      End If
   End With
End Sub
 
Upvote 0
Brilliant, thank you very much for all your help Rory (and others). The code works very well.

Cheers

Jamie


rorya said:
Here you go - note that I would simply disable the combobox's dropdown in Design View (change the ShowDropButtonWhen property in the Properties window to 0) rather than changing it at runtime:
Code: 
Private Sub ListBox1_Click()
   With Me.ListBox1
      If .Value <> "" Then FillContactList .Value
   End With
End Sub
Private Sub SpinButton1_Change()
   Me.ComboBox1.ListIndex = Me.SpinButton1.Value
End Sub
Private Sub Userfilter_change()
   FillCustomerList Userfilter.Value
End Sub
Private Sub UserForm_Activate()
   FillCustomerList
End Sub
Private Sub FillCustomerList(Optional strFilter As String = "")
   Dim Namelist, Entry
   Dim objDic As Object
   Me.ListBox1.Clear
   Namelist = Sheets("Names").Range("Customers").Value
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      For Each Entry In Namelist
         If Len(strFilter) > 0 Then
            If InStr(1, Entry, strFilter, 1) > 0 Then .Item(Entry) = Entry
         Else
            .Item(Entry) = Entry
         End If
      Next Entry
      If .Count > 0 Then Me.ListBox1.List = .keys
   End With
End Sub
Private Sub FillContactList(strCustomer As String)
   Dim Namelist, ContList
   Dim lngIndex As Long
   Dim objDic As Object
   Me.ComboBox1.Clear
   With Sheets("Names").Range("Customers")
      Namelist = .Value
      ' Contacts in col G, 6 cols to right of customers
      ContList = .Offset(, 6).Value
   End With
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      ' Loop through customer array (which is 2-dimensional as assigned from worksheet range)
      For lngIndex = LBound(Namelist, 1) To UBound(Namelist, 1)
         If StrComp(Namelist(lngIndex, 1), strCustomer, vbTextCompare) = 0 Then _
                                    .Item(lngIndex) = ContList(lngIndex, 1)
      Next lngIndex
      If .Count > 0 Then
         With Me.ComboBox1
            ' fill combo
            .List = objDic.items
            ' select first item
            .ListIndex = 0
         End With
         With Me.SpinButton1
            ' reset min and max values for spinbutton
            .Min = 0
            .Max = Me.ComboBox1.ListCount - 1
            ' set spinbutton to first item
            .Value = 0
         End With
      End If
   End With
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
671
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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