ComboBox filter to textboxes

A

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,286
Office Version
  1. 2013
Platform
  1. Windows
<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=150 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=50 height=17>A</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=50></TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=50>P</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table1</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table2</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table3</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table4</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table5</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table6</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table7</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table8</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table1</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table2</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table3</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table4</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table1</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table2</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table3</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table4</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table5</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Table6</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">area3</TD></TR></TBODY></TABLE><!-- / message --><!-- edit note -->


Good Day,
Could somebody help me in regards given table above?
Column P datas populates by a combobox.
I have 20 textboxes all the way down on my userform.
Is it possible to filter the datas from column P and put the column A values into the textboxes from top the down?
Many Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hi, Its not clear what you are trying to do.
Q(1) Is your combobox on the Userform.
Q(2) Is the data in the combobox, Column "P" values
If you could explain how the procedure would work.
i.e. Select item from combobox, Selection then entered in Text Box etc.
It not clear what values from column"A" would go in what TextBox.
You only have 18 values and there are 20 textboxes.
Please give Example of how it would work.
Mick
 
Upvote 0
Hi Mick,
Tanks for ur reply,
The combobox on userform.
Worksheet(0800) datast column (A1:P700)
Combobox add item values are located on column P
First 20 textboxes or less should fill with column A values when the column P value selected by combobox then rest 380 textboxes will be load with related informations.
For me what is important the first 20 textboxes from textbox1-textbox20
should filterby a combobox.
Hope u can help me coz that is the last part of my project.
Many Thanks
 
Upvote 0
Hi, I'm still not clear.
If the combobox is filled from column "P" then it will contain duplicates saying either Area1, 2 or 3.
In trying to understand , I am thinking you want only the uniques Area Numbers from column "P" in the combobox.
When one of these Areas is selected, then the "Table" numbers relating to the "Area" number selected will show, one in each Text Box from column "A".
This could be any number of "Table Numbers", in the text boxes up to 20 individual Table numbers, one in each textbox, depending on column "A" content.
Is that correct.
Mick
 
Upvote 0
MickG said:
Hi, I'm still not clear.
If the combobox is filled from column "P" then it will contain duplicates saying either Area1, 2 or 3.
In trying to understand , I am thinking you want only the uniques Area Numbers from column "P" in the combobox.
When one of these Areas is selected, then the "Table" numbers relating to the "Area" number selected will show, one in each Text Box from column "A".
This could be any number of "Table Numbers", in the text boxes up to 20 individual Table numbers, one in each textbox, depending on column "A" content.
Is that correct.
Mick
Click to expand...

Code: 
[COLOR=black] Private Sub UserForm_Initialize()[/COLOR]
list.ComboBox1.AddItem ("Area1")
end Sub
ComboBox not filled with column P ... The areas added one by one and shows on the code above.
What i want ,if the area1 has selected from combobox1 the 7 tables from from that area will fill into the textboxes as table1-2-3.....7.
Thanks
 
Last edited:
Upvote 0
Mick ,
i will make it more easier.
I will create a new worksheet named locations.
Every area i will put by column.
Column A A1 will be location(area1) column B all
the way down table numbers.then i can create for the other columns later.
 
Upvote 0
Try this:-
This is a combobo1 "Change_ event"
Code: 
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ctrl [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ctrl [COLOR="Navy"]In[/COLOR] Me.Controls
        [COLOR="Navy"]If[/COLOR] c <= 20 And TypeName(Ctrl) = "TextBox" And Ctrl.value <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            Ctrl.value = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ctrl
        c = 0
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] ComboBox1 <> "" And Dn.Offset(, 15) = ComboBox1 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            Me.Controls("TextBox" & c).Object.value = Dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick,
Greatly appreciated,
The code u had provided works perfect.

__If u don't mind i have one more think to ask in regards "textbox1=("")
How can i make this event easier if im going to erase the values from textboxes21 till textbox400 ?
Thanks again

 
Upvote 0
If you want to empty all the "TextBoxes" at the start of the code, alter the first bit of the code as below.
Code: 
For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Ctrl.value = ""
        End If
    Next Ctrl
Mick
 
Upvote 0
MickG said:
Try this:-
This is a combobo1 "Change_ event"
Code: 
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Ctrl [COLOR=navy]As[/COLOR] Control
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ctrl [COLOR=navy]In[/COLOR] Me.Controls
        [COLOR=navy]If[/COLOR] c <= 20 And TypeName(Ctrl) = "TextBox" And Ctrl.value <> "" [COLOR=navy]Then[/COLOR]
            c = c + 1
            Ctrl.value = ""
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ctrl
        c = 0
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] ComboBox1 <> "" And Dn.Offset(, 15) = ComboBox1 [COLOR=navy]Then[/COLOR]
            c = c + 1
            Me.Controls("TextBox" & c).Object.value = Dn
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...


Code: 
Dim ws As Worksheet, rngFind As Range
Set ws = ThisWorkbook.Sheets("0800")
Set rngFind = ws.Range("a:a").Find(what:=Me.TextBox1.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
Me.TextBox21.Value = rngFind.Offset(0, 11).Value
Me.TextBox22.Value = rngFind.Offset(0, 12).Value
Me.TextBox23.Value = rngFind.Offset(0, 13).Value
Me.TextBox24.Value = rngFind.Offset(0, 14).Value
End If
Mick How will be the alter if im going to use ur code for the attached code? Instead of writing the ranges one by one!
Lets say firt 20 textboxes loaded with ur code , and the rest 21 to 40 textboxes will be loaded with column N value then it will alter till end..
(21-40 / 41-60 ....)
Regards
 
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
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E

Forum statistics

Threads
1,224,519
Messages
6,179,263
Members
452,902
Latest member
Knuddeluff

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