Results 1 to 10 of 10

Thread: Timesheets and Userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Timesheets and Userform

    Hi All,

    I built our weekly/daily time sheets some years ago in Excel using mostly formulas to do the calculations. This worked great, but since I started to learn more about vba and userforms I am busy streamlining it to use userform inputs and vba to send the information to the relevant sheets.

    I have a summary sheet (shSummary) and then daily sheets (shMon; shTue; shWed; etc) On the summary sheet there is a date (WkStartDate) for the begining of the week and I calculate the end of the week with a basic formula.

    My userform is for the daily sheets. It have textboxes for the user inputs which is then sent to the sheet with vba. In the userform I have a Label (lDate) that I want to populate the day's date and (lDay) the day of the week.

    When I call the userform, I was thinking of using the Select Case statement to calculate the date according to the active sheet. For example
    if I'm in shMon then lDate = WkStartDate
    if I'm in shTue then lDate = WkStartDate + 1
    etc.
    I have absolutely no idea how to work with the select case statement and you will see that in my "BallsUp" of code.

    Code:
    Private Sub UserForm_Initialize()
    
    'PURPOSE: Initialize the Resource UserForm
    'Show the date and day at the top of the UserForm
        
        
    Dim sh As Worksheet
    Dim shAct As Worksheet
    Dim d As Date
    
    
    Set sh = shSummary
    Set shAct = ActiveSheet
    d = CDate(sh.Range("Y7").Value)
    
    
      Select Case d
        Case shAct = shMon
          Me.LDate.Value = Format(CStr(d + 1), "dd MMMM YYYY")
        Case shAct = shTue
    '      d 1
    '    Case shAct = shWed
    '      d 2
    '    Case shAct = shThu
    '      d 3
    '    Case shAct = shFri
    '      d 4
    '    Case shAct = shSat
    '      d 5
    '    Case shAct = shsum
    '      d 6
      End Select
    End Sub
    any and all suggestions will be highly appreciated.

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Timesheets and Userform

    Hi,

    Untested but try following & see if does what you want

    Code:
    Private Sub UserForm_Initialize()
        
    'PURPOSE: Initialize the Resource UserForm
    'Show the date and day at the top of the UserForm
        
        
        Dim sh As Worksheet, shAct As Worksheet
        Dim m As Variant
        Dim d As Date
    
    
        Set sh = Worksheets("shSummary")
        Set shAct = ActiveSheet
        d = sh.Range("Y7").Value
        
    'display date for selected daily sheet
    'if activesheet not a daily sheet show week start date
        m = Application.Match(shAct.Name, Array("shMon", "shTue", "shWed", "shThur", "shFri", "shSat", "shSun"), 0)
        Me.lDate.Caption = CStr(Format(IIf(Not IsError(m), d + CLng(m) - 1, d), "dd MMMM YYYY"))
    End Sub
    Note: UserForm label does not have Value property - Caption is the property used to populate required text.

    Dave

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Timesheets and Userform

    Hi Dave,

    Thank you for your help. This code is giving me a Run-time error '9: Subscript out of range.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Timesheets and Userform

    Quote Originally Posted by kakiebos View Post
    Hi Dave,

    Thank you for your help. This code is giving me a Run-time error '9: Subscript out of range.

    Does it error on this line

    Code:
    Set sh = Worksheets("shSummary")
    If so, the worksheet tab name shSummary you stated you have, does not exist.

    Check the name in your summary tab


    Dave

  5. #5
    Board Regular
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Timesheets and Userform

    shSummary is the Property name of the sheet. It is not the name of the sheet tab. The tab name is only Summary.
    Does this perhaps cause the error? I generally prefer to use the sheet's property name, as some users will sometimes change the tab names, and that causes havoc to code in my little experience.
    The same with shMon, tab name Monday; shTue, tab name Tuesday

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Timesheets and Userform

    Quote Originally Posted by kakiebos View Post
    shSummary is the Property name of the sheet. It is not the name of the sheet tab. The tab name is only Summary.
    Does this perhaps cause the error? I generally prefer to use the sheet's property name, as some users will sometimes change the tab names, and that causes havoc to code in my little experience.
    The same with shMon, tab name Monday; shTue, tab name Tuesday
    Its always helpful to share information like this when requesting assistance.

    Try this change to the code

    Code:
    Private Sub UserForm_Initialize()
        
    'PURPOSE: Initialize the Resource UserForm
    'Show the date and day at the top of the UserForm
        
        
        Dim sh As Worksheet
        Dim shAct As String
        Dim m As Variant
        Dim d As Date
    
    
        Set sh = shSummary
        shAct = ActiveSheet.CodeName
        d = sh.Range("Y7").Value
        
    'display date for selected daily sheet
    'if activesheet not a daily sheet show week start date
        m = Application.Match(shAct, Array("shMon", "shTue", "shWed", "shThur", "shFri", "shSat", "shSun"), 0)
        Me.lDate.Caption = CStr(Format(IIf(Not IsError(m), d + CLng(m) - 1, d), "dd MMMM YYYY"))
    End Sub
    Dave

  7. #7
    Board Regular
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Timesheets and Userform

    Quote Originally Posted by dmt32 View Post
    Its always helpful to share information like this when requesting assistance.

    Try this change to the code

    Code:
    Private Sub UserForm_Initialize()
        
    'PURPOSE: Initialize the Resource UserForm
    'Show the date and day at the top of the UserForm
        
        
        Dim sh As Worksheet
        Dim shAct As String
        Dim m As Variant
        Dim d As Date
    
    
        Set sh = shSummary
        shAct = ActiveSheet.CodeName
        d = sh.Range("Y7").Value
        
    'display date for selected daily sheet
    'if activesheet not a daily sheet show week start date
        m = Application.Match(shAct, Array("shMon", "shTue", "shWed", "shThur", "shFri", "shSat", "shSun"), 0)
        Me.lDate.Caption = CStr(Format(IIf(Not IsError(m), d + CLng(m) - 1, d), "dd MMMM YYYY"))
    End Sub
    Dave
    Hi. Sorry for not sharring that information. Will remember to do that in future.

    Thanks again for your assistance with my project. Like I said, when I changed the code to the sheet names, it worked like a charm. I now tried your new code for the sheet code names, and it is giving me the following error: Run-time error '91': Object variable or With block variable not set.

    I'm not sure, as it is not directing me to any specific line of code, but with all my testing and troubleshooting it appears to be this line.
    Code:
     shAct = ActiveSheet.CodeName

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Timesheets and Userform

    did you copy all my code as published or have you just amended your existing code?

    Dave

  9. #9
    Board Regular
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Timesheets and Userform

    Quote Originally Posted by dmt32 View Post
    did you copy all my code as published or have you just amended your existing code?

    Dave
    Hi Dave,

    I only copied some of the lines in your code and amended mine. It looked the same, but I must have missed something. I now copied your complete code and block commented mine. Your code is working perfectly.

    Thank you so much for your patience and assistance.

    May you have a blessed day.

    Regards

    Peet.

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Timesheets and Userform

    Quote Originally Posted by kakiebos View Post
    Hi Dave,

    I only copied some of the lines in your code and amended mine. It looked the same, but I must have missed something. I now copied your complete code and block commented mine. Your code is working perfectly.

    Thank you so much for your patience and assistance.

    May you have a blessed day.

    Regards

    Peet.
    You are not the first to do this but should always use any code updates complete & unaltered in first instance.

    Glad your issue now resolved

    Dave

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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