×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Defult values for weeks in Listbox
2

Defult values for weeks in Listbox

Defult values for weeks in Listbox

(OP)
Hi There

I am developing a KPI Dashboard for work.

I am trying to make it versatile so that individual users can customise it to suit their own requirements.

I have added a multiselect listbox with our weeknumbers 1-52. I want the default values to be the current week of the year plus the previous 11 weeks i.e 12 weeks in all.

If the current week is within the first 12 weeks of the year, then the listbox should default to weeks 1-12. Once we get into week 13, the listbox should default to weeks 2-13. Obviously I could just write one big bit of vba i.e If currentweek >0 and < 13, then show 01-12, else if currentweek=13 then show weeks 2-13. This seems awful cumbersome so I was wondering if there was a better way

RE: Defult values for weeks in Listbox

Here are some VBA logics for Week Numbers based on current date. This looks promising to start with:

CODE

Option Explicit

Private Sub Command1_Click()
Dim intWeekNo As Integer
Dim intW As Integer

intWeekNo = WeekNumberAbsolute(Date)

With ListBox1
    .Clear
    Select Case intWeekNo
        Case Is < 13
            For intW = 1 To 12
                .AddItem intW
            Next intW
        Case Is > 39
            For intW = 40 To 52
                .AddItem intW
            Next intW
        Case Else
            For intW = intWeekNo - 12 To intWeekNo
                .AddItem intW
            Next intW
    End Select
End With

End Sub

Public Function WeekNumberAbsolute(DT As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function 


---- Andy

There is a great need for a sarcasm font.

RE: Defult values for weeks in Listbox

@Andy, do you really need Case > 39?

The Case Else works for every week >= 13, Current Week plus the previous 11 weeks. Two Cases total.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

Then Elsie, have a question for you. Do you want weeks 1-12 for any week less than 12, when that week’s data hasn’t arrived yet.

Or would you get..
Week.  Selections
1.     1
2.     1-2
...
11.    1-11
12.    1-12
13.    2-13
...
40.    29-40
41.    30-41
...
52.    41-52
 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
I would be happy to just have the weeks with the data. However when I try to do charts with fewer weeks, the bars become thick rather than staying at a consistent size irrespective of how many weeks. Is there a way around that?

RE: Defult values for weeks in Listbox

I’d modify Andy’s code thusly...

CODE

‘
intWeekNo = WeekNumberAbsolute(Date)

With ListBox1
    .Clear
    If intWeekNo - 11 < 1 Then
       intWeek = 1
    Else
       intWeek = intWeekNo - 11
    End If

    For intW = intWeek To intWeekNo
        .AddItem intW
    Next intW
End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
How do I keep the width of the bars consistent even if I have fewer weeks

RE: Defult values for weeks in Listbox

Are you referring to a Bar Chart, like a Gantt Chart?

What table/values do you have for charts?

Plz give examples of less than 12 weeks and 12 weeks

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

Can you post a workbook example?

Why wouldn’t you have 12 weeks for weeks 1-11 using data/weeks for the previous year? Should tha data not be KPI relevant? Why would that data be ignored? It’s ongoing relevant info, it seems to me.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
Yes, I am using barcharts.

The values are all to do with closure on time/late of actions arising from various different activities. I have got the values in a single sheet that I am using to populate my dashboard. Whilst I want the default to be 12 weeks, I would like the user to have the option of a different time period such as 4 weeks, 8 weeks etc. I can do that at the moment but the bars become very wide. I would prefer them to stay the same thickness regardless of the time period selected

RE: Defult values for weeks in Listbox

Plz see the previous post. An example workbook with chart would be helpful.

Shooting from the hip, if 12 weeks will be the max, then weeks less than 12 might be set to ZERO (no data).

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
It’s likely thatgoing forward we will always have 12 weeks but we made the decision that as it’s a new system, we would start afresh from 1st January

RE: Defult values for weeks in Listbox

(OP)
I will attach a file in the morning when I get to work

RE: Defult values for weeks in Listbox

Thx.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
Hi

I have attached a sample version of the file

If you tick the checkboxes for weeks 1-12 then click update dashboard then repeat for just weeks 1-4, you will see what I mean when I say that the bars change size/thickness. Ideally, they would stay the same thickness regardless of how many weeks are selected

RE: Defult values for weeks in Listbox

Quote (Skip)

@Andy, do you really need Case > 39?

Yes, you are right.
I don't know what I was thinking,... ponder

---- Andy

There is a great need for a sarcasm font.

RE: Defult values for weeks in Listbox

IMNSHO using arbitrary start and end periods to measure effort (like month end, quarter end, year end) encourages behaviors that result in HOCKEY STICK efforts rather than ongoing steady efforts. That’s just my 2 pence worth.

I’ve uploaded your workbook having modified the Actions Not Yet Closed chart (chart 2) to only link to 3 weeks of data, rather than 12 AND having reduced the Plot Area WIDTH so that the chart column widths are closer to those in the first chart of 12 weeks (chart 1).

My next step would be to
1) overlay chart 1 with chart 2 (of course, you’ll need to make the Chart 2 Chart Area and Plot Area have NO FILL.
2) record the width of both Plot Area WIDTHS.
3) calculate the chart 2 Plot Area WIDTHS for 1 to 11 by extrapolation and put those values in a table for use for weeks 1-11.
4) calculating the source data range to comport with the current week and the selected week range.

Thats an extra pence worth.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Defult values for weeks in Listbox

(OP)
I cant see anywhere to view/modify the plot area widths

RE: Defult values for weeks in Listbox

You can do it manually by selecting any chart object, like Plot Area, when you select any Chart on your sheet. Chart Tools > Format...and select Plot Area. Then drag the Plot Area width left or right.

But you’ll eventually need to do this via VBA.

On your sheet you have a ChartObject object that has a Chart object and the Chart object has a ChartArea and a PlotArea, for instance...

CODE

With ActiveSheet.ChartObjects(1).Chart
   With .PlotArea
      .Width = 50
   End With
End With 

I think I got that right. I’m not at my laptop at this time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close