Excel and VBA code that could generate a Date& Time Stap and Serial Number

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guys

I hope every one is good and staying safe, i need a few help around the to generation numbers and a auto date and time.
Option 1. i need formula
I created a drop down in column D5 with list on Vessel name and i need once the vessel is click, column C5 should Auto give date &Time Stamp and Column B5 Should generation a serial Number starting from 0001

My Second Option is a VBA Code that can do this as well and is it position if am using a VBA on my Excel and upload in one Drive can this still work out for me to share with my colleague.

Appreciate with any help for both Option.

Shipping Voyage and Vessel Cargo Information Log.xlsx
BCDE
4Mus No'sDate &Time Supply Vessels Location/ Site Rig-move
5ADNOC-221Asseifiya Island
6Al Ghallan Island
Voyage_Log
Cells with Data Validation
CellAllowCriteria
D5:D10949List=Info!$B$3:$B$69
E5:E10949List=Info!$D$3:$D$74
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,191
Office Version
  1. 365
Platform
  1. Windows
Hello Lukma,

Perhaps this worksheet_change event may help:-
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
        On Error Resume Next
        If Target.Value <> "" Then
                Target.Offset(, -1) = Now
                Target.Offset(, -2) = 1
                Target.Offset(, -2) = Target.Offset(-1, -2) + 1
        End If
        
        With Range("A5", Range("A" & Rows.Count).End(xlUp))
                Target.Offset(, -2).NumberFormat = "000#"
        End With
        
Application.ScreenUpdating = True

End Sub

To implement this code:-
- Right click on the "Voyage_Log" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Each time that you select a vessel from the drop down, the code will immediately execute.

I hope that this helps,

Cheerio,
vcoolio.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,191
Office Version
  1. 365
Platform
  1. Windows
Sorry Lukma. I've just realised that I have the column order wrong so here is the amended code:-

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
        On Error Resume Next
        If Target.Value <> "" Then
                Target.Offset(, -1) = Now
                Target.Offset(, -2) = 1
                Target.Offset(, -2) = Target.Offset(-1, -2) + 1
        End If
       
        With Range("B5", Range("B" & Rows.Count).End(xlUp))
                Target.Offset(, -2).NumberFormat = "000#"
        End With
       
Application.ScreenUpdating = True

End Sub

You can upload the file to OneDrive and share the file but note that cloud stored files don't support macros so the file will need to be downloaded to a User's desktop.

Cheerio,
vcoolio.
 
Last edited:
Solution

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry Lukma. I've just realised that I have the column order wrong so here is the amended code:-

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
        On Error Resume Next
        If Target.Value <> "" Then
                Target.Offset(, -1) = Now
                Target.Offset(, -2) = 1
                Target.Offset(, -2) = Target.Offset(-1, -2) + 1
        End If
      
        With Range("B5", Range("B" & Rows.Count).End(xlUp))
                Target.Offset(, -2).NumberFormat = "000#"
        End With
      
Application.ScreenUpdating = True

End Sub

You can upload the file to OneDrive and share the file but note that cloud stored files don't support macros so the file will need to be downloaded to a User's desktop.

Cheerio,
vcoolio.
Dear Vcoolio

Thanks so much it works just as i wanted am so so grateful

Regards
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,191
Office Version
  1. 365
Platform
  1. Windows
You're welcome Lukma. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
118
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome Lukma. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.
Hi Vcoolio

Hope you been well i really have an slit few things to ask if possible

Is there a way to lock the cell B and C down because am having trouble with my colleagues
few of them sometime enter numbers and date which bothers me and every time i need to fix back from the share folder

So i decided to have the column B and C locked and leave out the rest column for them to be able to edit

i will Appreciate with a solution

Regards
 

Forum statistics

Threads
1,144,162
Messages
5,722,847
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top