show the recorded code.

Erik,

Take a look at this code - it bases the plot ranges on named ranges within
the worksheet and redefines the worksheet names (so the chart follows the
names), mobing them down (or up, depending on which macro you use) by one row
)the weeks running downeards in a column). The chart plots three ranges, one
for actual hours, one for budget hours and one for cumulative hours, so you
may want to take one or more out, depending on your requirements.
It looks horrendous, but it will make a lot more sense when it's pasted into
a code module!

You'll need to manually set up the chart the first time you use it, creating
the range names specified in the code.

The code also ensures that you can't move the plot range below the last row
containing a week number, or above the first row containing a week number.

Sub HoursGoForwardOneWeek()
   'This macro shifts the current X Axis, Actual, Budget and Cumulative
ranges in the HOURS chart FORWARD by one week.
   'It ensures that the LAST week of the proposed new plot range is not
lower than the LAST week in XAxislabels.
 
   SheetUnprotect
   DefineXAxisDetails
   
   DefineHoursPlotRangeDetails
       
   If LastXAxisLabelRow = HoursLastRow Then
       MsgBox ("You can't shift the plot range down any more, as the last
row plotted is the last row in the X-Axis!")
       Exit Sub
   End If
   
   ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Offset(1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Offset(1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Offset(1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Offset(1, 0)
   
   DefineHoursPlotRangeDetails
   SheetProtect
           
End Sub

Sub HoursGoBackOneWeek()
   'This macro shifts the current X Axis, Actual, Budget and Cumulative
ranges in the HOURS chart BACK by one week.
   'It ensures that the FIRST week of the proposed new plot range is not
lower than the FIRST week in XAxislabels.
   
   SheetUnprotect
   DefineXAxisDetails
   
   DefineHoursPlotRangeDetails
         
   If FirstXAxisLabelRow = HoursFirstRow Then
       MsgBox ("You can't shift the plot range up any more, as the first
row plotted is the first row in the X-Axis!")
       Exit Sub
   End If
   
   ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Offset(-1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Offset(-1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Offset(-1, 0)
   ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Offset(-1, 0)
   
   DefineHoursPlotRangeDetails
   SheetProtect
             
End Sub

Sub DefineXAxisDetails()
   
   Set Database = Sheets("Status Report")
   Set DBCR = Database.Range("HeaderXAxis").CurrentRegion
   
   DBCR.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "XAxisLabels"
   Set XAxisLabels = Database.Range("XAxisLabels")
           
   XAxisLabelsSize = XAxisLabels.Rows.Count 'number of rows in "XAxisLabels"
   FirstXAxisLabelRow = XAxisLabels.Cells(1, 1).Row 'row number of FIRST X
axis label
   LastXAxisLabelRow = XAxisLabels.Cells(XAxisLabelsSize, 1).Row 'row
number of LAST X axis label
   FirstXAxisLabelWeek = Left(XAxisLabels.Cells(1, 1).Value, 2) 'first two
chars of FIRST X axis label
   LastXAxisLabelWeek = Left(XAxisLabels.Cells(XAxisLabelsSize, 1).Value,
2) 'first two chars of LAST X axis label
   
   'MsgBox ("Number of X Axis Labels: " & XAxisLabelsSize & vbCrLf & vbCrLf
& _
       "First X Axis Label: row " & FirstXAxisLabelRow & " (week " &
FirstXAxisLabelWeek & ")" & vbCrLf & _
           "Last X Axis Label: row  " & LastXAxisLabelRow & " (week " &
LastXAxisLabelWeek & ")")
End Sub

Sub DefineHoursPlotRangeDetails()
   HoursPlotSize = Range("Hours_XAxis").Rows.Count 'number of rows in X
axis of HOURS chart
   HoursFirstRow = Range("Hours_XAxis").Cells(1, 1).Row 'row number of
FIRST row in X axis of HOURS chart
   HoursLastRow = Range("Hours_XAxis").Cells(HoursPlotSize, 1).Row 'row
number of LAST row in X axis of HOURS chart
   HoursFirstWeek = Left(Range("Hours_XAxis").Cells(1, 1).Value, 2) 'first
2 chars of FIRST X axis label (WkNo) of HOURS chart
   HoursLastWeek = Left(Range("Hours_XAxis").Cells(HoursPlotSize, 1).Value,
2) 'first 2 chars of LAST X axis label (WkNo)of HOURS
   'MsgBox ("Plotting " & HoursPlotSize & " rows" & vbCrLf & vbCrLf & _
       "First row: " & HoursFirstRow & " (week " & HoursFirstWeek & ")" &
vbCrLf & _
           "Last row: " & HoursLastRow & " (week " & HoursLastWeek & ")")
End Sub

----------------------------------------------------------------------------------------
If you want to redefine the number of rows/weeks your chart plots, use this:

Sub ResizeHours()
   
   SheetUnprotect
   DefineXAxisDetails
   DefineHoursPlotRangeDetails
       
   Message = "Number of weeks"
   Title = "How many weeks' information do you want to plot?"
   DefaultSize = 8
   
   NewSize = InputBox(Message, Title, DefaultSize)
   
   If NewSize < 1 Then
       Exit Sub
   End If
   
   If Not (IsNumeric(NewSize)) Then
       Exit Sub
   End If
   
   ProposedHoursLastRow = HoursFirstRow + NewSize - 1
   'MsgBox ("Modify Hours Graph" & vbCrLf & vbCrLf & _
       "Starting at row " & HoursFirstRow & " and continuing for " &
HoursNewSize & _
           " rows would finish at row " & ProposedHoursLastRow & vbCrLf & _
               "The last X axis label row is " & LastXAxisLabelRow)
   
   If ProposedHoursLastRow > LastXAxisLabelRow Then
       MsgBox ("You can't extend the plot range this far as it will extend
beyond the bottom of the X Axis!" _
           & vbCrLf & "Try moving your plot range back before attempting to
extend it by this number of rows!")
       Exit Sub
   End If
   
   ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Resize(NewSize, 1)
   
   DefineHoursPlotRangeDetails
   SheetProtect
   
End Sub

----------------------------------------------------------------------------------------
Good luck

Pete

Erik,

Also, be aware that my code contains some references to updating cells on
the worksheet that you probably won't need. Hope it makes sense.

It took me a LONG time to understand what I needed to do, especially the
bits about making sure the named ranges picked up by the chart aren't moved
up or down too far - it DOES work, believe me -I went through too many user
demonstrations for it not to by now..! :-)

Pete

Erik,

Not sure if I included this in my other postings, but here's the code to
reset the named ranges picked up by the chart, so they move to the top of the
range containing your week numbers. Make sure that if you have field headers
above your data ranges that there is at least one blank row separating them
from any other entries in your worksheet. This application was created as a
database, and as such, adheres to the rules for Excel databases.

Sub ResetHours()
   'This macro resets the X Axis, Actual, Budget and Cumulative ranges in
the HOURS chart to start from the first
   'row of the XAxisLabels range. It requests the number of weeks that are
required to be plotted
   'and ensures that this would not exceed the length of the XAxisLabels
range.
   
   SheetUnprotect
   DefineXAxisDetails
   
   Message = "Reset Hours Graph"
   Title = "Reset Hours Graph"
   DefaultSize = 10
   
   NewSize = InputBox(Message, Title, DefaultSize)
   
   If NewSize < 1 Then
       Exit Sub
   End If
   
   If Not (IsNumeric(NewSize)) Then
       Exit Sub
   End If
   
   ProposedHoursResetLastRow = Range("HeaderXAxis").Offset(1, 0).Row +
NewSize - 1
   
   'MsgBox ("First row would be: " & Range("HeaderXAxis").Offset(1, 0).Row
& vbCrLf & _
       "Last row would be: " & ProposedHoursResetLastRow & vbCrLf & _
           "Last X axis label row is: " & LastXAxisLabelRow)
           
   If Range("HeaderXAxis").Offset(1, 0).Row + NewSize > LastXAxisLabelRow
Then
       MsgBox ("You can't reset the chart to this number of rows, as the
last row plotted would be " & vbCrLf & _
           "lower than the last row in the X-Axis!")
       Exit Sub
   End If
   
   ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("HeaderXAxis").Offset(1, 0).Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("HeaderTotalHours").Offset(1, 0).Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("HeaderBudgetHours").Offset(1, 0).Resize(NewSize, 1)
   ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("HeaderCumulativeHours").Offset(1, 0).Resize(NewSize, 1)
   SheetProtect
End Sub

-----------------------------------------------------------------------------------
I'll go home now - good luck!

Pete