Input form not saving in the right place

[Filippo_Santarelli]

HI,

I attached the file I am having issues.

to resume the issue I am experiencing,

I created a workbook with one sheet with rawdata. I have several columns (which have the filter drop down menu). In Column A I have a counter for the first 1000 records (it is empty now I just p`ut the counter with an IF function)(

Now I created a form and a Save button with some basic VBA code behind it but when I hit save or it saves in the first line under the titles (WHEN the Range is A1 or in the last after the last row with the if counter if the Range is A2(the row with the titles of the columns).

see below the code, I am using to go to the last cell.
Sheet1.Activate
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Offset(0, 1).Value = dtpCaseReceivedDate.Value


I hope some one has a solution to have the save button save on the subsequent empty row instead of overwriting the same line over and over

Thanks
Filippo

[Neil_]

If you bind the ranges to the ControlSource of the text fields as you navigate, you don't need a save button. I've attached an example.

[Filippo_Santarelli]

Re: Input form not saving in the right place
If you bind the ranges to the ControlSource of the text fields as you navigate, you don't need a save button. I've attached an example.
Attached Files Attached Files
File Type: xlsm Data Entry Userform example.xlsm‎ (43.8 KB, 0 views) Download

Hi Neil,

I have security issues here and I cannot activate macros and my excel level is not enough to understand your answer

Can you be a bit more explicit please?


BTW I forgot to tel that I use Excel 2010
thanks
Filippo

[Neil_]

The example has quite a lot of code in it, and pasting it here without the userform to refer to would be of no help. Not sure about the problem you have in running macros when you have attached an xlsm book though.

[sanram]

It would be better if you can use the code like this :

Please Login or Register  to view this content.

Because Select, Offset they increase the execution time. Anyway in your code you are using this:

Please Login or Register  to view this content.

So this is selecting the row after title row all the time as Range("A1").End(xlDown) is A2 in your sheet.

[Filippo_Santarelli]

Hi Neil.

I am in a corporate network. I need to be extra carefull on anything I run here and Macro are blocked by default, except on some documents. Anyway I had IT scan it and authorise it .

I now see all the code. I will study it to see how to adapt it to my workbook. DO you mind me asking a few question here and there to understand all the controls? thing is I need to understand and use it on my worksheet.

Thanks!
Filippo

[Filippo_Santarelli]

Re: Input form not saving in the right place
It would be better if you can use the code like this :
Select Code
Private Sub CmdSave_Click()
Sheet1.Activate
LR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Sheet1.Range("B" & LR) = dtpCaseReceivedDate.Value
End Sub
Because Select, Offset they increase the execution time. Anyway in your code you are using this:
Select Code
Range("A1").End(xlDown).Offset(1, 0).Select
So this is selecting the row after title row all the time as Range("A1").End(xlDown) is A2 in your sheet. You can use A2 here instead of A1 :
Select Code
Range("A2").End(xlDown).Offset(1, 0).Select

Hi Sanram!

I will try your first suggestion

Select Code
Private Sub CmdSave_Click()
Sheet1.Activate
LR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Sheet1.Range("B" & LR) = dtpCaseReceivedDate.Value
End Sub

Thanks

[sanram]

You can use

Please Login or Register  to view this content.

[sanram]

Replace (Rows.Count, "A") with (Rows.Count, "B") in my first suggestion. I didn't see that you have formulas on column A. So it will not work.

[Filippo_Santarelli]

Replace (Rows.Count, "A") with (Rows.Count, "B") in my first suggestion. I didn't see that you have formulas on column A. So it will not work.

It works thank you!

[sanram]

You are welcome.