user form - how to set it up so it stores data when you press ente

[NDBC]

I want to create a user form with a text box that you type in the data and
press enter and the data is stored in a set cell, then the form automatically
clears and you type the next bit of data in the same text box, press enter
and the data is saved in the next cell down and so on. I can create the form
and text box but need the code. The only example I could find needed a
commandbutton to be clicked to store the data. I want to cut out this step.

Thanks

 

[Jacob Skaria]

Refer the below link

http://www.contextures.com/xlUserForm01.html

If this post helps click Yes

 

[Mike H]

Hi,

Assign this code to a button on your userform. You will need to change
MySheet to the correct name for the sheet you wanr and the textbox name and
if necessary change the column you want to use which is currently A

Private Sub CommandButton1_Click()
mysheet = "Sheet1"
lastrow = Sheets(mysheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Cells(lastrow, 1).Value = TextBox1.Text
End Sub

Mike

 

[NDBC]

Thanks for the replys but neither are quite what I wanted. I can do what both
examples do. That is put in the text box and have a button that does the
command to store the entered data.

What a want is a form with only the text box so that when you hit enter the
data is stored. I don't want to have to either click on the button or press
enter twice.

Can this be done.

 

[Mike H]

You could do it like this which detects the enter key being pressed

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode <> 13 Then Exit Sub
mysheet = "Sheet1"
lastrow = Sheets(mysheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Cells(lastrow, 1).Value = TextBox1.Text
End Sub

Mike

 

[NDBC]

Mike,

Thanks for that. It seems like it is exactly what I'm looking for. I tried
copying it in but get this error "Procedure declaration does not match
description of event or procedure having same name". The textbox is called
textbox1 so it is not the name. Is there something else I need.

Thanks

 

[Mike H]

Hi,

I suspect you entered it in the wrong place. From your userform, right click
the textbox and view code. On the right hand side and from the right hand
dropdown select 'Keydown' and paste my code into that.

If KeyCode <> 13 Then Exit Sub
mysheet = "Sheet1"
lastrow = Sheets(mysheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Cells(lastrow, 1).Value = TextBox1.Text

Mike

Mike

 

[Jacob Skaria]

Try the Beforeupdate event

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
lngRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(lngRow, 1).Value = TextBox1.Text
End Sub

If this post helps click Yes

 

[NDBC]

Mike

Thanks again for help. Sorry for being such a slow learner. I am making
progress as the form now comes up no worries. Problem is now that I type in
the data and press enter and nothing happens. No error, nothing. I tried
pressing up down, enter tab and clicking. I get nothing. In the end I just
have to click the x in top right to exit.

 

[NDBC]

Jacob,

Once again I am in need of your expertise. I am getting same problem. Form
comes up. type in number and hit enter. Nothing happens. Only difference is
when I hit the x to exit form the number then gets stored in sheet1. I am
getting closer to what I need but not quite there yet.

 

[JLatham]

His code pretty much works for me. I took it and modified it somewhat,
including the "clear and reset" for TextBox1:

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 13 Then
Exit Sub
End If
'put result at bottom of column A on active sheet
Cells(Cells. _
Rows.Count, "A").End(xlUp).Offset _
(1, 0) = TextBox1.Text
'clear/reset the Text Box
TextBox1 = ""
End Sub

Make sure you have the code in the right place: open your project, view the
UserForm object and double-click the textbox. It should show you the
_KeyDown event code you have. If that works, then double check to make sure
you don't have it declared more than once, perhaps elsewhere in your project.

If it doesn't show your code, it should at least create a stub for the
textbox's _Click event and you can use the right-hand pull-down to create a
stub for the _KeyDown event and just put the meat of the code into it and
give it a try. In this case, you probably definitely have another _KeyDown
event code laying around somewhere in the wrong place and need to get rid of
it.

 

[Mike H]

Hi,

The only thing that will happen with my code is that when you press enter
the contents of the textbox will be written to the first empty cell in column
A of sheet 1. What do you want to happen apart from that:-

To clear the textbox

TextBox1.Text = ""

To close the userform make this the last line in the code

Unload Me


Mike

 

[Jacob Skaria]

Hi Please ignore the previous post.. I didnt notice the "Enter Key". Try the
below which will update the entries to ColA of Sheet1

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then
lngRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("A" & lngRow) = TextBox1.Text
TextBox1.Text = ""
End If
End Sub

If this post helps click Yes

 

[NDBC]

Thank you all for your help. I definitely need to learn vba. I didn't have
another _keydown but there was some other code in the worsheet sheet1.
Deleted it and all works fine. The only thing I don't fully understand is the
if keycode is not equal to 13 then end sub.

My take on it was if keycode is not 13 (which I assumed meant enter key)
then exit form. In practice I can press any key and nothing happens so I must
be wrong. Could you please help with the explanation.

Thanks again all three of you

 

[NDBC]

Just so I can update my understanding of these commands, I gather
beforeupdate only gets executed when you exit the form. Is this right.

 

[Mike H]

The only thing I don't fully understand is the

if keycode is not equal to 13 then end sub.

Keycode 13 is both enter keys (keyboard and numeric keypad) so if you press
any other key then line below treminates the subroutine and allows input into
the textbox to continue.

If KeyCode <> 13 Then Exit Sub

It then follows that if keycode = 13 the rest of the code in the sub executes.

Mike

 

[Iain]

Hi Jacob.

I have a similar problem to this, and the code you put below works well. I
am trying to do the same thing, but with 4 different sets of data - "Code"
"Number" "Weight " & "Cost per gram". Is there anyway of being asked for a
code, then a number, then weight and finally Cost per gram" - then being
asked if you wish to enter more data, when yes goes through the loop again,
and no goes back to Sheet1?

I have tried "calling "Private Sub TextBox2_KeyDown" from within the code
below and it isnt working.

Many Thanks.

Iain