Macro / Custom View

[vecia]

I need some help with creating 'custom views'.

I have a workbook to store test marks for school children. There is a
worksheet per year and each worksheet contains around 170 columns to
contain all of their marks from years 7 - 9.

I need to be able to create custom views, so that the user can click a
button and just see columns A-Z for example i.e. all other columns
hidden, in order to make the sheet more manageable.

How ever this is done, it needs to work in the current active sheet,
i.e. whichever year the user is looking at.

I realise that I can't do this with custom views unless I set custom
viws up in every sheet but the idea is that in future years, I will
have handed over and they will be able to insert new sheets
themselves.

I also tried creating a macro by recording which columns to hide but
again, I can only get this to work in the sheet I recorded it in.

Can anyone help?

 

[Jim Cone]

v,
The following code show 26 columns at a time.
Each time showing the next consecutive group.
The constant "lngWidth" determines the number of columns to show.
You can put a button on a toolbar to run the code and cycle thru the columns.
With some work you could modify the code to add a reverse direction and
use another button on the toolbar to initiate that sequence.

Jim Cone
San Francisco, USA
'-------------------------

Sub OnlyTheOnesILove()
On Error GoTo VisibleError
Dim rngVisible As Excel.Range
Dim lngFirst As Long
Dim lngLast As Long
'ADJUST AS NEEDED...
Const lngWidth As Long = 26

'Check for hidden columns
On Error Resume Next
Set rngVisible = Rows(1).SpecialCells(xlCellTypeVisible).Cells
On Error GoTo VisibleError
If Not rngVisible Is Nothing Then
If rngVisible.Count < Columns.Count Then
'Some columns are visible.
lngLast = rngVisible.Columns(rngVisible.Columns.Count).Column
If lngLast > Columns.Count - lngWidth - 1 Then
lngFirst = 1
Else
lngFirst = lngLast + 1
End If
lngLast = lngFirst + lngWidth - 1
Else
'All columns are visible
lngFirst = 1
lngLast = lngWidth
End If
ActiveSheet.Columns.Hidden = True
Range(Columns(lngFirst), Columns(lngLast)).Hidden = False
Else 'All columns are hidden.
lngFirst = 1
ActiveSheet.Columns.Hidden = False
End If

'Force top left cell to top left corner.
ActiveWindow.ScrollColumn = lngFirst
Cells(1, lngFirst).Select
Set rngVisible = Nothing
Exit Sub
VisibleError:
ActiveSheet.Columns.Hidden = False
End Sub
'-------------------------------


"vecia"
<[email protected]>
wrote in message
need some help with creating 'custom views'.
I have a workbook to store test marks for school children. There is a
worksheet per year and each worksheet contains around 170 columns to
contain all of their marks from years 7 - 9.
I need to be able to create custom views, so that the user can click a
button and just see columns A-Z for example i.e. all other columns
hidden, in order to make the sheet more manageable.
How ever this is done, it needs to work in the current active sheet,
i.e. whichever year the user is looking at.
I realise that I can't do this with custom views unless I set custom
viws up in every sheet but the idea is that in future years, I will
have handed over and they will be able to insert new sheets
themselves.
I also tried creating a macro by recording which columns to hide but
again, I can only get this to work in the sheet I recorded it in.
Can anyone help?--
vecia

 

[vecia]

Ok, thanks for that, I will give it a try