Help require to record date and time

Dear friends,
Please suggest me a correction in this macro to achieve require output.

Sheets("LC").Range("G7").Select
    ActiveCell.FormulaR1C1 = "1"  ‘Here I want to enter the any register number of student and hid enter key
            Sheets("LC").Range("A8").Select  ‘Here I want to hid enter key and then a formula should be enter on ‘Register’ sheet in cell AA6 as follows
Selection.FormulaR1C1 = _
        "=LC!R[1]C[-26]&CHAR(10)&TEXT(LC!R[21]C[-25], ""dd/mm/yyyy hh:mm AM/PM"")"
                    Formula = Value ‘It’s very important because the date and time is continuously changing in cell b28 on LC sheet
End Sub

Requirements:
I want to know how many times and when the student has taken LC. The date and time should be on register sheet from column ‘AA:AE’ respectively for each student.
The register number is a unique id for each student.
I want to hid enter key on cell ‘g7 and after a8’ on LC sheet. It is very important to hid enter key on a8 because after hiding the enter key the receipt number change automatically. Though the register number of a student is same but the receipt number for each receipt is different for each time.
I want the date and time of lc given infront of each student respectively.

Any help will be appreciated to achieve this target.

Thanking you in anticipation.

Hi Pan314,
Thank you for your response. I didn't understand what you have done. Please don't change my workbook so that I will understand the solution more better.

My requirement is that if I enter a register number for example 5 then the program shold hid enter key and go to cell a8 receipt no. and hid enter so that the receipt no. will change automatically. Then the mentioned formula will be created on register sheet range aa:ae infront of entered register number on lc sheet cell g7. Then change the formula into value. It will be a first lc so the output should be in column aa lc1 column. If the student ask for second time lc then I will enter his register no. on lc sheet g7 in this case 5. Then after hiding enter key the a8 cell will be selected. After hiding enter key on it the receipt no. will be change automatically and a formula will be created on sheet register in lc2 column because it is a second lc and so on for each register no. respectively.

Hope this will clear my requirements to you correctly.

Thank you,

Hello pan314,
Sorry for not understanding your code correctly. Today I had gone it throughly and made some revisions to meet my requirements in my workbook but in vain. Something must be wrong in my revision. Your code is perfect but due to my lack of understanding this thread has gone long.

Please amend the following requirements in the code to solve it today positively.

Please do the necessary amendment in the attached workbook only for my better understanding.
The receipt number is automatically created after hitting enter key on it cell a8 lc sheet
I have to enter the register number on lc sheet cell g7 manually

lc

Sorry for trouble and thank you for suggesting a excellent code to achieve my target.

Finally extremely sorry for not understanding the code you provided.

Have a nice day.

Hey MSO,

I am not following precisely what needs to happen.

Sounds like you are looking to place a time stamp upon submitting a record. If this is the case we can avoid the need to copy and paste a format of visible values.
But first lets confirm what you are doing.

To begin with you want something to happen when you select a registered student ID - In G7 on the LC tab...

So let's place this VBA on the LC sheet - This can be altered to run different Macros upon updating G7 but for now let's run MarkTime

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("G7")) Is Nothing Then MarkTime
End Sub

In order to run MarkTime, you need to create one... Utilize the following. This will create a timestamp in G4 on LC... we can discuss what to do with this once we understand further what is needed. Unless of course someone else jumps in here -

Sub MarkTime()
    Sheets("LC").Select
    Range("G4").Value = Format(Now(), "dd/mm/yyyy hh:mm AM/PM")
End Sub

In your next reply... Create a Before/After tab of Register since I believe this is what you want the results to appear on.
It should contain 3 or 4 records in their final format with clear explanation as to how you came to make them up.

Hi ELegault,
Thank you for your response to solve my problem. Actually I want to mark date and time with a receipt no. on the register sheet infornt of that particular register number as shown. Here I'm explaining it again for your better understanding to achieve the target.

Ex. 1
I enter register no. 2 in cell g7 lc sheet then
It should select cell a8 lc sheet containing receipt number.
After hiding enter key the receipt number will change automatically.
then a date and time stamp should mark on lc sheet b28 as you created a macro for it.
then on register sheet the date and time stamp on cell b28 and receipt number a8 should be marked in the colum lc1 column aa because it's a first time of giving lc to the student. The format should be as 'receipt no. first line and date-time stamp on second line in a single cell as shown on register sheet.
If the student ask for lc second time then the same process but only the receipt/date-time in column lc2 column ab infront of register no. 2 respectoively. And so on till fifth time column lc3, lc4, lc5 respectively.
If another student ask lc then I will enter his/her register number in cell g7 lc sheet. Then the same process but only the receipt/date-time mark infront of his/her register number first time lc1, second time lc2, third time lc3, fourth time lc4 and fifth time lc5 respectively.

After enter the register number the lc certificate is generated automatically for which I have created a program. It's just for your information.

Hope this will clear my requirement to you.
Still if you have any doubt do ask me immediately.

Thank you.

So here again I may be overlooking something but it seems that what Pan has created would do that in which you are describing.
After testing the document Pan shared, the only thing missing is that Receipt Number which is your program, the rest seems to function in the sense that I enter a receipt for register 2 and each time it places it in the next receipt column up to five entries per register.

Your comments back to Pan.. Are you stating that the enter button should be hidden meaning you want to run it but not keep the button available. If that is the note then you could delete the button and just use the Macro list (ALT+F8) and select review which is the same as clicking that button.

The only real difference would be that you are not entering on G7 the registers number at which point you could alter the VBA Pan provided to run off of the cells you so desire instead.

Hi ELegault,
Good evening!

You have to enter the receipt no. manmally because I have not inserted a code for it in the workbook. In my original workbook it changes automatically after hitting enter key on cell a8 lc sheet. I don't want to hide enter key and use alt + f8 to run the macro.

After entering register no. in cell g7 lc sheet the lc is generated then after hitting on a8 lc sheet the receipt no. changes automatically.

Hope this will help you to solve the problem. I'm online so please feel free to ask any doubt to achieve the target.

Thank you,

Hi ELegault,
Bump!

Waiting to receive a solution eagerly because after solving this problem I have to work on it. My work is stopped. I have to complete the project at the earliest. So I humbly request you to suggest me a concrete solution at the earliest. It will be more better if I get it positively today.

I appreciate your guidance and kind cooperation to solve my problem.

Sorry for trouble.

Thank you,

Dear Pan,

Warm greetings from me!

First of all I apologize here once again for not understanding your code well before. It’s very close to my requirement now. Only few revisions have to do to make it complete.

Revisions require:

Do not hide any sheet, row because I have to enter another data in it. Here I have not entered that data.
Do not color any cells
Do not protect any sheet because I have another code in other macro to protect the workbook with password
No need to enter receipt no. manually. Now here I have inserted the code for automatic receipt no in a8 lc sheet after hitting enter key on a8. Please try to avoide this step to hit enter on a8. It should change automatically after hitting enter key on g7 lc sheet only to speed up the work.
Cell g4 is filled with another data and here it is blank. So do not use it in this programme.
No need of pop up message to record the receipt no and date-time. It should be automatic and must without the permission of user.
There is not a word ‘receipt no’ in my original workbook. Only the receipt no. is in cell a8 lc sheet. I have mentioned here it for you to that there is a receipt no. in cell a8 lc sheet. So avoide referencing cell a7 in the code.
After entering the register no. in cell g7 lc sheet there is no need to move the cursor on a8 for receipt no. After entering register no. in cell g7 lc sheet and hitting enter key on it (g7 lc sheet) the receipt no. a8 lc sheet should change automatically and cursor should be on g7 only.
Do not hide variable sheet. I will hide it later.
Do not limit the register number length because it increasing day by day endlessly.

Please do the above mentioned revisions in the updated workbook attach here only to avoide any confusion from my end.

Today I’m very glad to receive a positive solution close to my requirement from you. Hope to receive a final updated workbook today positively.

I’m feeling very guilty for not understanding the code before and prolonging it long.

Thank you and have a good evening.

Dear Pan,
Thank you for updated version. You have not used my attached workbook in last post 11 in which I have inserted the code for receipt number. I want that code to avoide any manual mistake in entering the receipt number which is very important. If it’s not possible to change the receipt number automatically after hitting enter key on g7 then after moving the curser on a8 I will hit enter key again to change receipt number without mistake.
The date in cell b28 is created by formula.
Now the cursor moves automatically from b28 to g7 which I don’t want. There is no need to move the cursor on b8.

A brief description of my program for your better understanding to modify the code.

After entering the register number in cell g7 the detail information of that particular student is transfer from register sheet to lc sheet.
Then after hitting enter key on cell a8 the receipt no. is change.
After hitting on cell a8 I want to record receipt no., date and time on register sheet in front of that particular student as you did now. Now only we have to run 2 macros for it.

Everything else is ok. After this revision I will give you my comments without fail. I have to insert the code in my original workbook so please tell me how to use the program briefly.

I appreciate you for your help.

Thank you.

Dear Pan,
Thank you but still there is a problem. I inserted the code in my file and now it’s giving error. It’s not working as per my requirement.

Please remove cell address output lc sheet g3 and g4 from the code because there is my data in original workbook.
The time is not working properly. It’s giving the same time for all lc.
The auto change of cursor from g7 to a8 is ok but from a8 to b28 is not require because it’s annoying for me. I have to check the data in the lc by scrolling the cursor line to line manually. So avoide it. I don’t want any auto change movement after selecting cell a8.
I will manually hit on cell a8 lc sheet to generate receipt no.
After hitting enter key on a8 I want to run a macro to record receipt no., date and time automatically. If possible else I will manually run a macro.

I request you to do the necessary amendment in the same workbook attached here for your perusal without fail for me to avoide any mistake from my end.

I’m optimistic to receive a final updated version today positively and close this prolonged thread.

Do remove unnecessary coding from the workbook to avoide my confusion. Please don’t hide any row/column/sheet.

Sorry for trouble and thank you for sparing your valuable time to fulfill my requirements to complete this program.

I appreciate you for the same.

Best of luck and have a nice day.

Bump!

Hi Pan and all of my excel expert friends,
Since last week I am working on this programme but still unable to achieve the target. Pan has provided a excellent code to record the receipt, date and time as per my requirement but I need some amendment in it to fulfil my requirements.

I appreciate Pan and ELegault for their valuable guidance to solve my problem. Due to this problem my programme is not completing.

Here once again I repeat my requirements simply in short:

I want to record receipt no., date and time of each LC given on register sheet infront of that particular register number which enter in cell g7 on LC sheet.
I have inserted a code to generate a receipt number automatically after hitting enter key on it cell a8 LC sheet to avoide any manual mistake in entering the receipt number which is very important.

I humbly once again request all of you friends please do spare your time for me to solve the problem and complete my project at the earliest.

I'm quite optimistic to receive a concrete solution from you today positively.

If you have any other idea to achieve this target do share it with me because I'm badly in a need of a solution for this problem since last week and now quite nervous for not achieveing the target.

I will appreciate your kind guidance and help to complete my project.

Thanking you in anticipation.

Dear Pan,
Excellent! Thank you. Now it’s very close to my requirement. It’s working fine but Still I require a little amendment in it to complete it. Now it’s 95% complete. I have clean the code as per my knowledge and requirement.

I like your new approach to enter the receipt number automatically. In a short coding you have achieve it. My first code was too lengthy than yours. I don’t want to enter the receipt number automatically after changing the cell g7. I want to run a macro manually to avoide unnecessary increment of receipt number after changing the cell. It’s very annoying for me. I have to take a print out of each LC to issue it to the student. So I have to scroll the cursor cell to cell and this time I don’t want to change the receipt number automatically. So do the necessary change in receipt number macro to run it manually.

I have change the time format to ‘hh:mm:ss am/pm’ but the seconds are not changing continuously as per hours and minutes.

Presently we have planned to issue 5 LC in our programme but if any student asked for sixth, seventh and so on LCs then we has to issue it. Mostly no one student asks for more than 5 LCS but sometimes it may possible. So remove the limit of 5 LCs from the code.

I have deleted the unwanted code from the workbook attached but I request you to go through it to avoide any mistake in the programme.

I don’t want to display any cursor movement on the screen to speed up the macro. So please use ‘application.screenupdating = false; application.enableevents = false in the beginning and true at the end of code.

I appreciate your excellent guidance to solve my problem.

Thank you and have a nice day.

Hi Pan,
Don't mind. I'm not understanding the code so that I'm not unable to amend it as per my requirement. This version is also not per my requirement. LC means leaving certificate; On register sheet there is a entry of each student with his details. I want to print out the LC to issue the student.

I have solved my problem using another code to achieve the target.

Thank you for taking trouble to solve my problem.

See you on the forum in future.

Best of luck and have a nice day.