Hello VBA Excel Experts,
I am currently doing an Excel VBA program that record click counts for the quantity of Good & Defective parts produced in our process.
For the click count recording, I have utilize userform and command button to automatically store the click count data qty on excel cell.
My problem is how to make a VBA code that record click count on a specific range if TIME NOW() falls between 2 time range column.
Example:
IF Time Now() is 7:15am, it falls between 7:00 am - 7:30 am, then when I click the command button, click count must appear on range C2 or D2 (depends if GOOD or NG).
Then when Time Now () is 7:31am, it falls between 7:31 am to 8:00 am, then click count must record on range c3 or D3.So on and so forth. Time range has 30 minutes interval.
Sample.PNG
My VBA Code is :
---------------------------------------------------------------------------------------------------------
Private Sub cmd_Good_Click()
Dim x As Long
With Range("C2")
If IsBlank = Range("C2") Then
x = 0
Else
x = Val(Split(Range("C2").Value)(0))
End If
.Value = x + 1
End With
End Sub
-------------------------------------------------------------------------------------------------
Private Sub cmd_NG_Click()
Dim x As Long
With Range("D2")
If IsBlank = Range("D2") Then
x = 0
Else
x = Val(Split(Range("D2").Value)(0))
End If
.Value = x + 1
End With
End Sub
-----------------------------------------------------------------------------------------------------------------
I know based from my code, the click count will only record on range C2 & D2 continuously without time consideration. I just can't figured it out how do I need to make the code with consideration of the Time Now () and the 2 time range so that it will work as I have explain in the above example scenario.
I hope someone can help me regarding my inquiry.
Thank you in advance!
Bookmarks