MS 2003 to 2007 Migration

[ninja1980]

Hi all

I’ve got a very interesting little problem (or not as the case may be).

I’ve been developing some very simple tools for my company in the form of some standard spreadsheets. We have recently switched over from Office 2003 to 2007. Below is an extract of code from the spreadsheet that doesn’t seem to do anything anymore. Now for the really interesting bit, when I copy the sheet into a new workbook, the code works perfectly until the document is saved at which point it reverts to being “broken”.

Set myrange = ActiveCell
Application.ScreenUpdating = False

  Select Case Target.Address

    Case "$D$10"
        If ActiveCell = "Vapour" Then
            Range("D17:D18").Select
            Selection.Interior.ColorIndex = 35
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
           
            Range("D19:D21").Select
            Selection.Interior.ColorIndex = xlNone
            
            With Selection.Borders(xlDiagonalDown)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlDiagonalUp)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        
        Else
        
            Range("D19:D21").Select
            Selection.Interior.ColorIndex = 35
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    
            Range("D17:D18").Select
            Selection.Interior.ColorIndex = xlNone
            
            With Selection.Borders(xlDiagonalDown)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlDiagonalUp)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With

        End If

****REST OF CODE MISSING****

Any help would be very much appreciated

 

[PHV]

until the document is saved
As an .xlsm ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ninja1980]

It is saved as a *.xlsm, I just cant work out why this is happening??

 

[AnotherHiggins]

What is your Macro Security set to? (Make sure to enable the Developer tab if you haven't already)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ninja1980]

All Macro settings are on low.

 

[AnotherHiggins]

I pasted that code into a module and saved the workbook as an .xlsm in 2007 - it seems to be working for me.

Have you tried stepping through the code with [F8] to watch it do its thing?

One tip, though - you can avoid using Select by using another set of withs. Using Select and Activate slows down your code and should be avoided wherever possible.

    Case "$D$10"
        If ActiveCell = "Vapour" Then
            With Range("D17:D18")
                .Interior.ColorIndex = 35
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
            End With

            With Range("D19:D21")
                .Interior.ColorIndex = xlNone
                With .Borders(xlDiagonalDown)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                With .Borders(xlDiagonalUp)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End With
        Else
....

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

I infer that your code might be in one of the Sheet or Workbook EVENT processes.

Please not that the TARGET object refers to the range that was acted upon (changed or selected).

The ActiveCell object can and often does refer to something entirely different than the TARGET object.

As John suggested, I avoid using Active ANYTHING.

Is this what you're trying to do?

    Dim r1 As Range, r2 As Range
    
    Select Case Target.Value
        Case "Vapour"
            Set r1 = Range("D17:D18")
            Set r2 = Range("D19:D21")
        Case Else
            Set r2 = Range("D17:D18")
            Set r1 = Range("D19:D21")
    End Select
    With r1
        .Interior.ColorIndex = 35
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
    End Select
    
    With r2
        .Interior.ColorIndex = xlNone
        With .Borders(xlDiagonalDown)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlDiagonalUp)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With

   Set r1 = nothing
   set r2 = nothing

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BaldyMcFatFat]

I have heard it said several times and places not to use ACTIVE____ at all. Is there a specific reason for this? I have had to self teach myself what I can about VBA and programmin in general, and therefore I am still ignorant in a lot of the nuances of it.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[PHV]

Have a look here:
faq707-4105

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

It is perfectly appropriate to use something like range("A2").Select if you want to control where the cursor is when another user opens the file.

Other than that, using .Activate and .Select slows down code. This might not be evident if you're dealing with a very small dataset, but it's just good practice to avoid it.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fumei]

And for all those non-Excel obsessed people...the same thing applies for Word.

Selecting (anything) should be avoided, and 99% of the time is not needed.

Baldy McFatFat (great handle BTW)
"I have heard it said several times and places not to use ACTIVE____ at all. Is there a specific reason for this? "

Yes, there is a specific reason. Whether it is Excel, or Word, selecting something means the instruction is passed through the GUI whether ScreenUpdating = False, or not. Setting ScreenUpdating = False does help a lot, as the results of the instructions are not updated on screen. However, AFAIK the instruction is still passed through the GUI.

This takes time, and perhaps more significantly, it uses resources (memory addresses).

As anotherhiggins points out, if there are only a few selects being done, hey, it is not a big deal. Current machines have the resources to make it hardly detectable. However, if you check out the link PHV posted, Skip's example states the difference of code usage is FIVE times! That will add up if you are doing a lot of selecting. Therefore, as in most cases you do not actually have to select, then...don't. It is - as stated - a good practice.

Actually, as a general "best practice"...if you do not have to do something...don't.

Gerry