Using a variable search name to minamize code is it possible?

[veedubnut115]

So I am a newbie when it comes to VBA. I am also self taught with both VBA and Excel so please bear with me. With all that said here is what I am doing with it and what I am trying to accomplish.

I created a time clock for my guys to clock in and out at. It has a drop down menu which the guy selects there name. I use that name/Cell to place a date/time stamp in another cell. This is done via a button click and time and hours are calculated.

What I want to be able to do as the list gets larger is, based off the drop down menu selection is have excel go down a selection of names and pick the correct cell or name and place there clock in or clock out time. Right now I have each name its own set of if statement ( see below, I only gave a few examples but I have 12 guys) I am looking out to the future for when I keep adding guys. I am thinking there has to be an easier way. I have to be able to not keep writing the same lines of code over and over again. Thoughts.

Hope this makes sense.

As you see in the example below the only real thing that changes is The value in code I put some ****** in front of it...The Range of Worksheet(2). A Range in worksheet(3) 4th line of code, and then where I place my date stamp, Worksheet(2) 5th line of code. In the excel file I have the persons name on the same rows so I was wondering if there was a to look for and match or something. As I said I am new to excel and coding so I don't know all the extra features. like lookup tables and such. I know lookup everything needs to be in order so it wont work in this instance. I was just hoping to avoid adding to my macro code every time I add employees I want to hand off this time clock and be done with it. My punch out is the same way, except I was going to add one more that is was going to look at there total hour worked in another worksheet, and output that data. So based off there name it would then go to a list, find there name and find the total number of hours they worked so far and output that as well as telling them that they punch out for the day. But its coded the same way.

So my question is how can I reduce my programming steps my making Worksheets(1).Range"(F3)=Worksheets(2).Range("A4") a variable based off the name select in F3
Same with Worksheets(3).Range("C3") A variable but based off the name selected in F3
And How

Thanks.

Please Login or Register  to view this content.

______________________________________________________

[ranman256]

your Names should be in a worksheet (and passwords) hidden.
the form loads all names into the combo box at start.
There should be NONE in code.

When the user gets the correct name/pass, then it should load that persons last entry...
if it has a last complete OUT then you ask for IN
if it has an IN but no OUT, then you ask for OUT.

bob, OUT, 6/1/15 9:00AM
bob, IN, 6/1/15 10 am

[veedubnut115]

ranman,
That is an interesting option because that could prevent my guys from forgetting to punch out or punch in.

Their names are in worksheets, not in my code. Passwords, are on a separate worksheet that is veryhidden and locked out.

Not sure what you mean by a form and a combo box. I don't have the excel file with me at the moment or I would look at uploading it.

What I basically have is one worksheet that has a dropdown that is a list of name below that is two buttons, punch in and punch out. Those buttons run my macro code to punch in or out.

The time punches go on worksheets 2 which is rows of peoples names. I have daily punches, In/Out time, Daily totals, and then Weekly totals all this on this sheet

There is other stuff on other sheets but that is basically what is going on and what I am needing help with at this time.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[veedubnut115]

Zbor,
Very sorry hope the title is better now.