Is this possible?

  • Thread starter Thread starter Chris Cred via OfficeKB.com
  • Start date Start date
C

Chris Cred via OfficeKB.com

Hey Everyone,

I will use one row as an example, however, there is a huge range I'm dealig
with.

VBA UserForm Question-

Let us say I have data in A1 that must never be removed or changed once
entered into that cell. I need the user to be able to enter values at a later
date that will drop into B1, C1, and D1 using the userform. I need the
userform to allow a user to select the person's name in the combobox (Or
whatever workd) and then have the data associated with that person.

-So if I already have the name "Guido" in A1
-And two weeks later I want to add $5,000 to B1 since he lost his bets

How would i do it?
 
No Suggestions whatsoever? I know we have some intelligence in this forum so
does anyone care to give me a helping hand?
 
Assuming the userform has the person's name in TextBox2 and the amount
betted in Textbox1 the following code should do the trick:

Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("IV" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub
 
I appreciate the response. Tha is pretty much what I'm looking for, however,
I need the data from the userform to drop into colukmns A,B,C everytime.
Right now this code looks for the first active, yet skips all the way to
culum H and adds the data there.

FYI, the cells that the data needs to drop into contains formuls.

Thanks
 
Chris,
You need to be more specific i.e. what textboxes put data
where. If the cells contain formulae, then I presume the textbox value is
required to be subsituted into the formulae so you would need something like:

Private Sub CommandButton2_Click()

rowno = Columns(1).Find(Trim(TextBox2.Value)).Row

Range("b" & rowno) = "=Sqrt(Int(" & TextBox1.Value & "))" ' <== sample
formula
Range("c" & rowno="= ..... your formula"
etc

End Sub

HTH
 
Okay-

Disregard the post above...The code below works fine. However...

The only thing I'm stuck on now is figuring how to add an additional row in
my code to do exactly the same thing row C does. So, row D!

Also, since combobox1 (CB) is a range using the RowSource property, and CB2
and CB3 will be additems, how would I trigger those to drop into the cells
with the commandclick?



Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("IV" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub



Chris,
You need to be more specific i.e. what textboxes put data
where. If the cells contain formulae, then I presume the textbox value is
required to be subsituted into the formulae so you would need something like:

Private Sub CommandButton2_Click()

rowno = Columns(1).Find(Trim(TextBox2.Value)).Row

Range("b" & rowno) = "=Sqrt(Int(" & TextBox1.Value & "))" ' <== sample
formula
Range("c" & rowno="= ..... your formula"
etc

End Sub

HTH
I appreciate the response. Tha is pretty much what I'm looking for, however,
I need the data from the userform to drop into colukmns A,B,C everytime.
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
****Man...I am loosing it***

EDITED REPOST:

Okay-

Disregard the post above...The code below works fine. However...

The only thing I'm stuck on now is figuring how to add an additional row in
my code to do exactly the same thing row C does. So, row D!

Also, since combobox1 (CB) is a range using the RowSource property, and CB2
and CB3 will be additems, how would I trigger those to drop into the cells
with the commandclick?

CODE:
Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("C" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub




Chris said:
Okay-

Disregard the post above...The code below works fine. However...

The only thing I'm stuck on now is figuring how to add an additional row in
my code to do exactly the same thing row C does. So, row D!

Also, since combobox1 (CB) is a range using the RowSource property, and CB2
and CB3 will be additems, how would I trigger those to drop into the cells
with the commandclick?

Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("IV" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub
Chris,
You need to be more specific i.e. what textboxes put data
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
Hi,
I have modified the code which relies on C being added before D
i.e code in order below. First "xlToLeft" will point to Cell B so offset(0,1)
will put data in C; second "xlToLeft" will point to column C (just filled) so
offset(0,1) will put data in D.

(What goes into col B?)

For CBs, change the textbox references to the CB references (if I understand
the requirement correctly).

hth

Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("C" & rowno).End(xlToLeft).Offset(0, 1) = TextBox1.Value
Range("D" & rowno).End(xlToLeft).Offset(0, 1) = TextBox3.Value
End Sub

Chris Cred via OfficeKB.com said:
****Man...I am loosing it***

EDITED REPOST:

Okay-

Disregard the post above...The code below works fine. However...

The only thing I'm stuck on now is figuring how to add an additional row in
my code to do exactly the same thing row C does. So, row D!

Also, since combobox1 (CB) is a range using the RowSource property, and CB2
and CB3 will be additems, how would I trigger those to drop into the cells
with the commandclick?

CODE:
Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("C" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub




Chris said:
Okay-

Disregard the post above...The code below works fine. However...

The only thing I'm stuck on now is figuring how to add an additional row in
my code to do exactly the same thing row C does. So, row D!

Also, since combobox1 (CB) is a range using the RowSource property, and CB2
and CB3 will be additems, how would I trigger those to drop into the cells
with the commandclick?

Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("IV" & rowno).Select
Selection.End(xlToLeft).Select
colno = ActiveCell.Column
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub
Chris,
You need to be more specific i.e. what textboxes put data
Click to expand...
[quoted text clipped - 19 lines]
Cells(rowno, colno + 1).Value = TextBox1.Value
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Here is the entire code. Everything looks good, however, I cannot get column
D filled. It does nothing. Btw, nothing goes in column B since it has static
text.
Hi,
I have modified the code which relies on C being added before D
i.e code in order below. First "xlToLeft" will point to Cell B so offset(0,1)
will put data in C; second "xlToLeft" will point to column C (just filled) so
offset(0,1) will put data in D.

(What goes into col B?)

For CBs, change the textbox references to the CB references (if I understand
the requirement correctly).

hth

Private Sub CommandButton2_Click()
rowno = Columns(1).Find(Trim(TextBox2.Value)).Row
Range("C" & rowno).End(xlToLeft).Offset(0, 1) = TextBox1.Value
Range("D" & rowno).End(xlToLeft).Offset(0, 1) = TextBox3.Value
End Sub
****Man...I am loosing it***
Click to expand...
[quoted text clipped - 44 lines]
Click to expand...
 
OOPS.CODE:

Private Sub cmdAdd_Click()
rowno = Columns(1).Find(Trim(ComboBox1.Value)).Row
Range("C" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox2.Value
Range("D" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox3.Value


'check for a part number
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
MsgBox "Please select the course number."
Exit Sub
End If

'clear the data
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox1.SetFocus

End Sub

Chris said:
Hi,

Here is the entire code. Everything looks good, however, I cannot get column
D filled. It does nothing. Btw, nothing goes in column B since it has static
text.
Hi,
I have modified the code which relies on C being added before D
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Newmind....I see my mistake! You did it! Thank you so much, it works
perfectly now. I really appreciate you taking the time to help me.

Chris

Chris said:
OOPS.CODE:

Private Sub cmdAdd_Click()
rowno = Columns(1).Find(Trim(ComboBox1.Value)).Row
Range("C" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox2.Value
Range("D" & rowno).End(xlToLeft).Offset(0, 1) = ComboBox3.Value

'check for a part number
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
MsgBox "Please select the course number."
Exit Sub
End If

'clear the data
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox1.SetFocus

End Sub
Hi,
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time Calculations Help 4
Tranfer data from Excel to word (Docx) via VBA in Excel 5
VBA copy and paste data 2
Set Userform label text if form is not loaded??? 3
Userform Textbox to Worksheet Textbox 1
Save data to closed workbook 2
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
OFFSET LOOKUP function in VBA? 9

Back
Top