Linking a textbox to a worksheet sub

[harber95]

I made a worksheet sub that its ".value=" should accommodate a value that was typed before in a user form textbox. How should I approach this?

[Trebor76]

Hi harber95,

Is the form still open while the macro is being run?

Robert

[harber95]

I just use the commands on VBA, there is no macro (I might be wrong, I'm a newbie)

[Trebor76]

I just use the commands on VBA, there is no macro

Macros are VBA code :confused:

Can you attach the workbook with a brief description as to what you're after?

[harber95]

In the file that I uploaded, open the "A_Student_Application" userform.
I am referring to the textbox that has the label "Last Name".
When I activate the userform, after typing a last in the mentioned textbox, There is button call "Insert data".
By pressing the button, I want the value in the textbox to appear in the table in Sheet1 in column A, right under the previous last name listed there.

14081

[Trebor76]

I have no idea what the rest of the code is doing but this will populate columns A to E in Sheet1 with the fields on the A_Student_Application form:


Dim lngMyRow As Long
lngMyRow = Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 'Finds the last row in columns A to J and increments it by one

Range("A" & lngMyRow).Value = Me.txtLastName.Value
Range("B" & lngMyRow).Value = Me.txtFirstName
Range("C" & lngMyRow).Value = Me.TextBox3
Range("D" & lngMyRow).Value = Me.TextBox4
Range("E" & lngMyRow).Value = Me.TextBox5

Regards,

Robert

[harber95]

It works, thanks.
One more thing, how do I do the same thing for a combobox (Year section)?

[JKwan]

msgbox Me.ComboBox1.List(Me.ComboBox1.ListIndex)

[harber95]

Thanks. Where do I add it? Are you sure I need a msgbox?
Is this supposed to be the cmd that puts the year number in sheet1?

[JKwan]

I was only showing you how to get the data out of the combo box. Add that along to message #6

[Trebor76]

It works, thanks.
One more thing, how do I do the same thing for a combobox (Year section)?

Just add this line to my code from thread #6:


Range("F" & lngMyRow).Value = Me.ComboBox1.Value

Regards,

Robert

[harber95]

Thanks, it works

[harber95]

Now I think comes the more inricate part.
In the file I uploaded in Thread #5, there is a User Form called "B_Insert_Grade".
I need to choose a subject from the list.
Put a grade in the textbox, then press the button.
The grade I typed should allign with the column that represents the grade in the subject chosen from the combobox before.
Reminder: it has to be in the same line that the data from "A_Student_Application" that was inserted earlier.
Thanks for the help!

[Trebor76]

You can use this code for your button on the B_Insert_Grade form:


Dim lngMyRow As Long
lngMyRow = Range("A:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Finds the last row in columns A to F as these were previously populated via the 'A_Student_Application' form

Select Case ComboBox1.Value
Case Is = "English"
Range("G" & lngMyRow).Value = Me.txtGrade.Value
Case Is = "Chemistry"
Range("H" & lngMyRow).Value = Me.txtGrade.Value
Case Is = "Biology"
Range("I" & lngMyRow).Value = Me.txtGrade.Value
Case Is = "History"
Range("J" & lngMyRow).Value = Me.txtGrade.Value
End Select

So does this mean you have to enter a grade for a subject, submit it and then do the same again for each of the other three subjects? If so I would just have the four subject titles and their grade fields on the form and submit them all at once - just a thought.

Robert

[harber95]

What you did is fine. Somehow the History grade isn't inserted in the table. Also, the year is presented as a date in the table, and it should be just a number. Thanks again

[Trebor76]

Somehow the History grade isn't inserted in the table

You've actually incorrectly spelt History as Histroy when loading the subjects to ComboBox1. Change this and it should be fine.


Also, the year is presented as a date in the table, and it should be just a number

This happens when no year is selected so the default 1-4 is returned which Excel is interpreting as a date.

Regards,

Robert

[harber95]

Thanks.

A little of topic, I want the ID written in the first User Form (A_Student_Application) to appear in the next one (B_insert_grade)

[Trebor76]

A little of topic

You need to start a new thread for different requests with a link back to this thread if you think it will help someone trying to answer your new thread.