VBA to send data from input box to another worksheet

adityatandel

Board Regular
Joined
Aug 10, 2007
Messages
71
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
Sub Foo()
res = Application.InputBox("Enter File Name")
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Value = res
End Sub
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
"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!
 
Upvote 0
Sorry, I failed to test my code - revised line below

Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = res
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top