+ Reply to Thread
Results 1 to 15 of 15

VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

  1. #1
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Talking VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    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!
    Last edited by arthurgases; 02-12-2022 at 06:48 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Arthur,

    Attached covers the "Good" button.
    When you click it, it adds one to the Total score in F12, finds which row "spans" the time of the click and enters "P" in Col C for that row.
    Then checks if there are any "P"s in Col C, and if there are it adds one to the Good total in F14, and clears Col C ready for the next click.
    If you want a "permanent" record of when the button was pressed, you need to change the match to "1" and not "P", change ".Cells(m, 3) = "P" to ".Cells(m, 3) =.Cells(m, 3)+1" , change "iVal=1" to "iVal>=1", and remove the last row clearing the contents of Col C.


    Please Login or Register  to view this content.
    For the "Fail" clicks, duplicate the Macro, change the "P" to "F" and set it to enter the addition into F16. Add a button and link it to the Fail Macro,

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 02-12-2022 at 03:13 PM.

  3. #3
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hi arthurgases, welcome to ExcelForum.

    My submission would look like this. Note that the code doesn't do anything if one or more entries in the table contains something else than a time.
    Please Login or Register  to view this content.
    Last edited by GWteB; 02-12-2022 at 04:42 PM.

  4. #4
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Talking Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Ochimus,

    At first, I would like to thank you for your time checking and replying on my inquiry.
    I tried to download the excel file you shared and try to run the program.
    However, every time I click the GOOD button, the only outcome is that it only adds up on the cell F12. There is no record on the column C on the time the clicked was performed. I also tried to do your suggestion,
    "" If you want a "permanent" record of when the button was pressed, you need to change the match to "1" and not "P", change ".Cells(m, 3) = "P" to ".Cells(m, 3) =.Cells(m, 3)+1" , change "iVal=1" to "iVal>=1", and remove the last row clearing the contents of Col C.""

    Sample.PNG

    but still it does not work or Maybe I missed something about on your suggestion. Can you confirm it?

    Anyhow, I also attached my excel file for your review.
    I hope you can check and help me on this.

    The only thing I want to happen on my program is that to:

    1. Record the click count based on the TIME NOW that falls between 2 times (range A & B, respectively).

    Looking forward for your reply regarding above inquiry.

    Thank you in advance!
    Attached Files Attached Files
    Last edited by arthurgases; 02-13-2022 at 08:53 PM.

  5. #5
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Talking Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hello GWteB,

    Thank you for time reviewing my inquiry.

    I have tried to copy your code and run the program but it does not work on my end.
    I would greatly appreciate if you could attach your sample excel file here so that I can fully grasp your code.

    I also attached my excel file for you to see how I want to run my program.
    The only thing that is lacking on my code is to record the click count on column "C" & "D" based on the TIME NOW by which the click count must fall on the time range on column A2:B27(referring to TIME NOW on the PC or Laptop)

    Example: If TIME NOW is 9:26am,all the click count (GOOD & NG)must fall between row 6 9:30 am to 10:00 am ONLY.. same goes to other time range condition A2:B27.

    SAMPLE 1.PNG

    However, on my end all click count only record of C2 & D2 respective and I cannot figured out how should I do the program .

    I hope you can revert and help me on my inquiry.

    Thank you in advance!
    Attached Files Attached Files
    Last edited by arthurgases; 02-13-2022 at 09:59 PM.

  6. #6
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    I see, well, I didn't use a UserForm, coded just the basics, assuming you were able to implement.
    Attached my excercise based on your screenshot. Will download yours and see what you exactly mean.

    EDIT:
    @arthurgases,
    Your workbook doesn't differ from the screenshot.
    I await your review of my upload, let me know whether I understood your explanation correctly.
    Attached Files Attached Files
    Last edited by GWteB; 02-14-2022 at 03:17 PM.
    Did this help? Say thanks by clicking the ★

  7. #7
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hello GWteB,

    Thank you very much for sharing your sample attached file.
    I do now understand your code and it is exactly how I want my program works.

    I already tried to copy your code and incorporate it on my program and it really works.
    Your vba programming is superb.

    Looking forward for your more vba programming support/guidance from you.

    Again, thank you very much.
    Good Speed!

  8. #8
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    You are welcome and thanks for letting me know. Good luck with your project

  9. #9
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Smile Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hello GWteB,

    Good day!
    How are you.
    I hope you're doing well.

    Sorry to bother you again with same project.
    I tried to add another excel sheet on my program while utilizing 1 userform with command button (Good & Not Good) for Data Recording.
    However, the userform does not work on both sheets. Meaning it only works on 1 sheet(either sheet1 or sheet2 only).
    Is there any way I can utilize 1 userform for multiple worksheet? I am planning to have 5sheets in 1 excel using 1 userform only.
    I hope you can support me on this for the 2nd time.

    Thank you in advance!

    Note: Attached herewith is my excel file for your reference and checking of code.
    I only add the sheet2 on the module but still it does not work.
    Code.JPG
    Attached Files Attached Files

  10. #10
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hi arthurgases,

    If I understand you correctly, you want to perform the same action on multiple worksheets simultaneously. Due to the structure of my previously provided code, only minor adjustments are needed to get this done.
    1. The reference to the desired worksheet range (the time table) must take place outside the RegisterClick procedure;
    2. The RegisterClick procedure must be declared in such a way, that it expects a range argument in addition to the product quantity argument.
    3. The click event handler of each of the command buttons needs to invoke the RegisterClick procedure as many times as number of sheets to be affected.
    These changes would look like the code below.

    This goes in a standard module:
    Please Login or Register  to view this content.

    This goes in the code-behind module of the userform:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Hi GWteB,

    Thank you for your time checking on my inquiry.
    I have downloaded and check the attached file that you have shared and tried to run the program.
    However, the userform only works only on sheet2.
    When I try to shift from sheet2 to sheet1, the click count was being recorded in sheet2 also and no recorded click count on sheet1.

    Can I ask for your help again to check what's lacking on the initial code that we have?
    Hope to receive your reply soon.

    Thank you in advance.

  12. #12
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    No idea what's going on at your side, as it works for me on both sheets simultaneously.

    https://www.dropbox.com/s/nv2225btxk...gases.gif?dl=0

  13. #13
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Got to see your screen video and follow the sequence of your opening of the userform, it works also on my end.
    I don't know also what's the problem on my end because when I try to run the program using sheet1, the click count really register on sheet2, but no click count data on sheet1.
    Anyways, following your sequence of running the program already works.
    Thank you for your kind support and sharing that screen video.

    However, what I want to happened is that the click count of sheet1 or sheet2 should stand alone while using 1 userform only. Is it possible?
    Because currently, the click count for both worksheets is somewhat like sync to one another, and click count adds up on both sheets.

    Click count registered on sheet1 must not be reflected on sheet2(vice versa). Is it possible?
    Reason is that sheet1 & sheet2 is different process. With that, click count on both worksheet differ from each other.

    Hoping to received your feedback.

    Thank you in advance.

  14. #14
    Forum Contributor GWteB's Avatar
    Join Date
    12-13-2021
    Location
    GMT +1
    MS-Off Ver
    2013
    Posts
    136

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Quote Originally Posted by arthurgases View Post
    ... what I want to happened is that the click count of sheet1 or sheet2 should stand alone while using 1 userform only. Is it possible?
    Actually that seemed obvious, but I wasn't sure. So now a distinction has to be made when sheet1 or sheet2 is used, I think the use of a second set of buttons is the obvious choice.

    I would suggest that you modify the UserForm yourself and add two more command buttons.Rename them both in such a way that the new name makes clear what the function of the button is. It might be wise to also rename the existing buttons.
    Now study the code I've provided in my post #10 and try to determine which part of each click event handler of the existing buttons needs to be moved to the click event handler of the newly created buttons.
    If you come to the conclusion that the code should look something like the one below, then you're on the right track.

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    01-22-2022
    Location
    PHILIPPINES
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code that input data on specific cell if TIME NOW falls b/w 2 times range column

    Thanks for the advise.
    I think also that's the best option that I can do.

    I'll modify my userform or even add another one, just to separate the additional 2 buttons to eliminate confusion on the user.
    Once done, i'll share to you the result.

    Again, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-21-2021, 06:47 PM
  2. How to check if a 2 specific time falls in between 2 times (Shifts) in Excel?
    By athishvikram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2020, 09:28 AM
  3. How to check if a 2 specific time falls in between 2 times (Shifts) in Excel?
    By athishvikram in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-26-2020, 09:28 AM
  4. Replies: 17
    Last Post: 07-19-2018, 07:42 AM
  5. Count if specific time falls within time range
    By JPED41 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2018, 07:31 PM
  6. trying to use a range of times and then input specific text
    By robaston1971 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-04-2016, 10:12 AM
  7. Selecting specific cell if score falls within specific range
    By Rennier in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2008, 02:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1