Timesheets and Userform

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,354
Office Version
2013
Platform
Windows
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
 

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
Hi Dave,

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,354
Office Version
2013
Platform
Windows
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
 

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,354
Office Version
2013
Platform
Windows
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
 

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,354
Office Version
2013
Platform
Windows
did you copy all my code as published or have you just amended your existing code?

Dave
 

kakiebos

Board Regular
Joined
Jun 10, 2018
Messages
59
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,354
Office Version
2013
Platform
Windows
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
 

Forum statistics

Threads
1,085,703
Messages
5,385,308
Members
401,938
Latest member
JustinTaster

Some videos you may like

This Week's Hot Topics

Top