Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    256 column limit (2000 to 2007)

    I have a workbook created in XL 2000, which means it is limited to a max of 256 columns. No problem, it only uses about 7!

    Having opened the workbook in XL 2007, when I come to close it I get a compatibility warning that says since I'm going to save it in (its native) .xls format I'm going to loose columns beyond 256! It even thinks the workbook refers to stuff beyond this limit!

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    I'm off to Google this 'issue' more. Meanwhile has anybody here seem this sort of nonsense before?

    stuck

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    What column is selected if you hit control+end when this message starts appearing?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    The workbook contains three worksheets, ctrl+end takes you to
    E15 on the first sheet
    R38 on the other worksheets

    The message only appears when I click Close and a macro (below) in ThisWorkbook runs in response to the Workbook_BeforeClose event. If I cancel the dialog displaying the error (screen dump attached) then cancel the .Save in the last line of the macro then it appears that the macro has run as I expect and only the first sheet is visible. ctrl+end takes me to E15. The Compatability Checker implicates both of the two hidden sheets as containing data beyond the std XL limits but that is nonsense, the workbook is native XL 2000!

    stuck
    -----------
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ScreenUpdating = False
    ActiveWorkbook.Unprotect password:="---hard wired password---"

    For Each sh In Sheets
    On Error Resume Next
    If sh.Range("Q1").Value = 0 And sh.Next.Range("P1").Value = 1 Then sh.Range("c11").Value = Time
    sh.Visible = xlVeryHidden
    sh.Protect password:="---hard wired password---", UserInterfaceOnly:=True
    Next sh

    Range("B2:C2,B4,D4,B6,B12").Select
    Selection.Locked = True
    Range("B2").Select

    With ActiveWorkbook
    .Protect password:="---hard wired password---"
    .Save
    End With

    End Sub
    ----------
    Attached Images Attached Images

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    Does it help to add

    Application.DisplayAlerts=False
    before the save, and
    pplication.DisplayAlerts=True
    after the save?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    Yes, that 'solves' the problem - or at least sweeps it under the carpet <img src=/S/grin.gif border=0 alt=grin width=15 height=15> !

    The other thing that makes it go away is to untick the 'Check compatibility...' box

    I suppose this effect is actually an undocumented feature in the compatibility checking routine

    Thanks,

    Ken

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    Further to my comment about the Compatibility checker and for the sake of completeness...

    Having commented out the .DisplayAlerts line you suggested and unticked the Compatibility checker then, as I indicated above, the warning of impending doom goes away.

    If I then re-open the workbook and explicitly run the Compatibiliy checker (via: Office button | Prepare | Run Compat...) then the dialog box tells me "No compatibility issues were found."

    Ken

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    So much for consistency <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 256 column limit (2000 to 2007)

    <img src=/S/ranton.gif border=0 alt=ranton width=66 height=37>OOOO! don't get me started on consistency... You don't have to dig that deep into the new 'fluent' (cough, cough) interface to discover dialog boxes unchanged since Office 2000. Visions of the Emperor wearing his New Clothes under a fur coat come to mind. <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>

    Ken

Posting Permissions

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