Use combobox value & textbox to fill a variable cell

A

Adrienk

New Member
Joined
Jul 7, 2013
Messages
2
Hi everyone :)

I am writing you because I am a little bit stuck in my excel,...

The idea was the following :

The user has to select a value in the combobox3 in my Userform3. And the value of this combobox are located in my column A of my sheet called Data.

Once he pressed on the button 4, it redirects him on my Userform5.

In this userform5, I created a textbox1 which is equal to the value of the combobox3 of the userform3.

Then, the user has to select some value in different combobox (there are 40 of them).

I don't want him to be bind to answer all the question. And I would like that he can save a few answers, and then when he comes back on the form, by pressing the button 4 of the userform 3, he got the answers he answered before* (I really have no idea how to do it.)

I would like that when the user presses on the button 3 of my userform5, it records the value of the combobox in different columns, at one line defined by the textbox1(in other words, the combobox3 of the userform3.) In order to make it fill a pre-defined table.

That is to say, the value of textbox1 is for instance "AAA" and is located in A4. I want that for the combobox1, the value get into B4; for the combobox2 C4,... etc.

Could you help me please?

I am a beginner and I am really stuck...

English isn't my first language so... I hope I was clear...


here is the code of my userform5:

Code: 
Private Sub UserForm_Activate()    TextBox1.Text = UserForm3.ComboBox3.Text
    End Sub
    
Private Sub CommandButton3_Click()
Dim Criteria As Integer
With Sheets("Data")
    Criteria = .Columns("A").Find(TextBox1.Text, .Range("A4"), xlValues).Row
    ComboBox1 = .Cells(Criteria, "B")
    ComboBox2 = .Cells(Criteria, "C")
    ComboBox3 = .Cells(Criteria, "D")
    ComboBox4 = .Cells(Criteria, "E")
    ComboBox5 = .Cells(Criteria, "F")
    ComboBox6 = .Cells(Criteria, "G")
    ComboBox7 = .Cells(Criteria, "H")
    ComboBox8 = .Cells(Criteria, "I")
    ComboBox9 = .Cells(Criteria, "J")
    ComboBox10 = .Cells(Criteria, "K")
    ComboBox11 = .Cells(Criteria, "L")
    ComboBox12 = .Cells(Criteria, "M")
    ComboBox13 = .Cells(Criteria, "N")
    ComboBox14 = .Cells(Criteria, "O")
    ComboBox15 = .Cells(Criteria, "P")
    ComboBox16 = .Cells(Criteria, "Q")
    ComboBox17 = .Cells(Criteria, "R")
    ComboBox18 = .Cells(Criteria, "S")
    ComboBox19 = .Cells(Criteria, "T")
    ComboBox20 = .Cells(Criteria, "U")
    ComboBox21 = .Cells(Criteria, "V")
    ComboBox22 = .Cells(Criteria, "W")
    ComboBox23 = .Cells(Criteria, "X")
    ComboBox24 = .Cells(Criteria, "Y")
    ComboBox25 = .Cells(Criteria, "Z")
    ComboBox26 = .Cells(Criteria, "AA")
    ComboBox27 = .Cells(Criteria, "AB")
    ComboBox28 = .Cells(Criteria, "AC")
    ComboBox29 = .Cells(Criteria, "AD")
    ComboBox30 = .Cells(Criteria, "AE")
    ComboBox31 = .Cells(Criteria, "AF")
    ComboBox32 = .Cells(Criteria, "AG")
    ComboBox33 = .Cells(Criteria, "AH")
    ComboBox34 = .Cells(Criteria, "AI")
    ComboBox35 = .Cells(Criteria, "AJ")
    ComboBox36 = .Cells(Criteria, "AK")
    ComboBox37 = .Cells(Criteria, "AL")
    ComboBox38 = .Cells(Criteria, "AM")
    ComboBox39 = .Cells(Criteria, "AN")
    ComboBox40 = .Cells(Criteria, "AO")
End With
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
ComboBox7 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
ComboBox20 = ""
ComboBox21 = ""
ComboBox22 = ""
ComboBox23 = ""
ComboBox24 = ""
ComboBox25 = ""
ComboBox26 = ""
ComboBox27 = ""
ComboBox28 = ""
ComboBox29 = ""
ComboBox30 = ""
ComboBox31 = ""
ComboBox32 = ""
ComboBox33 = ""
ComboBox34 = ""
ComboBox35 = ""
ComboBox36 = ""
ComboBox37 = ""
ComboBox38 = ""
ComboBox39 = ""
ComboBox40 = ""
UserForm6.Show
End
End Sub




Private Sub UserForm_Initialize()
  Label1.Caption = Worksheets("Criteria").Range("A1").Value
  Label2.Caption = Worksheets("Criteria").Range("A8").Value
  Label3.Caption = Worksheets("Criteria").Range("A15").Value
  Label4.Caption = Worksheets("Criteria").Range("A22").Value
  Label5.Caption = Worksheets("Criteria").Range("A29").Value
  Label6.Caption = Worksheets("Criteria").Range("A36").Value
  Label7.Caption = Worksheets("Criteria").Range("A43").Value
  Label8.Caption = Worksheets("Criteria").Range("A50").Value
  Label9.Caption = Worksheets("Criteria").Range("A57").Value
  Label10.Caption = Worksheets("Criteria").Range("A64").Value
  Label11.Caption = Worksheets("Criteria").Range("A71").Value
  Label12.Caption = Worksheets("Criteria").Range("A78").Value
  Label13.Caption = Worksheets("Criteria").Range("A85").Value
  Label14.Caption = Worksheets("Criteria").Range("A92").Value
  Label15.Caption = Worksheets("Criteria").Range("A99").Value
  Label16.Caption = Worksheets("Criteria").Range("A106").Value
  Label17.Caption = Worksheets("Criteria").Range("A113").Value
  Label18.Caption = Worksheets("Criteria").Range("A120").Value
  Label19.Caption = Worksheets("Criteria").Range("A127").Value
  Label20.Caption = Worksheets("Criteria").Range("A134").Value
  Label21.Caption = Worksheets("Criteria").Range("A141").Value
  Label22.Caption = Worksheets("Criteria").Range("A148").Value
  Label23.Caption = Worksheets("Criteria").Range("A155").Value
  Label24.Caption = Worksheets("Criteria").Range("A162").Value
  Label25.Caption = Worksheets("Criteria").Range("A169").Value
  Label26.Caption = Worksheets("Criteria").Range("A176").Value
  Label27.Caption = Worksheets("Criteria").Range("A183").Value
  Label28.Caption = Worksheets("Criteria").Range("A190").Value
  Label29.Caption = Worksheets("Criteria").Range("A197").Value
  Label30.Caption = Worksheets("Criteria").Range("A204").Value
  Label31.Caption = Worksheets("Criteria").Range("A211").Value
  Label32.Caption = Worksheets("Criteria").Range("A218").Value
  Label33.Caption = Worksheets("Criteria").Range("A225").Value
  Label34.Caption = Worksheets("Criteria").Range("A232").Value
  Label35.Caption = Worksheets("Criteria").Range("A239").Value
  Label36.Caption = Worksheets("Criteria").Range("A246").Value
  Label37.Caption = Worksheets("Criteria").Range("A253").Value
  Label38.Caption = Worksheets("Criteria").Range("A260").Value
  Label39.Caption = Worksheets("Criteria").Range("A267").Value
  Label40.Caption = Worksheets("Criteria").Range("A274").Value
  
    Me.ComboBox1.List = Worksheets("Criteria").Range("B2:B6").Value
    Me.ComboBox2.List = Worksheets("Criteria").Range("B9:B13").Value
    Me.ComboBox3.List = Worksheets("Criteria").Range("B16:B20").Value
    Me.ComboBox4.List = Worksheets("Criteria").Range("B23:B27").Value
    Me.ComboBox5.List = Worksheets("Criteria").Range("B30:B34").Value
    Me.ComboBox6.List = Worksheets("Criteria").Range("B37:B41").Value
    Me.ComboBox7.List = Worksheets("Criteria").Range("B44:B48").Value
    Me.ComboBox8.List = Worksheets("Criteria").Range("B51:B55").Value
    Me.ComboBox9.List = Worksheets("Criteria").Range("B58:B62").Value
    Me.ComboBox10.List = Worksheets("Criteria").Range("B65:B69").Value
    Me.ComboBox11.List = Worksheets("Criteria").Range("B72:B76").Value
    Me.ComboBox12.List = Worksheets("Criteria").Range("B79:B83").Value
    Me.ComboBox13.List = Worksheets("Criteria").Range("B86:B90").Value
    Me.ComboBox14.List = Worksheets("Criteria").Range("B93:B97").Value
    Me.ComboBox15.List = Worksheets("Criteria").Range("B100:B104").Value
    Me.ComboBox16.List = Worksheets("Criteria").Range("B107:B111").Value
    Me.ComboBox17.List = Worksheets("Criteria").Range("B114:B118").Value
    Me.ComboBox18.List = Worksheets("Criteria").Range("B121:B125").Value
    Me.ComboBox19.List = Worksheets("Criteria").Range("B128:B132").Value
    Me.ComboBox20.List = Worksheets("Criteria").Range("B135:B139").Value
    Me.ComboBox21.List = Worksheets("Criteria").Range("B142:B146").Value
    Me.ComboBox22.List = Worksheets("Criteria").Range("B149:B153").Value
    Me.ComboBox23.List = Worksheets("Criteria").Range("B156:B160").Value
    Me.ComboBox24.List = Worksheets("Criteria").Range("B163:B167").Value
    Me.ComboBox25.List = Worksheets("Criteria").Range("B170:B174").Value
    Me.ComboBox26.List = Worksheets("Criteria").Range("B177:B181").Value
    Me.ComboBox27.List = Worksheets("Criteria").Range("B184:B188").Value
    Me.ComboBox28.List = Worksheets("Criteria").Range("B191:B195").Value
    Me.ComboBox29.List = Worksheets("Criteria").Range("B198:B202").Value
    Me.ComboBox30.List = Worksheets("Criteria").Range("B205:B209").Value
    Me.ComboBox31.List = Worksheets("Criteria").Range("B212:B216").Value
    Me.ComboBox32.List = Worksheets("Criteria").Range("B219:B223").Value
    Me.ComboBox33.List = Worksheets("Criteria").Range("B226:B230").Value
    Me.ComboBox34.List = Worksheets("Criteria").Range("B233:B237").Value
    Me.ComboBox35.List = Worksheets("Criteria").Range("B240:B244").Value
    Me.ComboBox36.List = Worksheets("Criteria").Range("B247:B251").Value
    Me.ComboBox37.List = Worksheets("Criteria").Range("B254:B258").Value
    Me.ComboBox38.List = Worksheets("Criteria").Range("B261:B265").Value
    Me.ComboBox39.List = Worksheets("Criteria").Range("B268:B272").Value
    Me.ComboBox40.List = Worksheets("Criteria").Range("B275:B279").Value
  
  End Sub

Here is the excel file... I don't know if it's useful to you.
"click on accès à la gestion des données" then "Analyser un brevet existant" Select whatever you want and then click on "Accéder aux critères" to see the userform3 and 5.

Thanks a lot.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

I
  • Question
Value in userform TextBox incorrect due to initalize option being used
Replies
5
Views
381
ipbr21054
I
I
  • Question
Transfer values userform to worksheet not adding values in all the cells
Replies
7
Views
516
ipbr21054
I
Jimmypop
  • Question
Pass value from multiple textboxes on userform to cells in worksheet
Replies
6
Views
372
kanadaaa
kanadaaa
Z
  • Question
Userform Listbox updating and adding values using Textbox
Replies
0
Views
7K
zubin
Z
A
  • Question
saving fresh data!
Replies
3
Views
404
Yongle
Yongle

Forum statistics

Threads
1,215,059
Messages
6,122,917
Members
449,093
Latest member
dbomb1414

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