Solved Workbook with compatability issues

September 14, 2016 at 03:16:53
Specs: Windows 64
Hi Guys, I have developed a workbook over the last 2 1/2 years on Windows 2003. I have run a compatability report on Windows 10 as the workbook wont work as it used to on W3. I have various messages such as

Some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors. 4416
Excel 97-2003

A worksheet in this workbook contains a sort state with more than three sort conditions. This information will be lost in earlier versions of Excel. 3
Excel 97-2003
Excel 97-2003
Excel 97-2003

Can someone direct me on how to solve these issues. I don't know if there are any short cuts or that I have to rewrite the whole workbook
Any help would be greatly appreciated


See More: Workbook with compatability issues

Reply ↓  Report •


✔ Best Answer
September 21, 2016 at 19:40:11
Well, I've played around with you workbook and I am able to recreate the "jumbled" screens.

A couple of other things happened:

1 - Your instructions say to select any name from the Course Name drop down. I don't see any names, just these 6 entries.

3
4
4
4
5
4

2 - Maybe it's me and my unfamiliarity with using the workbook, but something weird happens with the password when I click the "Open" button on Competition Entry. Sometimes the casino49 password works and sometimes I need to enter the password that you included in the email. It was hard to reproduce the actions I did so I could figure out which password to use. When one didn't work, I just tried the other one.

OK, now, my guess is that your workbook is corrupted. There is too much going on for me to figure out what is happening with all your macros and I don't have the energy to try and reverse engineer the entire workbook. If you have a write-up or a flow chart of some kind, I'll take a look at it and dig a little deeper, but I'm not going to try and figure which macro runs when on my own.

Last but not least, I may have found a workaround that may or may not help. It seems that if I select the Dashboard worksheet before doing anything on the sheet I'm supposed to be working on, the jumble does not occur. For example, if I click the Scorecard Entry button to open that sheet and then before clicking the name drop down I click the Dashboard tab and then the Scorecard tab, I can then use the drop down and not see any jumble. If that works every time, then maybe you could add code to Select the Dashboard and then re-select the sheet you are working on. Maybe the jumble might not occur.

Other than that, I don't know that I can be of any help. I can only assume that something got corrupted when you did the conversion.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 14, 2016 at 05:50:43
I'm confused. I'm no OS expert, but I don't think there was ever a Windows 2003. There was a Windows Server 2003, but no Windows 2003.

Besides, the OS shouldn't matter as far as how the features within Excel work. If the OS version and the Excel version are compatible, the features will work. In others words, the OS version won't inhibit any features of the application as long as the application can run under that OS.

Is it possible that you meant Excel 2003?

If so, I'm still confused. Excel 2003 never supported more than 7 Nested IF's, so I don't see how the workbook even worked in Excel 2003.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
September 14, 2016 at 08:10:05
Hi DerbyDad03, been a while since we conversed. With a great deal of your assistance, I have created a workbook, IN OFFICE EXCEL 2007. I have had to upgrade my Home system and was supplied with Windows 10.
I loaded all my workbooks onto a memory stick and downloaded onto the new PC
This workbook is full of Formula and VBA's but will not perform as it has been.
I ran a compatability check in W10 and the following messages appeared

If the workbook is saved in an earlier file format or opened in an earlier version of Microsoft Excel, the listed features will not be available.

Significant loss of functionality # of occurrences Version

This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error. 20927
Excel 97-2003
DASHBOARD'!H6:H29 Excel 97-2003
DASHBOARD'!M6:M29
Excel 97-2003
Some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors. 4416
Excel 97-2003
Some cells in this workbook contain data validation rules, which refer to values on other worksheets. These rules won't be saved. 16
DASHBOARD'!P10 Excel 97-2003
Excel 97-2003
Minor loss of fidelity

A worksheet in this workbook contains a sort state with more than three sort conditions. This information will be lost in earlier versions of Excel. 3
Excel 97-2003
Excel 97-2003
Excel 97-2003

Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. 925 Excel 97-2003

One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be supported in earlier versions of Excel. 15
DASHBOARD'!P10 Excel 2007

Is the problem because it is Office Excel 2007?


Reply ↓  Report •

#3
September 14, 2016 at 08:31:14
I'm still confused and therefore may not be able to help you.

The messages seem to be saying that if you save the files in a "earlier format" you will lose functionality. Why not just save them in the 2007 (and beyond) file format?

xlsx for workbooks without macros
xlsm for workbooks with macros

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
September 14, 2016 at 10:30:37
On what version of Excel were the workbooks created?
Excel 2003 - Excel 2007 - Excel 2010

On what version of Excel are you trying to run them?

What version of Excel is on the New Windows 10 operating system machine?

I know that Microsoft Office has a Compatibility Checker but was
unaware of any such program in the Windows 10 operating system.

the workbook wont work as it used to

What is happening that is not supposed to be happening?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
September 14, 2016 at 10:47:45
re: "What is happening that is not supposed to be happening?"

or...

What is not happening that is supposed to be happening?

;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
September 15, 2016 at 04:07:13
Hi Guys,
To answer some of your questions
The workbook has been created on Excel 2007 and saved as .xlsm with macros
Tranferred this via memory stick from laptop to main Home system
Windows 10 is operating Excel 2016

All sheets are hidden except for the sheet titled Dashboard. This sheet has a number of FormControl buttons. If I click on one of these buttons, it will open up a second sheet. When I start to enter information into cells, I TAB from unprotected cell to unprotected cell. After about 3 TABs, the two sheets transpose onto each other, until I click another FC Button. Then the second sheet appears fine again until I start entering the next lot of info and TABBING
Also on some of the VBA,s, are KNOW coming up with a runtime error, such as the following

Sub AutoSortall2()

    Application.ScreenUpdating = False
    
    colNum = Sheets("SCARD2").Range("D36")

   If MsgBox("HAVE ALL THE SCORECARDS BEEN ENTERED?", vbYesNo) = vbYes Then
   
    Sheets("SCARD2").Visible = False
    
    Sheets("POSIT").Visible = True
    Sheets("POSIT").Select
    
  AutoSort_V3 Range(Cells(3, colNum - 5), Cells(51, colNum + 1))
    
' unprotect sheet
    Sheets("POSIT").Unprotect Password:="2L4NTJEzNuKn"
        Sheets("PLACES").Visible = True
             Sheets("PLACES").Unprotect Password:="2L4NTJEzNuKn"
    
    Sheets("POSIT").Range(Cells(3, colNum - 5), Cells(52, colNum + 1)).Copy
    
    Sheets("POSIT").Visible = False
    
    With Sheets("PLACES").Cells(3, colNum - 5)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      
    End With
    Application.CutCopyMode = False
    
        Rows(Range("HCAPADJ!C53").Value & ":52").EntireRow.Hidden = False
            Rows(Range("HCAPADJ!C53").Value + 8 & ":51").EntireRow.Hidden = True
        
        Application.GoTo Sheets("PLACES").Cells(1, colNum - 7), True
   
' protect sheet
    Sheets("POSIT").Protect Password:="2L4NTJEzNuKn", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("PLACES").Protect Password:="2L4NTJEzNuKn", DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Any help would be accepted


Reply ↓  Report •

#7
September 15, 2016 at 05:59:37
re: After about 3 TABs, the two sheets transpose onto each other, until I click another FC Button.

I don't know what that means.

re: Also on some of the VBA,s, are KNOW coming up with a runtime error, such as the following

Posting the code doesn't help unless you tell us where the error is occurring.

Please keep in mind that we are not working with your workbook and in such a complicated situation, it would be next to impossible for us to try and replicate your environment by setting up test workbooks.

We may not be of much help here. Sorry.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
September 15, 2016 at 06:14:51
Hi Derbydad03
Since sending the last message, the problem with the block of code occurred on the following line of code

Sheets("POSIT").Range(Cells(3, colNum - 5), Cells(52, colNum + 1)).Copy

Changing the coding to the following resolved the issue, in Excel 2016, but this wasn't an issue befoe

Sheets("POSIT").Range(Cells(3, colNum - 5), Cells(52, colNum + 1)).Select
Selection.Copy

re: After about 3 TABs, the two sheets transpose onto each other, until I click another FC Button.

By this I mean that if I have 2 worksheet tags/titles visible in the workbook, one being 'DASHBOARD' being permanently visible, and a second one.
On screen they mingle, in that I can see parts of each worksheet at the same time.
This occurs when I am entering data into a cell and then tab to the next cell to enter data
Why would this happen??
Please note that the workbook has over 40 worksheets. All bar one are hidden until required. Once they have been used they become hidden again.


Reply ↓  Report •

#9
September 15, 2016 at 06:18:23
PS You are more help than you realise, couldn't have got his far without your help


Reply ↓  Report •

#10
September 15, 2016 at 08:20:37
Tony
I'm not the best at writing or even understanding VBA macros,
best I can offer is to make sure you have all the latest Office Updates
and Windows 10 Updates.

If you can, you might want to install Office 2007 on your new Win 10
machine and see how that works.

Sorry, can't offer more assistance.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#11
September 16, 2016 at 05:07:00
No worries Mike, thanks for trying.
The code I have got sorted, but the issue of 2 worksheets mixing, that is, can see parts of each worksheet on the screen at the same time, is still a problem

Reply ↓  Report •

#12
September 16, 2016 at 07:24:41
All sheets are hidden except for the sheet titled Dashboard.
I TAB from unprotected cell to unprotected cell.

You have a lot going on in the workbook, with hidden sheets and protected
and unprotected sections of sheets and various VBA macros firing off at
different times.

How many sheets are we talking about? 2, 4, 10, 50 ?

There are several different ways to Hide a sheet, how are you hiding yours?

Have you tried running with the sheets UN-hidden to see if you get the same
jumbled effect?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
September 16, 2016 at 08:04:01
A couple of more thoughts. (Grasping at straws.)

Are you in Normal View or Page Layout?
What happens if you change from one to the other when the sheets get jumbled?

How about the Zoom level?
I know changing the zoom level has cured other problems when there were display problems.

You might try saving the file in the .XLSB or Binary Format and see if that
has any effect on the display problem.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#14
September 17, 2016 at 06:44:01
Hi Mike
To answer a few of your questions,
Number of sheets is 72
All sheets are hidden via VBA code

Sheets("nameofsheet").Visible = False

Have tried withal sheets unhidden and problem still occurs
Am in Normal View and tried as suggested, but problem still occurs
Changed zoom levels but prob still persists

Sheet named Dashboard is the constant visible sheet. When I unhide a second sheet, this has various cells that will only take data input, and start inputting data, as soon as I TAB from the first cell to the next cell, the DASHBOARD sheet jumbles with the second sheet. This happens only when I have typed data and then TAB. AS soon as I click on a control button, all I can see is the second sheet with no jumbling of the two sheets
I have tried to hide the DASBOARD sheet so that the only sheet that is visible is the sheet I need to input data, but the DASBOARD sheet still jumbles as before.

I am at a loss!!!!


Reply ↓  Report •

#15
September 17, 2016 at 11:00:20
as soon as I TAB from the first cell to the next cell,

Have you tried turning Automatic Calculation to Manuel?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#16
September 17, 2016 at 14:16:47
Can you try the workbook on another machine? That might help narrow the problem down to a workbook issue vs. an application issue.

Another (painful) alternative is to reduce the size of your workbook by half, then by half again, then by half again until the problem goes away. Once you have a workbook that doesn't exhibit the symptom, you can try to determine which sheet is causing the problem. It might just be one (or more) corrupted sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#17
September 20, 2016 at 07:32:20
Hi Guys,
Mike - tried your suggestion but problem still persists
DD03 - reduced workbook to around half its size and problem still persists

It seems that any sheet that I physically type data into cells, jumbles with the opening sheet (DASHBOARD). I was wondering if either of you guys would like a copy of the workbook, to be able to understand how it works, and maybe where the problem lies

Tony


Reply ↓  Report •

#18
September 20, 2016 at 07:37:39
I sent you an email address via PM. I won't be able to look at it until this evening US, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#19
September 20, 2016 at 07:42:42
re: "reduced workbook to around half its size and problem still persists"

I suggested "...reduce the size of your workbook by half, then by half again, then by half again until the problem goes away."

Reducing it by half just once may not have eliminated the corruption, if that is the problem. I'm not saying it is the issue, but I don't think you've tested my suggestion fully.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#20
September 20, 2016 at 07:57:08
Hi DD03,
When you get a chance to look at the WB, you will see that many of the worksheets perform actions in the background. These are crucial to the workings of the WB
I performed a YEAREND and then deleted all players bar 8, I think its the most I can remove. By doing this , the problem persists, so these sheets are maybe not causing the issue.
As your aware I learn every time I work on this , and I must be missing something, that's probably staring me in the face.
Please have a look and let me know what you think
Many thanks
Tony

Reply ↓  Report •

#21
September 20, 2016 at 11:26:52
Will I know what to do once I see the workbook?

Did you include a list of actions that cause the problem?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#22
September 20, 2016 at 12:21:05
Hi DD03,
Hopefully its visually self explanatory but then I am the one who uses it
Once open, the DASHBOARD page is visible
Click the control button 'COMPETITION ENTRY'
you will be prompted for a password 'casino49'
Hit the tab button and you will move between to cells
The box with a Course Name is a drop down box, select any name
Tab to the date box
Enter a date, eg todays date or yesterday, and TAB again
The screen then jumbles with the DASHBOARD screen
Hit the control button 'APPLY COMPETITION DATE'
the screen will return as unjumbled
The competition date is know pasted in the date block

You should see the problem I have. As a secondary process, close the workbook without saving, and reopen. Then go to @COMPETITION ENTRY'
enter the password

Hit the control button 'GO TO SCORECARD ENTRY'
The next page appears
TAB through this so you can see where the data is input
THe NAME cell is a drop down box, select a name
TAB to the next cell, enter a number between 2 and 9
tab and repeat till 18 numbers have been entered and you are back to the name cell
as you can see the screen is slightly jumbled as you go through this process
to enter the players score, hit the control button 'ENTER SCORECARD'
The screen will return to normal and you can repeat the process
Hit the forms button 'GOTO POSITIONS BOARD' and follow the prompts with 'yes'

This will take you back to the DASHBOARD

As you can see, any data input followed by a TAB to the next data cell causes the DASHBOARD to jumble with the sheet being worked on , on the screen until another VBA does its magic

The 'ADMIN CENTRE' also has the same thing happening, feel free to play around with it and let me have your feed back
Thanks
Tony



Reply ↓  Report •

#23
September 20, 2016 at 16:36:48
I did not receive any email from you via the email address I provided via PM.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#24
September 21, 2016 at 19:40:11
✔ Best Answer
Well, I've played around with you workbook and I am able to recreate the "jumbled" screens.

A couple of other things happened:

1 - Your instructions say to select any name from the Course Name drop down. I don't see any names, just these 6 entries.

3
4
4
4
5
4

2 - Maybe it's me and my unfamiliarity with using the workbook, but something weird happens with the password when I click the "Open" button on Competition Entry. Sometimes the casino49 password works and sometimes I need to enter the password that you included in the email. It was hard to reproduce the actions I did so I could figure out which password to use. When one didn't work, I just tried the other one.

OK, now, my guess is that your workbook is corrupted. There is too much going on for me to figure out what is happening with all your macros and I don't have the energy to try and reverse engineer the entire workbook. If you have a write-up or a flow chart of some kind, I'll take a look at it and dig a little deeper, but I'm not going to try and figure which macro runs when on my own.

Last but not least, I may have found a workaround that may or may not help. It seems that if I select the Dashboard worksheet before doing anything on the sheet I'm supposed to be working on, the jumble does not occur. For example, if I click the Scorecard Entry button to open that sheet and then before clicking the name drop down I click the Dashboard tab and then the Scorecard tab, I can then use the drop down and not see any jumble. If that works every time, then maybe you could add code to Select the Dashboard and then re-select the sheet you are working on. Maybe the jumble might not occur.

Other than that, I don't know that I can be of any help. I can only assume that something got corrupted when you did the conversion.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#25
September 22, 2016 at 01:18:05
Hi DD03,
Many thanks for having a look. Really appreciate your help. I was only looking for some guidance which from you is always beneficial

if I click the Scorecard Entry button to open that sheet and then before clicking the name drop down I click the Dashboard tab and then the Scorecard tab, I can then use the drop down and not see any jumble.

Thanks for this, I have had a play around and this seems to cure the problem for the short term. I will add some code and see what happens.

Going forward, I think I may have to start from scratch on a new workbook, but as long as the current one is usable, I a t least have something to record into

Again many thanks for your help
Tony


Reply ↓  Report •

#26
September 25, 2016 at 09:21:42
Great information thanks a lot...

Reply ↓  Report •

#27
September 25, 2016 at 09:51:46
Hi DD03,
Just a quick follow up, have played around a bit and sorted the problem to a usable end.
Added a bit of code as suggested and this seems to be sufficient for me to be happy with what I see
Many many thanks for your help with this, is greatly appreciated
Tony

Reply ↓  Report •

#28
September 25, 2016 at 12:13:27
I'm glad you got it to a point where you can use the workbook. It looks like you put a lot of effort into building it...it would be a shame to lose it due to the corruption that seems to have occurred because of the conversion to a later version.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •


Ask Question