Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Timesheet on desktop (2000)

    I maintain my own timesheet in Excel. My timesheet is a "homemade" product that displays on-screen. The part I print and submit every two weeks covers the range A1-G27. The range H4-K19 is the part no one sees except me. That's where I enter my arrival and departure times. I enter my arrival time of 7:00 AM in cell H9, for example; then my departure time at 3:00 PM in I9. The sheet then automatically computes the hours and displays that result in the appropriate cell in the part of the sheet that I print at the end of the pay period.

    My question: Is there a way to display just the non-printing part of this sheet on my desktop? I don't want it to take up the entire desktop--I just want a convenient way to post my arrival and departure times without having to launch Excel. I envision an item that appears on my desktop every time I sign in. Of course, what would be really slick would be a little pane that displays just the current date, with perhaps an action button that I can click to post the current time to the appropriate cell in my timesheet! Something tells me this could be a pretty ambitious undertaking, and maybe it's not even possible to do within the confines of the Excel application. Should I look to Access? Or perhaps there's a shareware or freeware app out there that does this. I'm not one to reinvent the wheel, y'know.

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Timesheet on desktop (2000)

    You might be able to do this if you publish the spreadsheet in HTML as part of a Digital Dashboard window in a front end for Outlook, but that is way out of my limited expertize.

    I would also expect many of the lightweight Google search for time tracking software commercial products have spreadsheet links, Timeslips comes to mind.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Timesheet on desktop (2000)

    Ouch, a single station Timeslips is $400 US! Sorry about that!
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    Far and away the best one I have used is TimeClock Plus. Not freeware, I think we paid about $100 for a single PC license if I remember correctly. Hugely capable program however.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    Checkout Stardock.com. They have an excellent skinning program that allows you to place interactive "objects" directly on your desktop. I know I've seen an Excel object that should do the trick. The objects can be downloaded for free from Wincustomize.com.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Timesheet on desktop (2000)

    Thanks one and all for the information!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    You might add some code to the workbook that positions your excel window.

    You should copy the code at the bottom of this message into the Thisworkbook class module:

    Workbook_Open (and BeforeClose) is an "event procedure" and needs to go in the module of the ThisWorkbook object.

    Alt+F11 to the VB editor
    In the project explorer window double-click the ThisWorkbook entry under Microsoft Excel Objects.
    In the code window which should now be showing you the module for ThisWorkbook:
    - select Workbook from the left hand dropdown at the top of the window
    - select Open from the right hand dropdown [may have happened automatically]
    - type (or copy) your code between the lines
    Private Sub Workbook_Open()
    and
    End Sub
    which will have been generated automatically.

    Make sure the module looks like what is copied below (but change the name of the worksheet in the gotot statement and play around with the numbers to get it the way you want).
    After entering this code, save the workbook, close and reopen it.


    <pre>Option Explicit

    Dim iTop As Integer
    Dim iHeight As Integer
    Dim iLeft As Integer
    Dim iWidth As Integer
    Dim lWindowstate As Long

    Private Sub Workbook_Open()
    With Application
    'Store original settings
    lWindowstate = .WindowState
    iTop = .Top
    iLeft = .Left
    iHeight = .Height
    iWidth = .Width
    'Now position window
    .WindowState = xlNormal
    .Top = 0
    .Left = 300
    .Height = 400
    .Width = 300
    'Go to your range (Change the worksheet's name!!!)
    .Goto ThisWorkbook.Worksheets("Timesheet").Range("H4"), True
    End With
    End Sub
    Private Sub Workbook_beforeclose(Cancel as Boolean)
    With Application
    'Restore Excel's window settings
    .Top = iTop
    .Left = iLeft
    .Height = iHeight
    .Width = iWidth
    .WindowState = lWindowstate
    End With
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Timesheet on desktop (2000)

    Sorry, but I did not get a chance to try this today, and I'm off till Monday. This looks very interesting, and I hope to try it out when I get back to work. Is this going to create a "floating" Excel page or what?

    Thank you very much!

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    <<Is this going to create a "floating" Excel page or what?>>

    Yes, it will position Excel to the righthand side of your screen when you open the file with the code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Timesheet on desktop (2000)

    Sorry, Jan, but I'm getting errors in my code, and I'm not skilled at understanding what's going on here.

    The error message says, "Ambiguous name detected." I went to the VB Editor's Help section, but the language has me baffled. I'll lay out some information about the workbook I'm using, and then I'll display the code exactly as I've entered it. Then would you be so kind as to look over the code and see where I've gone wrong.

    Full path to my workbook: cocuments and SettingsmyUserIDMy DocumentsMy Timesheets 2002Timesheets 2003.xls
    The current worksheet is number 41, labeled "030103-030116" (Jan. 3-16, 2003). The other 40 worksheets represent earlier two-week periods.
    The range I wish to display on-screen is H4-K19. That's the area where I enter my arrival and departure times. I presume since the code asks for a range that when this thing works I'll see just that range on display...?

    Supposing I get this working, how will I be able to view and print the timesheet that I actually submit? The print area is NOT part of the range I've specified in the code, but I don't turn in the part of the sheet that I'll display on-screen with this code.

    Now the code:

    Private Sub Workbook_Open()

    Option Explicit
    Dim iTop As Integer
    Dim iHeight As Integer
    Dim iLeft As Integer
    Dim iWidth As Integer
    Dim lWindowstate As Long
    Private Sub Workbook_Open()
    With Application
    'Store original settings
    lWindowstate = .WindowState
    iTop = .Top
    iLeft = .Left
    iHeight = .Height
    iWidth = .Width
    'Now position window
    .WindowState = xlNormal
    .Top = 0
    .Left = 300
    .Height = 400
    .Width = 300
    'Go to your range
    .Goto ThisWorkbook.Worksheets("Timesheets 2003") .Range("H4,K19"), True
    End With
    End Sub
    Private Sub Work_beforeclose(Cancel As Boolean)
    With Application
    'Restore Excel's window settings
    .Top = iTop
    .Left = iLeft
    .Height = iHeight
    .Width = iWidth
    .WindowState = lWindowstate
    End With
    End Sub

    Thanks again, Jan! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    Hi,

    These modules should not be placed in a normal sub, instead put them in the Thisworkbook sub below "Microsoft Excel Objects".

    Also this line:


    .Goto ThisWorkbook.Worksheets("Timesheets 2003") .Range("H4,K19"), True

    contians just one space too many and needs to look like this:

    .Goto ThisWorkbook.Worksheets("Timesheets 2003").Range("H4,K19"), True
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Timesheet on desktop (2000)

    Good morning, and thanks for the quick response! The extra space was an easy fix, but the rest is a bit tougher.

    < These modules should not be placed in a normal sub, instead put them in the Thisworkbook sub below "Microsoft Excel Objects". >

    Sorry, but I don't think I'm looking in the right place. In the VBE, where do I look for "Microsoft Excel Objects"? At this moment, in the VBE, I see on the left side of the screen a pane labeled "Project - VBA Project." Inside that pane is a list that resembles a file tree in Windows Explorer. The fifth item in the list is "VBA Project (Timesheets 2002.xls)." I expand the item and see all the worksheets in this Excel file, plus "Class 1" at the head of the list and "This Workbook" at the end. Sorry, but I'm still a beginner at VBA.

  13. #13
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    <P ID="edit" class=small>(Edited by carbonnb on 13-Jan-03 11:58. edited to reduce image size.)</P>Lucas,

    You were almost there. Once you had expanded the project, you should have seen:
    <pre>Microsoft Excel Objects
    +--Sheet1 (Sheet1)
    +--Sheet2 (Sheet2)
    +--Sheet3 (Sheet3)
    +--ThisWorkbook</pre>

    Double click on the ThisWorkbook. That should open a new code window.

    Paste the code in the new code window that opened.

    Have a look at the attached screenshot as well.
    Attached Images Attached Images
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Timesheet on desktop (2000)

    Brian: Thanks for setting me straight on that! Now I think I really am "almost there." There's still one more little snag:

    I get an error message--"subscript out of range." When I clicked the Debug button, I see the following line of my code highlighted:

    .Goto ThisWorkbook.Worksheets("Timesheets 2003").Range("H4,K19"), True

    The de###### has always seemed to me like a wise-ass teacher who says, "OK, Lucas--you've got a mistake in this paragraph. Now, you tell me what the problem is!" So the de###### has told me where the problem is--can you please help me identify the problem.

    Thank you!

  15. #15
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet on desktop (2000)

    Do you have a worksheet named 'Timesheets 2003', without the 's?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •