Using Worksheet as a userform

deangla

New Member
Joined
Oct 1, 2006
Messages
6
Bear with me. I'm fairly new at this. I have two worksheets. The first sheet is based on a timecard. Even looks like a timecard. The second sheet is Pay which lists each employe and the fifteen or sixteen days of the pay period. I've made a couple of userforms and I'm wondering if I can use the Timecard sheet as a userform. If so, would I activate the Pay sheet with my VBA code in the Timecard sheet? Get me started, so I can beat my head on the wall for a bit, before I cry for help. :)

Thanks,

Dean
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Hi Dean

Welcome to the board!

If the timecard sheet looks a good replica as is, why would you wish to use a userform? It may be better to explain a little more what it is you wish to achieve, so that the boys and girls can better help you.

To partially answer your question, almost anything's possible with Excel / VBA, as long as you've got a clear goal.
If you really wish to incorporate a spreadsheet into your userforms, there's an "additional" control available to the userform, called "Microsoft Office Spreadsheet 9.0", but if you expand on your requirements in this thread, I'm betting that there will be a much better way to set you on your way. For example there's a great deal of code available to the spreadsheet, without ever going near the userform.

Good luck, and fingers crossed!!
 

deangla

New Member
Joined
Oct 1, 2006
Messages
6
Hi Sykes,

Thanks for responding. Sorry, I was not too clear in my query. I presently have a Timecard worksheet which lays out the days of the pay period. By entering the employee name and the in and out times each day from a dropdown list, we get the hours per shift worked by the employee, converted to decimal, with a total at the bottom for each pay rate that employee has. At the present time, we write the totals on the real timecard, clear the sheet and do the next employee.

Once all the timecards are done, we manually enter all the hours for each employee in another worksheet, named pay. That sheet is used for several tracking functions, ie. Hours per shift, day per category, etc., etc. What I'm trying to accomplish is-when we have completed calculating the hours for the employee, we can press a button and upload the information into the pay sheet. What I'm asking is can I use this timecard sheet, basically, as a userform.

I hope that helps a little. I'm old and tend to ramble a bit, something like hand quicker than brain. :confused:

Thanks,

Dean
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Hi Sykes,

Thanks for responding. Sorry, I was not too clear in my query. I presently have a Timecard worksheet which lays out the days of the pay period. By entering the employee name and the in and out times each day from a dropdown list, we get the hours per shift worked by the employee, converted to decimal, with a total at the bottom for each pay rate that employee has. At the present time, we write the totals on the real timecard, clear the sheet and do the next employee.

Once all the timecards are done, we manually enter all the hours for each employee in another worksheet, named pay. That sheet is used for several tracking functions, ie. Hours per shift, day per category, etc., etc. What I'm trying to accomplish is-when we have completed calculating the hours for the employee, we can press a button and upload the information into the pay sheet. What I'm asking is can I use this timecard sheet, basically, as a userform.

I hope that helps a little. I'm old and tend to ramble a bit, something like hand quicker than brain. :confused:

Thanks,

Dean

I am a little confused. Are all employees located on a single Time sheet or is it only one employee? To me it sounds like this is the case. Personally I would approach this differently in that I would use some method of having all the data saved on one summary type of sheet for all employees after they are entered by some method such as a userform or a worksheet.

I have one example I use where work requests are sent from a userform and stored on a network drive in the form of an appended text file. The text file is then imported into another Workbook. In your case the userform would probably be part of the 'my' target workbook. When I say userform I am not excluding the possibility of this actually being a sheet used for entry.

I am assuming that each employee has an employee id or some other unique identifier. Their data would be store on the master sheet. As you select the employee on the master sheet their data is transferred to the Timecard which then could be printed after data is updated on it. The pay sheet would look at this data for its information.

In my case the master sheet has vba code which records the row number selected. Initially I did this by placing an x in the first column and then using vloopup for finding the x, but now I use 'Target.Row'. You can store this value either as a global variable in vba or even on the worksheet itself. In that way the other sheets, the Timecard and Pay, now know what row is selected on the master sheet.

On your sheet 'Master' you would have a worksheet_selection change piece of code similar to this assuming you are storing the row number in cell k1:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wb As String
Dim z As Integer

On Error GoTo EndMacro

wb = ThisWorkbook.Name

z = Target.Row

Workbooks(wb).Worksheets("Master").Range("k1") = z

EndMacro:

End Sub

Now whenever you make a selection on the 'Master' sheet the row of the selection is stored in cell 'k1'.

What you do now is decide how you want the data on this row transferred to the Timecard.

This can be done as soon as the selection change is made by including in the selection change code something like this:

Code:
Dim EmployeeName As String

EmployeeName=Workbooks(wb).Worksheets("Master").Cells(z,2)

Workbooks(wb).Worksheets("Timecard").Range("B1") = EmployeeName

As soon as the selection change is made the Employee Name located in my example in column 2, on the row selected to the 'Timecard' sheet in cell 'B1'.

Now if you want to have the 'Pay' sheet reflect this just add a line and the Employee Name would be added to that sheet as well. No need to use lookup or any other search method, although you can for other reasons.

Note this is a two way street for me. On my second sheet I can make changes there and the changes are taken back to the 'Master' sheet, or I can make changes directly on the 'Master' sheet and they are transferred to the second sheet. This is done using the sheet code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'your code goes here

End Sub

Hope this gives you some ideas on how you may want to do this, Target.Row works here as well as does Target.Column, Target.Address, etc. Each sheet in Excel has these types of events where you can place code which is run whenever one of these events occur. You do want to avoid an event which causes another event to run ending up in a loop, such as a cell change which causes a Worksheet_Change event which in turn is seen as a Worksheet_Change event and a loop starts. To prevent this add at the start of the code:

Code:
Application.EnableEvents = False

'and then at the end of the code after the error check, EndMacro: in my 'case

Application.EnableEvents = True

To speed up the transfer of data you may want to do this at the start as well:

Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'after the code runs turn these back on with:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

This will often make things run much faster but this may depend on what you need to do with the data while it is running. You can turn these on or off on a line by line basis if need to make your code do what you want it to do (such as calculation).

Perry
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Dean
Perry's ideas are, no doubt, good and to re-visit your situation from another angle will be of benefit long term.
In the meantime, to try and give you what you asked for, I think we only need 2 bits of info:
1. The common denominator for the employee between Timecard and Pay.
2. Which Timecard cells need their data transferring, and to where, in Pay.

When we've got this info, I propose we attach some code to a button which uses "vlookup" to find the employee's common denominator in Pay (probably their name or employee number) who's data you've calculated in Timesheet, and "offset" to dump the data to the correct cells in Timesheet, and finally a line to clear the info from Timesheet, ready for the next input.

So -
By entering the employee name and the in and out times each day from a dropdown list
Does the employee name come from a dropdown as well?
What I'm trying to establish is, can we be sure that the name in Timesheet will always be exactly the same as when it appears in Pay? (otherwise the Vlookup will fail.)
Are the employees records in Pay, in rows?
In what range in Pay are the names held?
Assuming that the last two are answered in the affirmative, please say which cell data from Timesheet needs transferring to which column in Pay, eg:

Timesheet C3 needs to go to Pay col 4 (in the row pertinent to the employee name)
Timesheet E6 needs to go to Pay col 7

etc etc
 

deangla

New Member
Joined
Oct 1, 2006
Messages
6
Perry,

Thank you, something to look into.

Sykes,

I should have done this to begin with(shoulda, woulda, coulda). :rolleyes: Below are the two sheets I'm working with.

TimeCard

Employee Emp #
Linda 6 DATE TIME
IN 09/01/06 12:00 PM Fri
1.5 B OUT 09/01/06 1:30 PM
IN 09/01/06
0 OUT 09/01/06
IN 09/02/06 5:00 PM Sat
5 "" OUT 09/02/06 10:00 PM
IN 09/02/06
0 OUT 09/02/06
IN 09/03/06 3:30 PM Sun
7.25 A OUT 09/03/06 10:45 PM
IN 09/03/06
0 OUT 09/03/06


Payroll Sheet

C
O Fri Sat
D DATE 09/01/06 09/02/06 09/02/06
EMPLOYEE E HRS 95.25 77.25
<<NIGHT BAR >> RATE
3 Suzanne $12.00 7.00

50 Ronald T. $9.50 7.75

5 Leslie - Wait $7.00 5.25 4.25
6 Linda - Wait $7.00
6 Linda - Bar A $11.00
6 Linda - Office B $16.00 1.50


The common denominator on both sheets is the employee number. On the timecard sheet it is cell C2 and on the Payroll sheet it is A8:A100. The secondary is the pay rate code which can be one of 5 selections. On the timecard sheet it runs from B3:B65 and on the payroll sheet from C8:C100. What I want to do is compare emp# and pay rate code from the timesheet to the pay sheet and post the daily hours in the corresponding day. As I stated in my first post, I'm fairly new at this and I've purchased a couple of books. Unfortunately, they are not here yet and I'm off to the races, in the blind, so to speak. I've made a couple userforms, but I thought I might be able to do it from worksheet to worksheet. I'm just not sure where to start. :confused:

Thanks,

Dean
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Dean
There's good news, and bad news!

Good news :
We've now identified that we can use the employee number for the vlookup, to find the correct row in Pay to chuck the data into.

Bad news:
I can't make head nor tail of your data!
You can either use Colo's cool HTMLMaker utility v2.42 to display your spreadsheet on the board, available here:
http://www.puremis.net/excel/downloads.shtml

....or just give me a couple of "real" ranges that need transferring. e.g.

Timecard C3 needs to go to Pay col F in the cell pertinent to the employee's name, that way I can get you started, and you'll probably be able to do the rest yourself.........
 

KristenT

New Member
Joined
Aug 10, 2006
Messages
16
Ok, I have to admist that I didn't read all the answers to your query, just skimmed them, so now I have a question.....
Are the Timesheet and Pay sheets in the same workbook? If yes, since you are doing one employee at a time and then clearing the sheet, start by simply linking the approriate cells from Timesheet into pay using the =formula i.e. if you want the value from cell A3 on timesheet to appear in cell C36 on Pay then in Pay C36 type =Timesheet!A3
Do this to link the data. When you get that far then write back and we'll help you have it look up the correct pay rates for each employee based on thier employee number.
From what you've written you haven't done that part yet.... maybe I've missed something, but that seems like the simplest first step.
Kristen
 

deangla

New Member
Joined
Oct 1, 2006
Messages
6
Hi Kristen,

Thanks for the response. Yes, they are in the same workbook, but I don't want an active link input to the payroll sheet. I should have, and probably will, go ahead with a userform. It seems the easier alternative at this point.

Sykes! Thank you for your responses. I may repost in a couple of days. However, I find myself in the middle of a new pay period and the payroll must be done. One of my girls is out and it falls upon me to complete the task. People tend to get a bit cranky when they don't get their pay check.

Thanks again, :)

Dean
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

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