Dashboard & Sheet switching

[Andy Roberts]

I am designing a dashboard / workbook for our projects which has different
sheets for different elements of each project (i.e. specification, expenses
etc). There will be one workbook for each project.

The problem I'm having is deciding on the best way to deal with the scenario
of needing different variations of the workbook depending on what type of
job it is. I have 5 sheets within my workbook and I was thinking of
creating a second set of 5 sheets (slightly different) for a different job
type and then having a combo box to select the job type which would switch
the user to a different version of sheet 1 with different hyperlinks to the
second set of sheets (not even sure this would work)

The other option is to create a separate workbook altogether and just open
which workbook is required for each job. However this would mean maintining
more than 1 workbook. Does anyone have any suggestions as to the best way
to proceed?

--
Regards

Andy
Win XP Pro
Office 2010

 

[isabelle]

hi,

you can create 10 sheets
Job 1-1, 1-2 job, job 1-3, 1-4 job, job 1-5
Job 2-1, 2-2 job, job 2-3, 2-4 job, job 2-5

Sub create_sheets()
Dim f, i As Integer
f = Array("job 1-1", "job 1-2", "job 1-3", "job 1-4", "job 1-5", "job
2-1", "job 2-2", "job 2-3", "job 2-4", "job 2-5")
For i = LBound(f) To UBound(f)
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = f(i)
Next
End Sub

at the event when the workbook opens, hide those sheets

on ThisWokbook page

Private Sub Workbook_Open()
sht = Array("job 1-1", "job 1-2", "job 1-3", "job 1-4", "job 1-5", "job
2-1", "job 2-2", "job 2-3", "job 2-4", "job 2-5")
For i = LBound(sht) To UBound(sht)
Sheets(sht(i)).Visible = xlVeryHidden
Next
End Sub

create two buttons job1 and job2

Private Sub CommandButton1_Click()
job1 = Array("job 1-1", "job 1-2", "job 1-3", "job 1-4", "job 1-5")
job2 = Array("job 2-1", "job 2-2", "job 2-3", "job 2-4", "job 2-5")

Application.ScreenUpdating = False

For i = LBound(job1) To UBound(job1)
Sheets(job1(i)).Visible = True
Next

For i = LBound(job2) To UBound(job2)
Sheets(job2(i)).Visible = xlVeryHidden
Next

Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
job1 = Array("job 1-1", "job 1-2", "job 1-3", "job 1-4", "job 1-5")
job2 = Array("job 2-1", "job 2-2", "job 2-3", "job 2-4", "job 2-5")

Application.ScreenUpdating = False

For i = LBound(job1) To UBound(job1)
Sheets(job1(i)).Visible = xlVeryHidden
Next

For i = LBound(job2) To UBound(job2)
Sheets(job2(i)).Visible = True
Next

Application.ScreenUpdating = True
End Sub

isabelle


Le 2013-06-17 05:17, Andy Roberts a écrit :