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,280
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,280
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,280
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,280
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,280
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,082,384
Messages
5,365,144
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top