Hi All,

I have developed a workbook in Excel 2003 with a lot of VBA code. I was
originally told that all the users were on Excel 2003 but it transpires that
a number are on Excel 97 & 2000 and l am trying to 'modify' the workbook to
work on all versions from 97 upwards.

The following code (A) is giving me real problems. It works perfectly well
in Excel 2003 but always crashes in Excel 97. I pretty sure it is to do with
the method of hiding / unhiding columns. The macro recorder in Excel 97
records the code at (B) but l am pretty sure that you should be able to set
the column properties without having to select the column ranges.

The code is attached to a cbx _Change event

Can anybody tell me what is wrong with the code ?
Secondly will the solution needs to run on Excel 2000 & 2003 as well as 97

All help gratefully appreciated

xxxxx Code A - Excel 2003 xxxxxxxxxxxxxxxxxxxxxxxx
Private Sub cbxPrimaryRole_Change()

Dim ColRef
On Error Resume Next
ActiveSheet.Unprotect ("mbNOMIS7")
Columns("F:CZ").Hidden = True

If Range("DQ2").Value = 0 Then
ColRef = "ALL"
Else
ColRef = Range("DQ2").Value
End If

If ColRef = "ALL" Then
Columns("F:CZ").Hidden = False
Else
Columns(ColRef).Hidden = False
End If
ActiveSheet.Protect ("mbNOMIS7")
On Error GoTo 0

End Sub

xxxxxx Code B - Excel 97 xxxxxxxxxxx

Range("F11:CZ11").Select
Selection.EntireColumn.Hidden = False



Regards

Michael Beckinsale

I tried it on XL97 and XP and it works fine on both.

Are there any other peculiarities with the workbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael Beckinsale" wrote in message
...
Hi All,

I have developed a workbook in Excel 2003 with a lot of VBA code. I was
originally told that all the users were on Excel 2003 but it transpires
that
a number are on Excel 97 & 2000 and l am trying to 'modify' the workbook
to
work on all versions from 97 upwards.

The following code (A) is giving me real problems. It works perfectly well
in Excel 2003 but always crashes in Excel 97. I pretty sure it is to do
with
the method of hiding / unhiding columns. The macro recorder in Excel 97
records the code at (B) but l am pretty sure that you should be able to
set
the column properties without having to select the column ranges.

The code is attached to a cbx _Change event

Can anybody tell me what is wrong with the code ?
Secondly will the solution needs to run on Excel 2000 & 2003 as well as 97

All help gratefully appreciated

xxxxx Code A - Excel 2003 xxxxxxxxxxxxxxxxxxxxxxxx
Private Sub cbxPrimaryRole_Change()

Dim ColRef
On Error Resume Next
ActiveSheet.Unprotect ("mbNOMIS7")
Columns("F:CZ").Hidden = True

If Range("DQ2").Value = 0 Then
ColRef = "ALL"
Else
ColRef = Range("DQ2").Value
End If

If ColRef = "ALL" Then
Columns("F:CZ").Hidden = False
Else
Columns(ColRef).Hidden = False
End If
ActiveSheet.Protect ("mbNOMIS7")
On Error GoTo 0

End Sub

xxxxxx Code B - Excel 97 xxxxxxxxxxx

Range("F11:CZ11").Select
Selection.EntireColumn.Hidden = False



Regards

Michael Beckinsale

add this line

Private Sub cbxPrimaryRole_Change()

Dim ColRef

ActiveCell.Activate

On Error Resume Next
ActiveSheet.Unprotect ("mbNOMIS7")
Columns("F:CZ").Hidden = True

If Range("DQ2").Value = 0 Then
ColRef = "ALL"
Else
ColRef = Range("DQ2").Value
End If

If ColRef = "ALL" Then
Columns("F:CZ").Hidden = False
Else
Columns(ColRef).Hidden = False
End If
ActiveSheet.Protect ("mbNOMIS7")
On Error GoTo 0

End Sub

This is a focus problem that occured in xl97, but has been fixed in later
versions.

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in message
...
Hi All,

I have developed a workbook in Excel 2003 with a lot of VBA code. I was
originally told that all the users were on Excel 2003 but it transpires
that
a number are on Excel 97 & 2000 and l am trying to 'modify' the workbook
to
work on all versions from 97 upwards.

The following code (A) is giving me real problems. It works perfectly well
in Excel 2003 but always crashes in Excel 97. I pretty sure it is to do
with
the method of hiding / unhiding columns. The macro recorder in Excel 97
records the code at (B) but l am pretty sure that you should be able to
set
the column properties without having to select the column ranges.

The code is attached to a cbx _Change event

Can anybody tell me what is wrong with the code ?
Secondly will the solution needs to run on Excel 2000 & 2003 as well as 97

All help gratefully appreciated

xxxxx Code A - Excel 2003 xxxxxxxxxxxxxxxxxxxxxxxx
Private Sub cbxPrimaryRole_Change()

Dim ColRef
On Error Resume Next
ActiveSheet.Unprotect ("mbNOMIS7")
Columns("F:CZ").Hidden = True

If Range("DQ2").Value = 0 Then
ColRef = "ALL"
Else
ColRef = Range("DQ2").Value
End If

If ColRef = "ALL" Then
Columns("F:CZ").Hidden = False
Else
Columns(ColRef).Hidden = False
End If
ActiveSheet.Protect ("mbNOMIS7")
On Error GoTo 0

End Sub

xxxxxx Code B - Excel 97 xxxxxxxxxxx

Range("F11:CZ11").Select
Selection.EntireColumn.Hidden = False



Regards

Michael Beckinsale

Bob & Tom,

Thanks very much for your input. Sorry for the delay in replying but l was
trying to find some "peculiarity" for Bob.

Tom l put in the line you suggested and it all works fine now. Is there a
'list' anywhere of these known bugs so that l can refer to them before haing
to pester the newsgroup?

Again many thanks. I have been trying to overcome this problem since 7.30
this morning! Begining to think that my old laptop / Excel / operating
system was dying on me !

"Michael Beckinsale" wrote in message
...
Hi All,

I have developed a workbook in Excel 2003 with a lot of VBA code. I was
originally told that all the users were on Excel 2003 but it transpires
that a number are on Excel 97 & 2000 and l am trying to 'modify' the
workbook to work on all versions from 97 upwards.

The following code (A) is giving me real problems. It works perfectly well
in Excel 2003 but always crashes in Excel 97. I pretty sure it is to do
with the method of hiding / unhiding columns. The macro recorder in Excel
97 records the code at (B) but l am pretty sure that you should be able to
set the column properties without having to select the column ranges.

The code is attached to a cbx _Change event

Can anybody tell me what is wrong with the code ?
Secondly will the solution needs to run on Excel 2000 & 2003 as well as 97

All help gratefully appreciated

xxxxx Code A - Excel 2003 xxxxxxxxxxxxxxxxxxxxxxxx
Private Sub cbxPrimaryRole_Change()

Dim ColRef
On Error Resume Next
ActiveSheet.Unprotect ("mbNOMIS7")
Columns("F:CZ").Hidden = True

If Range("DQ2").Value = 0 Then
ColRef = "ALL"
Else
ColRef = Range("DQ2").Value
End If

If ColRef = "ALL" Then
Columns("F:CZ").Hidden = False
Else
Columns(ColRef).Hidden = False
End If
ActiveSheet.Protect ("mbNOMIS7")
On Error GoTo 0

End Sub

xxxxxx Code B - Excel 97 xxxxxxxxxxx

Range("F11:CZ11").Select
Selection.EntireColumn.Hidden = False



Regards

Michael Beckinsale