[SOLVED] Payroll

I've been working on a payroll workbook, and most everything is working the
way I want it to except the YTD totals. I want to update the YTD cell with
the push of a button (or keystrokes) after the user finishes with the rest
of the values. Help?

Ted

Presumably you have a spreadsheet in which you update specific data and then
get a YTD total at the end. I understand that you want to update the data
first and then calculate the YTD total afterwards.

The simplest way to do what you want is the following.

In the Excel sheet select <Tools><Options> and select the <Calculation> tab.
In the 'Calculation' part, set it to 'Manual' instead of 'Automatic'. This
means that you spreadsheet will not update calculations until you ask it to.

Once you have inputted your latest payroll data select <Tools><Options> and
go back into the <Calculations> tab and press the <Calc Now (F9)> button.
This will now 'force' excel to update your YTD total.

But, as you may have guessed, instead of going to <Tools><Options> etc. you
can simply press <F9> and this will force the update. I shows you the long
way just for completeness.

This is the easist way to what you are asking without using VBA etc. Please
note that if you turn off automatic calculations then I would make sure your
employees know so that there is no confusion over the numbers.

Hope this helps...

Regards



Alex


"Ted Dawson" wrote:

> I've been working on a payroll workbook, and most everything is working the
> way I want it to except the YTD totals. I want to update the YTD cell with
> the push of a button (or keystrokes) after the user finishes with the rest
> of the values. Help?
>
>
>

Is that setting global, across all the sheets in the workbook, or can it be
set for each sheet?

Ted

As far as I am aware, that is global i.e. applies to all sheets. Just tried
it and it seems that way.

There is another way around your problem but it involves VBA. Are you
familiar with VBA?

The solution to your problem would be quite simple and would be sheet
specific.

Is this of any interest?

Alex
"Ted Dawson" wrote:

> Is that setting global, across all the sheets in the workbook, or can it be
> set for each sheet?
>
>
>

Yes, please elaborate. We're not scared of VBA.



"Alex" <[email protected]> wrote in message
news:[email protected]...
> Ted
>
> As far as I am aware, that is global i.e. applies to all sheets. Just
> tried
> it and it seems that way.
>
> There is another way around your problem but it involves VBA. Are you
> familiar with VBA?
>
> The solution to your problem would be quite simple and would be sheet
> specific.
>
> Is this of any interest?
>
> Alex
> "Ted Dawson" wrote:
>
>> Is that setting global, across all the sheets in the workbook, or can it
>> be
>> set for each sheet?
>>
>>
>>

Ted

Ok. There are two parts to this. First is setting up the VBA code, the
second part is getting it to work on your sheet.

Part 1:

With your Excel workbook open and the appropriate sheet, select ALT + F11.
This will launch the Visual Basic Editor (VBE). In here now select
<Insert><Module>. This will insert a blank module into your VBE.

Now in the blank module type the following (or cut and paste from here):

Sub YTDcalc()
Dim YTDtotal As Double

YTDtotal = Range("A1") + Range("A2") + Range("A3")

Range("A4") = YTDtotal
End Sub

This is a VBA procedure that I have named YTDcalc. It is quite simple but I
will explain. In the procedure I have defined YTDtotal. This will be the sum
of a list of values i.e. A1, A2 and A3. This total will then be displayed on
the worksheet in cell A4.

Ted, you will need to do two things here.
(1) Replace A1, A2, and A3 with the cells that you would like to sum on your
spreadsheet e.g if you enter payroll figures in B1, F10 and Z100 then change
the above code to read:

YTDtotal = Range("B1") + Range("F10") + Range("Z100").

(You can have more than 3 range references, I just chose three for ease of
explanation)

(2) Change the cell reference for where you want the YTDtotal to be
displayed on your worksheet. For example if your YTD total appears in cell
C20 on your spreadsheet then replace the current code with:

Range("C20") = YTDtotal

Ok, that is part 1 done. You have set up the VBA code. (you can select
<save> at this point if you like. [note you can always get rid of this VBA
code from your workbook so don't worry about irreversible changes].

Part 2:

You can now close the VBE by simply clisking the X in the top-right hand
corner. You should now just be looking at your worksheet.

Now select <View><Toolbars><Forms>. The 'Forms' toolbar appears. Select the
'button' icon (by clicking it - note if you hover your cursor over the icons
you get a descriptive of what each one is) and then draw the shape of a
button on your worksheet to the size that you want it to be.

At this point a dialog box most likely appears with the YTDcalc procedure
displayed. Highlight YTDcalc and select <OK>. This assigns that VBA procedure
to the button so that each time you click the button that VBA procedure runs.

You are now almost done. You can close the 'Forms' toolbar and move the
button to whichever position you wnat on the worksheet by simply dragging it.

Finally, it may be best to give the button a name e.g. 'Calculate YTD
Total'. To do this, right-click the button and select <Edit Text> and then go
right ahead and edit the text (you may need to resize the button if the text
is too long). Once that is done, click anywhere on the worksheet and is all
is done.

Now you can test to see if it works. Enter some new values in your payroll
sheet and then click the button to get an answer if the cell that you have
designated.

By the way, if you want to get fancy then add this at the bottom of your VBA
module (between 'Range("A4") = YTDtotal' and 'End Sub'):

MsgBox "YTD total = " & Format(YTDtotal, "$0.00")

This will display a pop-up box telling you the YTD figure. (To do this
simply press ALT + F11 and go back into the VBA module and add the text).

Be aware, that when you come to next open this workbook you will get a
message asking if you would like to 'Enable Macros'. Please select <Enable
Macros> as this will allow your VBA procedure to work.

Ted, I hope this works for you. Please let me know if you have any
difficulties. For your information I am working in a European timezone so
possibly we are awake at different times. I will get back to you.

Regards


Alex




"Ted Dawson" wrote:

> Yes, please elaborate. We're not scared of VBA.
>
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> > Ted
> >
> > As far as I am aware, that is global i.e. applies to all sheets. Just
> > tried
> > it and it seems that way.
> >
> > There is another way around your problem but it involves VBA. Are you
> > familiar with VBA?
> >
> > The solution to your problem would be quite simple and would be sheet
> > specific.
> >
> > Is this of any interest?
> >
> > Alex
> > "Ted Dawson" wrote:
> >
> >> Is that setting global, across all the sheets in the workbook, or can it
> >> be
> >> set for each sheet?
> >>
> >>
> >>
>
>
>

Alex, works like a champ. Thank you for your help. I certainly appreciate
it.


Ted