 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Is this possible?
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? >Hey Everyone, > [quoted text clipped - 13 lines] > >How would i do it? 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 >Assuming the userform has the person's name in TextBox2 and the amount > betted in Textbox1 the following code should do the trick: [quoted text clipped - 6 lines] >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. [quoted text clipped - 15 lines] > >Cells(rowno, colno + 1).Value = TextBox1.Value > >End Sub 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 [quoted text clipped - 19 lines] >> >Cells(rowno, colno + 1).Value = TextBox1.Value >> >End Sub ****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 >Okay- > [quoted text clipped - 20 lines] >>> >Cells(rowno, colno + 1).Value = TextBox1.Value >>> >End Sub 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*** > [quoted text clipped - 44 lines] > >>> >Cells(rowno, colno + 1).Value = TextBox1.Value > >>> >End Sub 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 [quoted text clipped - 20 lines] >> >>> >Cells(rowno, colno + 1).Value = TextBox1.Value >> >>> >End Sub 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, > [quoted text clipped - 7 lines] >>> >>> >Cells(rowno, colno + 1).Value = TextBox1.Value >>> >>> >End Sub 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 >OOPS.CODE: > [quoted text clipped - 23 lines] >>>> >>> >Cells(rowno, colno + 1).Value = TextBox1.Value >>>> >>> >End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.