Timesheets and Userform

[kakiebos]

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.

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.

 

[dmt32]

Hi,

Untested but try following & see if does what you want

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

 

[kakiebos]

Hi Dave,

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

 

[dmt32]

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

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

 

[kakiebos]

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

 

[dmt32]

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

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

 

[kakiebos]

Its always helpful to share information like this when requesting assistance.

Try this change to the 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.

 shAct = ActiveSheet.CodeName

 

[dmt32]

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

Dave

 

[kakiebos]

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.

 

[dmt32]

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