Userform Newbie

[Helen]

Hello

I'm new to all this however by reading in the Excel community I've picked up
bits and pieces here and there -- I have a simple userform with a Combo Box -
Text Box and a Command Button. The Combo is populated from Row Source -
Sheet2!A1:A20, Sheet 1 has the same info as in the Combo also inserted in
rows A1:A20. If for example I select "Bob" in the Combo then enter 10 in the
Text Box click the Command Button could it enter on Sheet1 ColumnB 10 against
"Bob" and then if I selected "Alice" entered a number in the Text Box it
would enter the number against "Alice" in ColumnB and so on for each
selection or would I need 20 Text Boxes? I just hope I have explained
properly what I'm trying to do!!!!

 

[RaceEend]

Hello

I'm new to all this however by reading in the Excel community I've
picked up bits and pieces here and there -- I have a simple userform
with a Combo Box - Text Box and a Command Button. The Combo is
populated from Row Source - Sheet2!A1:A20, Sheet 1 has the same info
as in the Combo also inserted in rows A1:A20. If for example I
select "Bob" in the Combo then enter 10 in the Text Box click the
Command Button could it enter on Sheet1 ColumnB 10 against "Bob" and
then if I selected "Alice" entered a number in the Text Box it would
enter the number against "Alice" in ColumnB and so on for each
selection or would I need 20 Text Boxes? I just hope I have explained
properly what I'm trying to do!!!!

In one-line:
Range(Me.ComboBox1.RowSource).Resize(1,
1).Offset(Me.ComboBox1.ListIndex, 1) = Me.TextBox1.Value

replace Combobox1 and Textbox1 by the correct name.

In the event of the commandbutton, test if there is a item selected in
the combobox.

Private Sub CommandButton1_Click()
If Me.ComboBox1.ListIndex > -1 Then
Range(Me.ComboBox1.RowSource).Resize(1,
1).Offset(Me.ComboBox1.ListIndex, 1) = Me.TextBox1.Value
End If
End Sub

--

 

[Helen]

Hello

Thanks brilliant worked first time spot on -- two queries at the moment it
is working in Sheet 2 and I would really like it to work on Sheet1 and how if
I select"Bob" today and enter 10 if tomorrow he has another 10 how does the
sheet or code add what already is in the cell together to make the new total.

Many, Many Thanks

 

[RaceEend]

Hello

Thanks brilliant worked first time spot on -- two queries at the
moment it is working in Sheet 2 and I would really like it to work on
Sheet1 and how if I select"Bob" today and enter 10 if tomorrow he has
another 10 how does the sheet or code add what already is in the cell
together to make the new total.

Many, Many Thanks

I am not sure if i understand it correct.
The list of the combobox is read from sheet1 but the data must be on
sheet2? On sheet2 is the same data? Why isn't the combobox list linked
to sheet2

second question:
Adding the value

Private Sub CommandButton1_Click()
If Me.ComboBox1.ListIndex > -1 Then
Dim rng As Range
Set rng = Range(Me.ComboBox1.RowSource).Resize(1,
1).Offset(Me.ComboBox1.ListIndex, 1)
rng = rng + Me.TextBox1.Value
End If
End Sub

--

 

[JLGWhiz]

Helen, the answer is yes. If your Combo box has a macro attached that says
when Bob is selected there that Bob is also selected on the worksheet, then
you can write code to say that the range (a cell) adjacent to the combo box
selection = value entered in TextBox(somenumberorname). Since you did not
post the current code, it is difficult to offer a solution in code.

 

[Helen]

Hello All

Thank you so much for all your help -- I tried yesterday to add to the value
of the exsisting value and I could only get it to work to individual cells
your rng = rng thingy does the job perfectly -- couldn't find that anywhere
in the help files -- I also twigged the sheet problem myself after asking the
question. Now can get on with my Excel learning curve.

Thank You