Thank you sir. As I've said on previous occasions I like it because you make mistakes (like we all do) and you alter things to correct it which shows us how to correct our own work. If you hadn't done that we'd have to scour the code (which can be lengthy) and have no idea why it has gone wrong, just the error marked in the code, but not what has led to the mistake. Well done!
Thank you very much! I really appreciate it. All other videos only showed how to enter the date in a way that would change all entries, each day that you open the file. Very good tutorial. Thank you
Sir, thank you very much for clear our question. By user form I enter data to my excel sheet. The data shown in excel sheet can't be edited & deleted in excel sheet but only by user form command button. How it is done ? Please guide.
As in your example, my vba worked perfectly. How do you get the Date into say cells J2 and O2 if this same type data was further over on the same worksheet?
Hai... Dinesh this video is great and it works well... but in my case I want the date stamp on a particular cell only using VB, like a date on a form. Not in row or column. Pls help me out here.. Thnx
Hello I watched your videos they are so helpful thank you so much, Also I have a question? If I want a date for example today that change every two months automatically and also show me with color that it changed, how can i do it? thanks in advance!
Dear Sir, I am very impressed by your video lectures. You are a wonderful teacher. I need your help regarding following issue. I am a very basic learner of EXCEL VBA. I am creating an Attendance Sheet in MS -EXCEL. Actually I am running a small school. I want to take attendance of my students with following procedures. 1. When a student arrive to school, he will show his ID Card (containing Barcode, which is his roll number actually). 2. The Barcode Scanner will read the BARCODE from his ID Card and display his roll number into a TEXT BOX of a User Form. (This User form already has created by me). 3. Now the problem is that when scanner gives output to the text box, THE TEXT BOX SHOULD AUTOMATICALLY TRANSFER THIS VALUE TO EXCEL CELL AND THEN THE TEXT BOX SHOULD REFRESH AUTOMATICALLY AND READY TO TAKE ANOTHER INPUT. MEANS THAT I DON’T WANT TO HIT ANY KEY TO PROCESS THE REFRESHING OF TEXT BOX. 4. Is there any way to do that…… Thanks in advance. Your student KASHAN
Let's say you wish to transfer the data to sheet1 and column 1. Your command button on the userform will do the following on click: We find the next blank row to enter the roll number via scanner via textbox: nextblankrowrow = sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row sheet1.cells(nextblankrow,1)=textbox1 textbox1="" textbox1.setfocus Hope this helps.
@@Exceltrainingvideos Sir, I see following error. Sheet1.Cells(nextblankrow, 1) = TextBox1 This line has yellow now and following error is appeared Run Time Error 1004 Application-defined or object re-defined error. Also please note that i don't want to press "ENTER" key to transfer the data to Sheet1, Column1. Please help me. Regards, KASHAN
When I show Barcode through scanner each character of Roll No. transfer to each next line. The Roll# consists of 6 characters when i scan the barcode each character transfers to new line. i.e. a roll number takes 12 lines to accommodate 6 characters. Please help. thanks in advance KASHAN
The code works great. How do I make sure that when I go back and erase a cell's value the automatic date and type also update (display blank as all conditions are now not met)?
Cells(i, J).Value = Date & Quote & Time Cells(i, O).Value = Date & Quote & Time Put J and O in quotes. Replace Quote with an empty string as shown in the video. You may like to visit: familycomputerclub-dot-com/auomatic-date-time-entry-excel-vba.html Replace -dot- with .
Thank You Sir. I really liked your video and the simplicity in your accent. I am interested in VBA Tutorial so if you can provide me more details on VBA basics and advance then it will be great help.
Thanks for sharing, but I don't think you need for loop try below code - If Cells(Target.Row, 1) = "" Or Cells(Target.Row, 2) = "" Or Cells(Target.Row, 3) = "" Or Cells(Target.Row, 4) = "" Then Exit Sub Cells(Target.Row, 5).Value = Now
If I have start and finish dates and when I update my schedule daily with blue colour in each cell ,I have to change the finish dates everytime. Iam wondering if there's somehow i can insert my colour for update and my finish date or start change based on date at first row . Thanks in advance
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 2 To 100 If Cells(i, "A").Value "" And Cells(i, "B").Value "" And Cells(i, "C").Value "" And Cells(i, "D").Value "" And Cells(i, "E").Values "" Then Cells(i, "E").Value = Date & "" & Time Cells(i, "E").NumberFormat = "m/d/yyyy h:mm am/pm" End If Next Range("E:E").EntireColumn.AutoFit End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub
Sir is it possible to make the date and time non modifiable after once added by the operator so that they cannot modify it as per their need. Only administrator can modify. Is there any coding for that? Please suggest.
@@Exceltrainingvideos when I tried to add more columns (i need this to work for 4 additional columns), I kept getting compile errors and other errors as well.
Sir, i need both in time & arrival time. its possible in VBA? Like If i put a number then its shows in time, then after doing execution i put done, then its shows arrival time.
Hi! I found your video very useful to what I needed. I just want to know if there is a shortcut if I have around 71 cells to be completed with data and I need that cell to automatically have the date. Is there a shortcut because having to type codes for all 71 cells is a bit long. Thank you!
Hello Dinesh. I have tried the above the code to get the date and time auto populate using the code. can i lock the E column with password. When i try to do that using protect sheet it shows error. How i do that in codes. Pls reply i'm waiting
This link will guide: www.exceltrainingvideos.com/tag/automatically-lock-worksheet-cells-after-data-entry-using-vba/ Or search www.exceltrainingvideos.com
Hello This code crashes under MS Excel 2016 for some reason, Checked in forums and people got the same issue with different codes when moving to an excel version to another Why is that?
Hi Sir,i have to add same auto date functions in more colums, how can i add more macros in this entire Sheet to Mark date n time for more colums data. please help.
This is the code im using; Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 8 To 1200 If Cells(i, "B").Value "" Then Cells(i, "G").Value = Time Cells(i, "G").NumberFormat = "h : MM AM/PM" End If Next Range("E:E").EntireColumn.AutoFit End Sub Excel keeps showing "error 28" what sould be de problem??
What if I wanted to put an end time to column F? Like there's a start time and end time to each row. But I don't want to click on any buttons, just automatically gives me the time I started and finished doing the task in 1st row?
The link to this VBA tutorial will help: www.exceltrainingvideos.com/tag/timer-in-excel-using-vba/ When you have some data in the last used cell of row f, you can call the end timer.
Thank you for your response. 😊 How about a time and motion file without the need to add buttons for start timer and stop timer. The VBA that automatically starts the time when i enter something at column A2 then stops the timer when I enter something in A3. 😊 sorry newbie here.
Hi, I copied the VBA code from your website but for some reason the date & time is not being stamped after completing values on the cells, there is no error msg either, can you please advise if I am doing anything incorrectly? thank you
I have DTPicker in my form. When I running a macro de format is not date and tried differents way and I'm not able to format it. Can you could help me? tks Carlos
What if I want to call date and time from a function? For example, when preparing the invoice immediately when I open my document the today's date &time appears? and would be stored with the invoice
This lin will help: www.exceltrainingvideos.com/how-to-auto-run-vba-project-when-excel-workbook-is-opened/ To enter date and time in let's say Range A1 you would add the code: Range("A1").value=Date & " " & Time
Dear Sir, I tried with your codes exactly as you directed but was not successful, what could be the reason and How can I correct it? The code I have done is given below; Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 2 To 100 If Cells(i, "A").Value " " And Cells(i, "B").Value " " And Cells(i, "C").Value " " And Cells(i, "D").Value = " " Then Cells(i, "D").Value = Date & " " & Time Cells(i, "D").NumberFormat = "m/d/yyyy h:mm AM/PM" End If Next Range("D:D").EntireColumn.AutoFit End Sub I appreciate if you could help me understand the error I have done Best regards, Gamini
Dear mr. Dinesh, Thanks for that helpful info, Sir I need your help to have the time stamp when the date in the cell changed "if the cell not empty", How can I do That? Thanks in advanced, Hamid
Sir, if i text box2 input time is less then input textbox1, then give error msg in excel... exm textbox1= 00:20, textbox2=23:30... then give error msg . .
If TimeValue(Me.TextBox2.Value) < TimeValue(Me.TextBox1.Value) Then msgbox" pls enter correct time,textbox2 value is always greater then textbox1" end if exit sub sir, its work when i input in textbox1= 10:00 ' (am)and textbox2=09:00 '(am) but when i input in textbox1=00:20 '(am) and text box 2= 23:30 '(pm) its not work
Nice Tutorial it was very useful for one of my project but i need infinity value as you used i = 2 to 100...i need infinity instead of 100... please guide me Regards
Hi sir, i have faced a problem with this VBA....i have done this task manually & its properly working.. But i protected the Worksheet when its not working,, Its says, 1004 error. So how can i fix up it!!?
I need help my scenario: I will scan a badge id(barcode) and the output will appear on the cell. However, the barcode I scanned will give me numbers as the output. How can I automatically change the numbers into the person's name ? hope you understand my question :)
i need a help the date is updating correctly but when i close excel file and reopen it there is no effect of code is happening ,please do help me on this
Sir, May you help me for saving and updating a date column in excel worksheet with date format as "DD/MM/YYYY" through Excel user form even system date format setting in US Date format.
Hi, I have learned a lot from your videos, and want to thank you: however, I am having one issue. I've am using VBA to transfer data from the Data Entry Sheet to a log sheet. Everything is transferring over correctly except for the. time. Example: I imput the name, route number, and start time. The name and route number transfers over correctly but the time transfers over as a DATE and Time. I input 2:00 PM and when I click the button 1/19/1900 12:00 is transferred but I need the 2:00 Pm to transfer. can you help me?
i have 2 problems that following:- - The vb always shows debugging error 438?!, and shows yellow highlight in sentence no3 "if Cells (i, "A")......etc" - When I put words in column D, it is refusing to show the date on cells of E Thanks
If x=5 then Alternatively do a search at www.exceltrainingvideos.com You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Dear Sir, I am trying to build a DIAGNOSTIC CENTRE MANAGEMENT SOFTWARE in Excel userform VBA. In that I have a List box with 7 columns and over 1000 rows of data. I want your help in editing an individual cell of a column say column 7, multiple rows that are a part of a group i.e Biochemistry investigations in column 7 sfter selecting Biochemistry form a drop down list. This group Biochemistry contains about 500 rows. I should also be able to select all in the BIOCHEMISTRY group and set a uniform value for all those investigations in column 7 or for individual cells in column 7 I want to be able tro edit the column contents directly from the list box. Can you please help. Regards
hi dinesh, need some help. I have the basic understanding of how this works. HOwever what i require is multiple time stamps in multi[ple cells. Again the date and time stamp should only appear if the specified cell has data input. e.g. A2 = Apple B2 = Date, C2 = OUT of STOCK - D2 = date etc. Please help. thank you.
As I've pointed out many times, if you just copy and paste the macro code from my website, it may not work because characters like 'quotes' are different. So please check your code thoroughly before executing it.
Hi Dinesh, I Had Made Code Seeing Your Video as Per My Requirement.. But I Am Getting Error of "Out of Stack Space" "Run Time Error 28" Kindly Pls Help on Same.. Below is The Code Which I Had Entered in VB Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 2 To 100 If Cells(i, "D").Value = "Start" And Cells(i, "D").Value = "Start" Then Cells(i, "F").Value = Date & "" & Time Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM" End If Next Range("F:F").EntireColumn.AutoFit End Sub
hi mr. dinesh. I would like to ask for your help because i keep getting run-time error '13' type mismatch. and it points out to this in the vba: If Cells(i, "D").Value "" And Cells(i, "E").Value "" And Cells(i, "F").Value "" And Cells(i, "G").Value = "" Then i have no idea where the problem is. i'm not an IT person. i hope you can help me. i'm doing this for our seminar registration sheet so that the time they logged can be recorded automatically as soon as they typed their names. thank you! :)
Have a look at this link: www.exceltrainingvideos.com/automatic-date-time-entry-using-excel-vba/ Of course you can also do a search at: www.exceltrainingvideos.com
Hi Sir, If i add some data in other cell then the clock in a particular cell should stop, but other cells should tick around, may i know what script can i use? the script what is used is below. Sub runClock() Range("A1:A10").Value = Now() If clockon = True Then Application.OnTime Now + TimeValue("00:00:01"), "runClock" End If End Sub
Sir! This is Franklin. I'm not a CS student. But I'm interested in developing software program for bill for my medical store. Somehow, by watching videos of VBA code, I developed bill. But I can't write coding for expiry date. If I enter number format with four digit like 0121, it should be entered as mm/yy that 01/21. Pl. help me to do. Thank you Franklin
hi sir, can you give me a solution for the following problem. there is a list of items. start started starting to be started how many items are there starting with "start"? the formula countif(a2:a5,"*stat*") showing 4 items as answer. but there are only 3 items, how can I get the exact answer. please suggest me in this regarding.
hello sir, i really loved and used this post, ,my quetion is, and i will be happy if you can answer me , you give us the "if" rulls will happend so i will receive the date and time in the choosen cell, but what if i deleted the information in those cells,? how can? or what is the code need to add so the date and time will delete also as it has added when the cells were filled with data? i hope i am clear, i will explain agian my self .When the condition is met, then I get as a result the date, but I want that as soon as the condition is not met that the situation will return to normal and the date will be also deleted or will be written something like "choose date", maybe it's need to add "if else" or "else" function for that?
In a worksheet: =DATE(YEAR(A2),MONTH(A2), DAY(A2+15)) The date is in cell A2. In VBA: “=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,DAy(RC[-1]+15))” R=row C=column, negative value means to the LEFT. The date is one column to the LEFT. www.exceltrainingvideos.com/convert-date-formats/
my entire column is updating the time and date. also, entering new data updates my entire date/time column instead of each column having its own time stamp. HELP????
I copied the VBA from your website and changed a few things. I didn't want to display the time entry so i took that out. Also i changed the ""And Cells" around since my date is in column A instead of column E. My question is; my code seems to work fine on row 2 but when i input my data in the last column on row 3 or higher the computer starts to think and causes excel not to respond. Please help.
Hi Sir, Thanks for help I done it but I have a problem. When I make a file Visual Basic & I edit worksheet and is change automatic time & date but when I close this file and I open again this file than is not change automatic time & date so I need edit again Visual Basic. Please help me to do how I can get one worksheet file change automatic time & date without edit all time Visual Basic. Thanks
i did simple code but its not working Dim i As Integer For i = 4 To 10 If Cells(i, "G").Value = True Then Cells(i, "H").Value = Date & "" & Time End If i = i + 1 Next i have drop down list in G column (True or False) then excel become not responding i really don't know whats wrong? could you help?
Abu Taha First you'll have to select 'True' or 'False' for the column G and then run the following code: Dim i As Integer For i = 4 To 10 If Cells(i, "G").Value = True Then Cells(i, "H").Value = Date & "" & Time End If Next i
When I run this code (see below) it works but it keeps changing every time to the new time. I need it to only update ether new column what am I doing wrong? For i =2 to 1000If cells (I, "B").value ""ThenCells (i, "D").Value = TimeCells(i, "D").numberformat = "h:mm:ss AM/PM"End ifNext i
Thank you sir. As I've said on previous occasions I like it because you make mistakes (like we all do) and you alter things to correct it which shows us how to correct our own work. If you hadn't done that we'd have to scour the code (which can be lengthy) and have no idea why it has gone wrong, just the error marked in the code, but not what has led to the mistake. Well done!
Appreciate your feedback.
You are the God of Macros Mr. Dinesh.,,,,!!!!
Thanks. Please share the tutorials with your friends.
Thank you very much! I really appreciate it. All other videos only showed how to enter the date in a way that would change all entries, each day that you open the file. Very good tutorial. Thank you
You are welcome! Please share with your friends too.
Works Perfectly!! Just don't Copy & past...
Thanks for useful video.
Your explanation is awesome with complete information in short time.
Sir, thank you very much for clear our question.
By user form I enter data to my excel sheet. The data shown in excel sheet can't be edited & deleted in excel sheet but only by user form command button.
How it is done ? Please guide.
I have been looking and needing for this since last year! Thanks for making this video my friend. Hope I can use it correctly. Many thanks!
You are like a Boss, Thanks for the awesome tutorial
As in your example, my vba worked perfectly. How do you get the Date into say cells J2 and O2 if this same type data was further over on the same worksheet?
thank u sir i learn so much
Hai... Dinesh this video is great and it works well... but in my case I want the date stamp on a particular cell only using VB, like a date on a form. Not in row or column. Pls help me out here.. Thnx
Works wonderfully. Thank you so much.
Hello I watched your videos they are so helpful thank you so much,
Also I have a question? If I want a date for example today that change every two months automatically and also show me with color that it changed, how can i do it?
thanks in advance!
Dear Sir,
I am very impressed by your video lectures. You are a wonderful teacher. I need your help regarding following issue. I am a very basic learner of EXCEL VBA.
I am creating an Attendance Sheet in MS -EXCEL. Actually I am running a small school. I want to take attendance of my students with following procedures.
1. When a student arrive to school, he will show his ID Card (containing Barcode, which is his roll number actually).
2. The Barcode Scanner will read the BARCODE from his ID Card and display his roll number into a TEXT BOX of a User Form. (This User form already has created by me).
3. Now the problem is that when scanner gives output to the text box, THE TEXT BOX SHOULD AUTOMATICALLY TRANSFER THIS VALUE TO EXCEL CELL AND THEN THE TEXT BOX SHOULD REFRESH AUTOMATICALLY AND READY TO TAKE ANOTHER INPUT. MEANS THAT I DON’T WANT TO HIT ANY KEY TO PROCESS THE REFRESHING OF TEXT BOX.
4. Is there any way to do that……
Thanks in advance.
Your student
KASHAN
Let's say you wish to transfer the data to sheet1 and column 1. Your command button on the userform will do the following on click: We find the next blank row to enter the roll number via scanner via textbox:
nextblankrowrow = sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
sheet1.cells(nextblankrow,1)=textbox1
textbox1=""
textbox1.setfocus
Hope this helps.
@@Exceltrainingvideos Sir,
I see following error.
Sheet1.Cells(nextblankrow, 1) = TextBox1
This line has yellow now and following error is appeared
Run Time Error 1004
Application-defined or object re-defined error.
Also please note that i don't want to press "ENTER" key to transfer the data to Sheet1, Column1.
Please help me.
Regards,
KASHAN
When I show Barcode through scanner each character of Roll No. transfer to each next line. The Roll# consists of 6 characters when i scan the barcode each character transfers to new line. i.e. a roll number takes 12 lines to accommodate 6 characters. Please help. thanks in advance
KASHAN
Dear Sir, Finally, I have successfully solved my problem. Thank you so much. Regards, KASHAN
Super Great! Thank you so much for your kind help!
You are welcome. Please share the knowledge with your friends also on social media.
The code works great.
How do I make sure that when I go back and erase a cell's value the automatic date and type also update (display blank as all conditions are now not met)?
Cells(i, J).Value = Date & Quote & Time
Cells(i, O).Value = Date & Quote & Time
Put J and O in quotes. Replace Quote with an empty string as shown in the video.
You may like to visit:
familycomputerclub-dot-com/auomatic-date-time-entry-excel-vba.html
Replace -dot- with .
sir your give vba code successfully worked but undo Function not working after this.
Thank You Sir. I really liked your video and the simplicity in your accent. I am interested in VBA Tutorial so if you can provide me more details on VBA basics and advance then it will be great help.
Sure I will! This link will help to learn basics and advanced features in Excel VBA: www.exceltrainingvideos.com/excel-vba/excel-2003-vba/
Thanks for sharing, but I don't think you need for loop
try below code
-
If Cells(Target.Row, 1) = "" Or Cells(Target.Row, 2) = "" Or Cells(Target.Row, 3) = "" Or Cells(Target.Row, 4) = "" Then Exit Sub
Cells(Target.Row, 5).Value = Now
Thank you very much, it is very useful !, I try it and it works well.
Incrível esse vídeo - Consegui inserir essa função na minha planilha. Parabéns.
Great work sir thanks a lot
hi good video but if i dont want automatic if i want a series of buttons to add time,date and a new invoice number? please help and shome how
If I have start and finish dates and when I update my schedule daily with blue colour in each cell ,I have to change the finish dates everytime.
Iam wondering if there's somehow i can insert my colour for update and my finish date or start change based on date at first row .
Thanks in advance
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 2 To 100
If Cells(i, "A").Value "" And Cells(i, "B").Value "" And Cells(i, "C").Value "" And Cells(i, "D").Value "" And Cells(i, "E").Values "" Then
Cells(i, "E").Value = Date & "" & Time
Cells(i, "E").NumberFormat = "m/d/yyyy h:mm am/pm"
End If
Next
Range("E:E").EntireColumn.AutoFit
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Hi Sir, I want to view this macros recording later.Where is the data getting captured?
Sir is it possible to make the date and time non modifiable after once added by the operator so that they cannot modify it as per their need. Only administrator can modify. Is there any coding for that? Please suggest.
Is there a way to use this same code for MULTIPLE columns? Can more than just columns A and E be used for this
Try it out!
@@Exceltrainingvideos i did and i wasn't able to add additional columns
@@Exceltrainingvideos when I tried to add more columns (i need this to work for 4 additional columns), I kept getting compile errors and other errors as well.
Sir, i need both in time & arrival time. its possible in VBA?
Like If i put a number then its shows in time, then after doing execution i put done, then its shows arrival time.
Sir, where can I get a book 📚 on VBA programming for office 2007?
Try this: freecomputerbooks.com/Microsoft-Office-Excel-2007.html
VERY NICE
Thank You Sir
Very helpful thank you
Hi! I found your video very useful to what I needed. I just want to know if there is a shortcut if I have around 71 cells to be completed with data and I need that cell to automatically have the date.
Is there a shortcut because having to type codes for all 71 cells is a bit long.
Thank you!
Hello Dinesh. I have tried the above the code to get the date and time auto populate using the code. can i lock the E column with password. When i try to do that using protect sheet it shows error. How i do that in codes. Pls reply i'm waiting
This link will guide: www.exceltrainingvideos.com/tag/automatically-lock-worksheet-cells-after-data-entry-using-vba/
Or search www.exceltrainingvideos.com
its helpful, Thanks a lot
Thank u sir 👍.
Most welcome
thank you Dinesh
You're welcome. Please share with your friends.
if i change the date in the excel sheet remaining all the page formulas should be updated according to the date what is the formula
sir
what i wonder if can i put the date of modifying of an existing data at each change
What did you press after And cells to include cells A through E?
thank you very much sir.
if possible that i want automatic generate retirement date while entry data in excel vba
These Excel tutorials will help:
www.exceltrainingvideos.com/date-functions/
www.exceltrainingvideos.com/subtract-dates-in-excel/
Hello
This code crashes under MS Excel 2016 for some reason, Checked in forums and people got the same issue with different codes when moving to an excel version to another
Why is that?
This link will help: www.exceltrainingvideos.com/automatic-date-time-entry-using-excel-vba/
Hi Sir,i have to add same auto date functions in more colums, how can i add more macros in this entire Sheet to Mark date n time for more colums data.
please help.
This is the code im using;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 8 To 1200
If Cells(i, "B").Value "" Then
Cells(i, "G").Value = Time
Cells(i, "G").NumberFormat = "h : MM AM/PM"
End If
Next
Range("E:E").EntireColumn.AutoFit
End Sub
Excel keeps showing "error 28" what sould be de problem??
What if I wanted to put an end time to column F? Like there's a start time and end time to each row. But I don't want to click on any buttons, just automatically gives me the time I started and finished doing the task in 1st row?
The link to this VBA tutorial will help: www.exceltrainingvideos.com/tag/timer-in-excel-using-vba/
When you have some data in the last used cell of row f, you can call the end timer.
Thank you for your response. 😊 How about a time and motion file without the need to add buttons for start timer and stop timer. The VBA that automatically starts the time when i enter something at column A2 then stops the timer when I enter something in A3. 😊 sorry newbie here.
Hi, I copied the VBA code from your website but for some reason the date & time is not being stamped after completing values on the cells, there is no error msg either, can you please advise if I am doing anything incorrectly? thank you
Kamal Hussain
Same problem here, I also copied the code from the website but nothing happens :-(
I have DTPicker in my form. When I running a macro de format is not date and tried differents way and I'm not able to format it. Can you could help me?
tks
Carlos
What if I want to call date and time from a function? For example, when preparing the invoice immediately when I open my document the today's date &time appears? and would be stored with the invoice
This lin will help:
www.exceltrainingvideos.com/how-to-auto-run-vba-project-when-excel-workbook-is-opened/
To enter date and time in let's say Range A1 you would add the code:
Range("A1").value=Date & " " & Time
link
Please help. How to use time criteria while extracting email from Outlook in Excel.
Search my channel using 'time' as keyword.
Not able to find . Please help
Hi,
Thanks very good your halpfull video clip.
Can you help me please.
How fix Hijri to Gregorian calendar conversion each cell in excel ??
Your voice is same as Guruji from Sacred Games.
What's that?
I urgently need the VBA Script to make Date and Time Stamping automatically.
regards,
This link should help: www.exceltrainingvideos.com/automatic-date-time-entry-using-excel-vba/
Dear Sir,
I tried with your codes exactly as you directed but was not successful, what could be the reason and How can I correct it? The code I have done is given below;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 2 To 100
If Cells(i, "A").Value " " And Cells(i, "B").Value " " And Cells(i, "C").Value " " And Cells(i, "D").Value = " " Then
Cells(i, "D").Value = Date & " " & Time
Cells(i, "D").NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("D:D").EntireColumn.AutoFit
End Sub
I appreciate if you could help me understand the error I have done
Best regards,
Gamini
Dear mr. Dinesh,
Thanks for that helpful info,
Sir I need your help to have the time stamp when the date in the cell changed "if the cell not empty",
How can I do That?
Thanks in advanced,
Hamid
Sir,
if i text box2 input time is less then input textbox1, then give error msg in excel... exm textbox1= 00:20, textbox2=23:30... then give error msg .
.
Share your code here.
If TimeValue(Me.TextBox2.Value) < TimeValue(Me.TextBox1.Value) Then
msgbox" pls enter correct time,textbox2 value is always greater then textbox1"
end if
exit sub
sir, its work when i input in textbox1= 10:00 ' (am)and textbox2=09:00 '(am)
but when i input in textbox1=00:20 '(am) and text box 2= 23:30 '(pm) its not work
@@KnowEverythin What does this code do:
TimeValue(Me.TextBox2.Value)
sir actually I am not using any code, sir pls write the code for this ....
Private Sub CommandButton1_Click()
UserForm1.TextBox1.Value = Format(TextBox1, "hh:mm:ss AM/PM")
UserForm1.TextBox2.Value = Format(TextBox2, "hh:mm:ss AM/PM")
mytime1 = TimeValue(UserForm1.TextBox1.Value)
mytime2 = TimeValue(UserForm1.TextBox2.Value)
mytime3 = mytime1 - mytime2
UserForm1.TextBox3.Value = Format(mytime3, "hh:mm:ss")
End Sub
Nice Tutorial
it was very useful for one of my project but i need infinity value as you used i = 2 to 100...i need infinity instead of 100...
please guide me
Regards
Hi sir, i have faced a problem with this VBA....i have done this task manually & its properly working.. But i protected the Worksheet when its not working,, Its says, 1004 error. So how can i fix up it!!?
nice class, im not so good for excel!!
You can do it!
I need help
my scenario:
I will scan a badge id(barcode) and the output will appear on the cell. However, the barcode I scanned will give me numbers as the output. How can I automatically change the numbers into the person's name ?
hope you understand my question :)
No idea.
In first sheet date automatically change then second sheet another column every day and sate wise rows with data change but how
Search www.exceltrainingvideos.com/ or this channel goo.gl/5Jx1NP
i need a help the date is updating correctly but when i close excel file and reopen it there is no effect of code is happening ,please do help me on this
The code work BUT i had to delete the timestamp in order to update.. cant it auto update when any cell had make a change (e.g if tht cell got edited)?
Use worksheet 'change' event.
Sir, May you help me for saving and updating a date column in excel worksheet with date format as "DD/MM/YYYY" through Excel user form even system date format setting in US Date format.
Try this code:
Range("A1").NumberFormat = ("dd - mm - yyyy")
This link will help: www.exceltrainingvideos.com/format-dates-in-excel-with-vba/
How can i fill to the last row 1048576
Hi, I have learned a lot from your videos, and want to thank you: however, I am having one issue. I've am using VBA to transfer data from the Data Entry Sheet to a log sheet. Everything is transferring over correctly except for the. time. Example: I imput the name, route number, and start time. The name and route number transfers over correctly but the time transfers over as a DATE and Time. I input 2:00 PM and when I click the button 1/19/1900 12:00 is transferred but I need the 2:00 Pm to transfer. can you help me?
You are not supposed to make any input! The date and time is taken automatically from the system.
Okay, but I don't want the time from the system, I want the time I have assigned, is it possible?
Patrick Chung www.exceltrainingvideos.com/time-functions/
is there a DTPicker for excel 2016 using windows 10 64 bit?
No.
i have 2 problems that following:-
- The vb always shows debugging error 438?!, and shows yellow highlight in sentence no3 "if Cells (i, "A")......etc"
- When I put words in column D, it is refusing to show the date on cells of E
Thanks
This video time: 4:41, what I have to write if I don't want the value is "not equal to empty", how I key in the specific value in code?
If x=5 then
Alternatively do a search at www.exceltrainingvideos.com
You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Dear Sir, I am trying to build a DIAGNOSTIC CENTRE MANAGEMENT SOFTWARE in Excel userform VBA. In that I have a List box with 7 columns and over 1000 rows of data. I want your help in editing an individual cell of a column say column 7, multiple rows that are a part of a group i.e Biochemistry investigations in column 7 sfter selecting Biochemistry form a drop down list. This group Biochemistry contains about 500 rows. I should also be able to select all in the BIOCHEMISTRY group and set a uniform value for all those investigations in column 7 or for individual cells in column 7
I want to be able tro edit the column contents directly from the list box. Can you please help.
Regards
Search www.exceltrainingvideos.com/ step by step. I would suggest you think through the solution again step by step and write those steps.
hi dinesh, need some help. I have the basic understanding of how this works. HOwever what i require is multiple time stamps in multi[ple cells. Again the date and time stamp should only appear if the specified cell has data input. e.g. A2 = Apple B2 = Date, C2 = OUT of STOCK - D2 = date etc. Please help. thank you.
I need help, how can I do to, when I click in a check-box automatically apears the exact date of the moment when I clicked on the check-box?? Thanks
As I've pointed out many times, if you just copy and paste the macro code from my website, it may not work because characters like 'quotes' are different. So please check your code thoroughly before executing it.
+Dinesh Kumar Takyar Hi Dinesh ! when i use you this code then excel can't use function ctrl+z ,please help me fix this issue , thank you !
yes please help me same problem happen with my data sheet. undo Function not working.
Please I want to automatically insert the date and time in about five cells. Currently I can only do for two cells. Please help me out
Sub enterDateTime()
Dim cel As Range
For Each cel In Range("A1:A5")
cel.Value = Now
Next
End Sub
Hi Dinesh,
I Had Made Code Seeing Your Video as Per My Requirement.. But I Am Getting Error of "Out of Stack Space" "Run Time Error 28"
Kindly Pls Help on Same.. Below is The Code Which I Had Entered in VB
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 2 To 100
If Cells(i, "D").Value = "Start" And Cells(i, "D").Value = "Start" Then
Cells(i, "F").Value = Date & "" & Time
Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"
End If
Next
Range("F:F").EntireColumn.AutoFit
End Sub
Check against this link: www.exceltrainingvideos.com/automatic-date-time-entry-using-excel-vba/
Hi, why does my date still changes when I enter data on the next row?
Share your code.
same here
hi mr. dinesh. I would like to ask for your help because i keep getting run-time error '13' type mismatch. and it points out to this in the vba: If Cells(i, "D").Value "" And Cells(i, "E").Value "" And Cells(i, "F").Value "" And Cells(i, "G").Value = "" Then
i have no idea where the problem is. i'm not an IT person. i hope you can help me. i'm doing this for our seminar registration sheet so that the time they logged can be recorded automatically as soon as they typed their names. thank you! :)
Have a look at this link: www.exceltrainingvideos.com/automatic-date-time-entry-using-excel-vba/
Of course you can also do a search at: www.exceltrainingvideos.com
thank you so much for responding mr. dinesh. I will get back to you if i've been successful. :)
is it possible without VBA?
This Code worked but it kept showing error in the Cells(i, "AD").NumberFormat = "m/d/yyyy h:mm AM/PM" and is asking to debug the same.
What if I want to update my previous cell which already has date and time. . . . ???
This link will help: www.exceltrainingvideos.com/update-record-in-database-automatically/
Or search www.exceltrainingvideos.com
Thank you so much!!! :)
When I save and close it then open it again I can no longer see the timestamp when I enter into an empty cell?
Hi Sir,
If i add some data in other cell then the clock in a particular cell should stop, but other cells should tick around, may i know what script can i use? the script what is used is below.
Sub runClock()
Range("A1:A10").Value = Now()
If clockon = True Then
Application.OnTime Now + TimeValue("00:00:01"), "runClock"
End If
End Sub
How fix hijri to gregogian calendar conversion cach cell in excel ?
dear sir i want show real time on userfrom lable, how can i show
Here's the solution. Share it with your friends too:
Private Sub UserForm_Initialize()
UserForm1.Label1.Caption = Format(Time, "hh:mm")
End Sub
Sir! This is Franklin. I'm not a CS student. But I'm interested in developing software program for bill for my medical store. Somehow, by watching videos of VBA code, I developed bill. But I can't write coding for expiry date. If I enter number format with four digit like 0121, it should be entered as mm/yy that 01/21. Pl. help me to do.
Thank you
Franklin
Please search my channel using the keyword 'dates' or 'date' to discover at least 10 videos on this topic in Excel and find a solution.
Thank you
Can do yo u have the maximize version of this video
This link might help: familycomputerclub.com/auomatic-date-time-entry-excel-vba.html
Amazing!!
can u plz tell me how to make a list of pdf which store in my pc and copy them in a pen drive
Search www.exceltrainingvideos.com or watch my latest video.
hi sir, can you give me a solution for the following problem.
there is a list of items.
start
started
starting
to be started
how many items are there starting with "start"?
the formula
countif(a2:a5,"*stat*") showing 4 items as answer.
but there are only 3 items, how can I get the exact answer.
please suggest me in this regarding.
Filter and count.
hello sir, i really loved and used this post,
,my quetion is, and i will be happy if you can answer me , you give us the "if" rulls will happend so i will receive the date and time in the choosen cell, but what if i deleted the information in those cells,? how can? or what is the code need to add so the date and time will delete also as it has added when the cells were filled with data? i hope i am clear, i will explain agian my self .When the condition is met, then I get as a result the date, but I want that as soon as the condition is not met that the situation will return to normal and the date will be also deleted or will be written something like "choose date", maybe it's need to add "if else" or "else" function for that?
if i type Time it keeps changing to TIME (Capitals) - any ideas why ?
can you help me? how can add auto 15days, like for example today is 15 i like to auto + 15 days then will see 30
In a worksheet: =DATE(YEAR(A2),MONTH(A2), DAY(A2+15))
The date is in cell A2.
In VBA:
“=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,DAy(RC[-1]+15))”
R=row
C=column, negative value means to the LEFT.
The date is one column to the LEFT.
www.exceltrainingvideos.com/convert-date-formats/
my entire column is updating the time and date. also, entering new data updates my entire date/time column instead of each column having its own time stamp. HELP????
I copied the VBA from your website and changed a few things. I didn't want to display the time entry so i took that out. Also i changed the ""And Cells" around since my date is in column A instead of column E. My question is; my code seems to work fine on row 2 but when i input my data in the last column on row 3 or higher the computer starts to think and causes excel not to respond. Please help.
Joel Marable I'm having same problem.
Excel 2010
Me also
Same Problem here! any Tipp and solution ?
Hi Sir, Thanks for help I done it but I have a problem. When I make a file Visual Basic & I edit worksheet and is change automatic time & date but when I close this file and I open again this file than is not change automatic time & date so I need edit again Visual Basic. Please help me to do how I can get one worksheet file change automatic time & date without edit all time Visual Basic.
Thanks
i did simple code but its not working
Dim i As Integer
For i = 4 To 10
If Cells(i, "G").Value = True Then
Cells(i, "H").Value = Date & "" & Time
End If
i = i + 1
Next
i have drop down list in G column (True or False)
then excel become not responding i really don't know whats wrong? could you help?
Abu Taha First you'll have to select 'True' or 'False' for the column G and then run the following code:
Dim i As Integer
For i = 4 To 10
If Cells(i, "G").Value = True Then
Cells(i, "H").Value = Date & "" & Time
End If
Next i
Dinesh Kumar Takyar Thanks a lot
thank you !!
I love you Dinesh Kumar Takyar!
When I run this code (see below) it works but it keeps changing every time to the new time. I need it to only update ether new column what am I doing wrong? For i =2 to 1000If cells (I, "B").value ""ThenCells (i, "D").Value = TimeCells(i, "D").numberformat = "h:mm:ss AM/PM"End ifNext i
You need to check the condition, if the receiving field "is empty" ( ="" not "")