Timesheet Dashboard

Dear All,

I am very happy to announce I have created a “Timesheets” Dashboard application.

Unlike many versions you can find online are working perfectly, I have developed a new timesheet format. Timesheet 2012 Dashboard has a new and more user friendly way to fill out your timesheet.

Timesheet 2012 Dashboard is a dynamic spread-sheet and needs the macros enabled to be used. If you do not know how to enable the macros, please follow the instruction at the link http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx

The dashboard has two dynamic menus which allow you to actively interact with the spread-sheet. The first menu, called Active Control and shown below, gives you the option to select a specif month or all the month. This can be very helpful to avoid to scroll up and down your timesheet or create a PDF of your timesheet.

The second menu, called User Menu and shown below, has 6 buttons:

1. Update: in spite of your timesheet is automatically updated [not in this Demo version, you will get an error saying it cannot find the server instead] as soon as you open it, this button allows to perform a further manual update. Please note that the Demo version is for demostration only. There is a final version which can be used on an intranet to manage the all companies timesheets
2. Email Admin: this button opens your Outlook and allows sending an email to Admin, copying another address as well. You should email Admin if you need help with your timesheet
3. Create PDF: this features allows to create a PDF of the visible area in your timesheet [Selecting All on the Active Control you will PDF the 12 months while selecting only one month you will PDF only the selected month]. The PDF will be automatically saved on your PC with the format FileNameCurrentDate.pdf [Document folder or desktop]
4. Quick timesheet: this button is the most powerful tool on the new timesheet. This control allows to select a range and fill it out with the same number of hours. [further explanation below]
5. Save/Close: Save and close button. Please use this button to save your timesheet and resetup Excel with the normal view
6. Admin: only Admin is allow to use this tool. The spreadsheet automatically recognize who is logged in

The Quick Timesheet tool:

In spite of you still should fill out your timesheet in the “old fashioned” way, and which I strongly recommend to do, I have developed a tool to fill out the timesheet faster.

As already aforementioned, this tool allows you to fill out a range of cells with the same number of hours.

In the example below, the days from 2 to 6 and from 9 to 11 have been selected and the Quick Timesheet Tool will fill them out with 8 hours a day as soon as the OK button is clicked.

You can enter letter as well, i.e. H for Holiday and so forth

IMPORTANT:

On top of your timesheet there are some information about the staff. Title and Location have a scroll-down list [the list is dynamic and updatable if connected to the server].

The project area:

As shown below, you can select a project number through a scroll-down list [the list is dynamic and updatable if connected to the server]. Please note that you can select a maximum 15 different projects per month. Projects without hours or vice versa, are not allowed and you cannot save the timesheet if this happen.

To download the timesheet please click here: Timesheet Dashboard

Please save the Timesheet on your Computer before using it. Once you have saved the file, unzip it using 7z [http://www.7-zip.org/] and install it.

Following on from the above also note that LockXLS add-in will be to execute the file with Excel.

I hope you enjoy the spread-sheet.

Paolo

[Copyright © 2012, Paolo Succo]

Sum of cells depending on cell colour formatting

Hi All,

I recently came out with a problem using the sum function.
Imagine that you have a range of cells and you used a background color to identify certain specific cells and you wish to calculate the sum of only the cells with, for example, the red background in the range.
How can this problem be sorted out? Here the solution:

Function SumColor(DefClr As Range, DefRng As Range)

Dim MyCell As Range
Dim MyCol As Long
Dim SumColCell

'Identify the colored cell
MyCol = DefClr.Interior.ColorIndex

'Sum the colored cells in the range
For Each MyCell In DefRng
If MyCell.Interior.ColorIndex = MyCol Then
SumColCell = WorksheetFunction.SUM(MyCell, SumColCell)
End If
Next MyCell

SumColor = SumColCell

End Function


The VBA formula above as two parameters. With the first one you identify a colored cell in the range that you want to sum, the second is the range that you want to sum.

E.g.

Let's assume that this is what you have on column A:

A1. with red background

A2. with red background

A3. with yellow background

A4. cell with no background

A5. cell with no background

If you want to know the sum of only the red background cells, proceed as follow:

enter in A6 =SumColor(A1,A1:A5)

Enjoy

Comparing Two List

Hey Guys,

having problems in ffinding duplicates between two numerical list?
Here the solution:

Download the two lists comparing spreadsheet

The spreadsheet can be used for text lists as well but it might be possible you need to reformat the texts before comparing them.

Enjoy

2D & 3D Animation With Excel

Hi Guys,

please find enclosed an Excel File showing 2D and 3D animations with Excel.

Download the animation:
2D & 3D Animation with Excel

Enjoy

List Files In A Specif Folder

Hi Guys,

Today I will show you how to select a folder from your computer and list all the files in it.

The Macro will also give you information about the files like:

1 - Name
2 - Size
3 - Date and Time


Sub ListFilesInAFolder()

'######################################################################

'Author Paolo Succo
'Date 12 December 2011

'Macro to list files in a folder

'######################################################################

Dim directory As String
Dim r As Long
Dim f As String
Dim path As String


'Select folder
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a location for the file list"
.Show

Range("A:C").Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Bevel 1")).Select
Selection.ShapeRange.IncrementLeft 150#
Selection.ShapeRange.IncrementTop -0.75

If .SelectedItems.Count = 0 Then
MsgBox "Canceled"
Else
path = .SelectedItems(1)
path = directory


r = 1

'Headers
Cells(r, 1) = "Filename"
Cells(r, 2) = "Size"
Cells(r, 3) = "Date/Time"
Range("a1:c1").Font.Bold = True

'Get file
f = Dir(directory, vbReadOnly + vbHidden + vbSystem)
Do While f <> ""
r = r + 1
Cells(r, 1) = f
Cells(r, 2) = FileLen(directory & f)
Cells(r, 3) = FileDateTime(directory & f)

'get next file
f = Dir()

Loop

End If
End With

Cells.EntireColumn.AutoFit

End Sub

Download an example:

Files_List.xlsm

Can Excel Speak? Yes It Can!!!

Hi Guys,

Today I will show you how to create a function to make your Excel read texts.

The function is the following:

'######################################################################

'Author Paolo Succo
'Date 12 December 2011

'Function to make your spreadsheet red texts

'######################################################################

Function Talk(txt)

Application.Speech.Speak (txt)
Talk = txt

End Function

After creating a VBA module with this VBA code, you will beallow to use a new Excel formula called "Talk".
Write your text in any cell in your spreadsheet and make the formula read the text.

E.g.

Write the text in A1 and enter in B1 the formula =Talk(A1)

I hope you enjoy!

Proper Function VBA

Today I am going to present a simple macro to simulate the Excel proper function.
Please note that every formula in the spreadsheet will be converted to value.

Sub Capitalization()

'######################################################################

'Author Paolo Succo
'Date 09 December 2011

'Proper Function VBA
'Please note that all formulas will be converted as values

'######################################################################

Dim LastColumn As Integer
Dim LastRow As String
Dim ColumnLetter As String
Dim ColumnRange As String
Dim Target As Range

Dim ClickOption As String
Dim WarningMsg As String

WarningMsg = "Please be aware that all data will be converted as values and texts will be formatted as requested." & vbNewLine & "Would you like to proceed anyway?"

ClickOption = MsgBox(WarningMsg, vbQuestion + vbYesNo, "Warning!!!")
If Answer = vbYes Then

'Find Column Number
If WorksheetFunction.CountA(Cells) > 0 Then
LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If

'Find Column Letter
If LastColumn > 26 Then
ColumnLetter = Chr(Int((LastColumn - 1) / 26) + 64) & Chr(((LastColumn - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(LastColumn + 64)
End If

'Find Last Row
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

ColumnRange = ColumnLetter + LastRow
Range("A1:" & ColumnRange).Select

For Each Target In Selection
Target.Value = Application.WorksheetFunction.Proper(Target.Value)
Next

Else

End If

End Sub