+ Reply to Thread
Results 1 to 4 of 4

Backward compatability

  1. #1
    Michael Beckinsale
    Guest

    Backward compatability

    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



  2. #2
    Bob Phillips
    Guest

    Re: Backward compatability

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  3. #3
    Tom Ogilvy
    Guest

    Re: Backward compatability

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  4. #4
    Michael Beckinsale
    Guest

    Re: Backward compatability

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1