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
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.
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.
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!
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)?
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
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
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
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?
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!
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
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.
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
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????
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
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.
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
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??
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
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?
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 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.
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 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!!?
Based on this, i customised and put date value in column A and start time in Column C and end time in Column D (based on value of E) Now if i wish to calculate the time difference between D1 and C2 with a condition that A2 and A1 are same..how can i do this..pls assist
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
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
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
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 :)
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
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.
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.
@@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.
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
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
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
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?
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
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
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 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.
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 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
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 .
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?
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/
Hello Mr. Dinesh, thank you for all those great videos. I am looking for a simple code that on open the workbook the date is shown but always return to the first day from the current Month e.g. 1-8-2018 even if the current date is 3-8-2018, can you help me please So my code so far is:Range("BF409").Value = DateRange("BF409").Numberformat = "d-m-yyyy" But "d" always must be "1 How do I do that?
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.
Thanks for useful video.
Your explanation is awesome with complete information in short time.
Works Perfectly!! Just don't Copy & past...
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
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/
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.
thank u sir i learn so much
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!
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)?
Thank you very much, it is very useful !, I try it and it works well.
Super Great! Thank you so much for your kind help!
You are welcome. Please share the knowledge with your friends also on social media.
Incrível esse vídeo - Consegui inserir essa função na minha planilha. Parabéns.
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
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
What did you press after And cells to include cells A through E?
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
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?
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
Hi,
Thanks very good your halpfull video clip.
Can you help me please.
How fix Hijri to Gregorian calendar conversion each cell in excel ??
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!
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
thank you Dinesh
You're welcome. Please share with your friends.
Great work sir thanks a lot
Thank u sir 👍.
Most welcome
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.
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
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????
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
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.
Hi Sir, I want to view this macros recording later.Where is the data getting captured?
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
VERY NICE
Thank You Sir
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??
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
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?
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 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.
Very helpful 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.
+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.
Your voice is same as Guruji from Sacred Games.
What's that?
sir
what i wonder if can i put the date of modifying of an existing data at each change
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
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
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!!?
its helpful, Thanks a lot
Based on this, i customised and put date value in column A and start time in Column C and end time in Column D (based on value of E)
Now if i wish to calculate the time difference between D1 and C2 with a condition that A2 and A1 are same..how can i do this..pls assist
nice class, im not so good for excel!!
You can do it!
thank you very much sir.
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
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/
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
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
Sir, where can I get a book 📚 on VBA programming for office 2007?
Try this: freecomputerbooks.com/Microsoft-Office-Excel-2007.html
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.
When I save and close it then open it again I can no longer see the timestamp when I enter into an empty cell?
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.
How fix hijri to gregogian calendar conversion cach cell in excel ?
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 "")
I love you Dinesh Kumar Takyar!
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/
Hi, why does my date still changes when I enter data on the next row?
Share your code.
same here
if i type Time it keeps changing to TIME (Capitals) - any ideas why ?
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/
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 ?
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.
Thank you so much!!! :)
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
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
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 :-(
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/
Amazing!!
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
Mine just keeps refreshing the whole column to the current time and date?
same as me. Please did you find a solution?
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
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
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
is it possible without VBA?
How can i fill to the last row 1048576
is there a DTPicker for excel 2016 using windows 10 64 bit?
No.
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 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.
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.
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.
thank you !!
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. :)
Am facing error in numberformat line how to rectify that
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
Can do yo u have the maximize version of this video
This link might help: familycomputerclub.com/auomatic-date-time-entry-excel-vba.html
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
I don't want to change the date every time. Because date should be static at the time of entry.
Initialize the date to the value you need.
how to auto insert date & time in table form
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.
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/
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/
Hello Mr. Dinesh, thank you for all those great videos. I am looking for a simple code that on open the workbook the date is shown but always return to the first day from the current Month e.g. 1-8-2018 even if the current date is 3-8-2018, can you help me please So my code so far is:Range("BF409").Value = DateRange("BF409").Numberformat = "d-m-yyyy" But "d" always must be "1 How do I do that?
Try this: Range("BF409") = "= TODAY() - Day(TODAY()) + 1"
Take care of the quotes.
Dinesh Kumar Takyar Thank you