Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press ente
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press ente
Refer the below link
http://www.contextures.com/xlUserForm01.html If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press ente
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press ente
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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. "Mike H" wrote: 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 "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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. "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 "NDBC" wrote: 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. "Mike H" wrote: 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 "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 "JLatham" wrote: 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. "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
Just so I can update my understanding of these commands, I gather
beforeupdate only gets executed when you exit the form. Is this right. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 "NDBC" wrote: 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 "JLatham" wrote: 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. "NDBC" wrote: 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" wrote: 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" wrote: 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. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
user form - how to set it up so it stores data when you press
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form - Get Data From Drop List | Excel Discussion (Misc queries) | |||
User form Samples (to query data) | Excel Discussion (Misc queries) | |||
How can I make the cursor move down 8 cells each time I press ente | Setting up and Configuration of Excel | |||
How can I make the cursor move down 8 cells each time I press ente | Setting up and Configuration of Excel | |||
How do i fill the adjacent cell formulas in user form when i press | Excel Discussion (Misc queries) |