Timesheets and Userform

kakiebos

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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
Hi Dave,

Thank you for your help. This code is giving me a Run-time error '9: Subscript out of range.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
did you copy all my code as published or have you just amended your existing code?

Dave
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top