VBA Userform for inputting hours worked

    Hello,

    I have an Excel sheet I want to use, where someone can select a Project (Column A = Project1, Project2 ..) a date (Row 1), and a rate of pay (Row 2 = x1, x1.5 and x2)


    then enter an amount of hours which will populate the correct cell. There will end of being 000's of projects and span an entire year, hence the reason for an input box. I have tried to use the code enclosed however end up with a few problems.

    1. The lists end up with gaps in between them and
    2. I can not make the code take the input form data and place it in the correct cell.

    I have tried searching google and YouTube but having no luck and my "coding" ability is basic at best


    Thank you for any help


    24/02/202025/02/2020
    x1.0x1.5x2.0x1.0x1.5x2.0
    Project A
    Project B
    Project C

    Hello,


    Please excuse my ignorance, but is a flat file essentially the way the spreadsheet is laid out? Unfortunately I have inherited a template that they would prefer not to change. However if this makes it a lot easier, open to any suggestion.


    Thank you again

    Re,


    The so-called ' template ' you have received is a poisoned gift ...:evil:


    The number of your future headaches is practically unlimited ... :cursing:


    P.S. If you feel like becoming a billionaire ...

    and if you can charge your management with the purchase price of all the aspirin tubes ...

    Then you could keep ... what they dare to call a ' template ' ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited 2 times, last by Carim ().

    Love 1

    Ah ok, get the feeling I need to change this. Regarding using a User Form for my problem, do you know if this is possible if I changed to a flat file format?

    It could be done, but I think manual entry with that time sheet layout would be easier than a userform

    Quote from iamlost

    Ah ok, get the feeling I need to change this. Regarding using a User Form for my problem, do you know if this is possible if I changed to a flat file format?

    In my humble opinion ... it is compulsory ;)


    See below description of Flat File :

    https://www.ozgrid.com/Excel/E…eadsheetDesign.htm#Layout


    Now ... the most important aspect is your working process : how data is going to be stored ...


    Could you describe in plain English ... who will be what with what frequency ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Love 1

    I did a similar userform last week or the week before, admitted the entry sheet was not such a problem. I won't have time to do this until tomorrow

    Thank you both.

    Carim - Effectively staff will complete paperwork and send to an office which is then input by office staff on a weekly basis. The original template I have covers around 40 different projects and spans an entire year. The intention is to store the data in the spreadsheet and when a new year starts save the last years and create a new template.


    Roy - that would be great thank you. I will try and have a look at your website later on tonight.

    Hello Carim, thanks for the sheet.


    If possible I was looking for a user form due to the number of projects and dates there will be as well as to avoid user input error (the task is going from one team to another) therefore trying to make it "fool proof"

    Genuinely appreciate all of the help but thinking a userform might be more complicated than I originally thought.

    Hello,


    In order to build a "fool proof" UserForm ... you might need to list all your 40 Projects in Labels ...;)


    and probably insert a Calendar ... Date and Time Picker ...:/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Love 1

    I didn't even think about a data and time picker :thumbup:, I had "imagined" just typing in a date but you are right not entirely "fool proof" :/


    Didn't understand listing all projects in labels though?

    Sorry, I missed your replies.


    1. DatePickers can be problematic, so avoid them.

    2. I think your projects are in a Combobox.


    I would like to change the format of the sheet though.

    I have tidied up your code by making the ComboBoxes load better.


    Check this out, I think it is doing what you need.


    I have kept your sheet layout

    Quote from royUK

    I have tidied up your code by making the ComboBoxes load better.


    Check this out, I think it is doing what you need.


    I have kept your sheet layout

    Roy, You are a hero sir, cant thank you enough (and also Carim) can only hope I can work to having your level of knowledge someday. Thank you again

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login