VBA to send data from input box to another worksheet

[adityatandel]

I have created a form for my work. And I have input boxes for users to add data that I would like to get stored in another worksheet. What is the vba code to send data for an input box to another worksheet at the bottommost empty row; first column.

Private Sub cmdAddData_Click()

' Collect user name, find the space between first and last names, and separate the names.
UserName = InputBox("Enter the Program Name.", "Program Name")
spaceLoc = InStr(1, UserName, " ")
firstName = Left(UserName, spaceLoc - 1)

Thank you,
Adi

 

[Joe Was]

This will get data from the user and add it to a list in column "A" on sheet 2. If sheet 2 has a label in column "A" then the item will be placed below it in the next available row. If it does not have a label then the first item starts one row down from the first row.

The MsgBox stays on Sheet 1, and you do not switch back and forth during the list update on Sheet 2. So, the user only sees Sheet 1.

Sub myBuildList()
Dim lngLstRow&
Dim strMyIPBMsg$

strMyIPBMsg = InputBox("Add User-Name:", "Get User Name!")

With Sheets("Sheet2")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("A" & lngLstRow).Value = strMyIPBMsg
End With

Sheets("Sheet1").Select
End Sub

 

[jim may]

Sub Foo()
res = Application.InputBox("Enter File Name")
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Value = res
End Sub

 

[Joe Was]

Note: Jim's last row code needs to be updated, it over-writes the last item in the existing list, rather than moving to the next blank row. So, his code replaces each item, which may be what you want?

My code builds a running list, down the column, each item on its own row.

 

[adityatandel]

this is what my code looks like now. so i entered text in the input box but i see nothing in the "Data" sheet. my worksheet is named "Data". when i hit enter, nothing happens. please help.

Private Sub cmdAddData_Click()

' Collect program name, find the space between first and last names, and separate the names.
UserName = InputBox("Enter the Program Name.", "Program Name")
spaceLoc = InStr(1, UserName, " ")
' firstName = Left(UserName, spaceLoc - 1)
End Sub

' Message box for user if program name already exists in the database

Sub MessageBox()
If (UserName = "Warranty") Then
MsgBox ("Program Name already exists in the database")
Else
MsgBox ("Program Name successfully added to the database")
End If


End Sub

' Data from input boxes get added to the database"
Sub myBuildList()
Dim lngLstRow&
Dim strMyIPBMsg$

strMyIPBMsg = InputBox("Add User-Name:", "Get User Name!")

With Sheets("Data")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("A" & lngLstRow).Value = strMyIPBMsg
End With

Sheets("Sheet1").Select
End Sub

 

[Joe Was]

"when i hit enter, nothing happens"

The code runs from a Module and you must run the sub?

Does the InPutBox Display?

What Code module do you have the code, in?
I ran my code from "Module1"

Your other code does not interact with my code, so my code should get the users input and add it to column "A" below any existing data.

Do you have sheets "Sheet1, Sheet2, Data?"

Try removing the last line of code!

 

[jim may]

Sorry, I failed to test my code - revised line below

Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = res

 

[adityatandel]

Joe,

1. "The code runs from a Module and you must run the sub?"

i want my code to run when the message box appear. so once the user inputs data in the input box, i want a message box to say that the data has successfully been added to the database. this is when i want the data added to the database. sorry, i should have made this clear earlier.

2. "Your other code does not interact with my code, so my code should get the users input and add it to column "A" below any existing data."

how do i get the two codes to interact with each other. this will get me to my goal.

3. "Do you have sheets "Sheet1, Sheet2, Data?""

they are :Front End, Data, Sheet 3"

I really appreciate your help man

 

[Joe Was]

PrivateS ub cmdAddData_Click()
Dim strUserName$
Dim lngLstRow&
' Collect program name, find the space between first and last names, and separate the names.

strUserName = InputBox("Enter the Program Name.", "Program Name")

'spaceLoc = InStr(1, strUserName, " ")
' firstName = Left(strUserName, spaceLoc - 1)
' Message box for user if program name already exists in the database

If UCase(strUserName) = "WARRANTY" Then
MsgBox ("Program Name already exists in the database")
Else

' Data from input boxes added to the database"
With Sheets("Data")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("A" & lngLstRow).Value = strUserName
End With

MsgBox ("Program Name successfully added to the database")
End If

Sheets("Front End").Select
End Sub

 

[Joe Was]

The above was tested, and combines your Subs!
This other code below, should add the additional steps to work with the name parts as well:

Sub cmdAddData_Click()
Dim strUserName$, strFirstName$, strLastName$
Dim lngLstRow&, strLength&, lngSpaceLoc&
Dim sw1 As Boolean

'Get program name from user!
strUserName = InputBox("Enter the Program Name.", "Add Program Name!")

'Find First and last names.
On Error GoTo myErr

strLength = Len(strUserName)
lngSpaceLoc = InStr(1, strUserName, " ")
strFirstName = Left(strUserName, lngSpaceLoc - 1)
strLastName = Right(strUserName, strLength - lngSpaceLoc)
sw1 = True

'Optional, if good User Name, show data.
MsgBox "Total Length: " & strLength & vbLf & _
"1st Space: " & lngSpaceLoc & vbLf & _
"First: " & strFirstName & vbLf & _
"Last: " & strLastName, _
vbInformation + vbOKOnly, _
"User InPut Results"

myErr:
' Message box for user if program name already exists in the database
If UCase(strUserName) = "WARRANTY" Then
MsgBox "Program Name already exists in the database", _
vbCritical + vbOKOnly, _
"Error!"
Else

' Data from input boxes added to the database"
With Sheets("Data")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("A" & lngLstRow).Value = strUserName
End With

MsgBox "Program Name successfully added to the database!", _
vbExclamation + vbOKOnly, _
"Added!"
End If

Sheets("Front End").Select

'Test for good user name.
If sw1 = False Then
GoTo myStrMsg
Else

GoTo myEnd
End If

myStrMsg:
'User Name Warrning!
MsgBox """" & strUserName & """ is not a proper Name!", _
vbCritical + vbOKOnly, _
"InPut Warrning!"

myEnd:
End Sub