[RESOLVED] Sheet Move, VBA EXCEL

[billhuard]

Is there a short way to know if a sheet have moved inside a workbook.

Like Sheet1 was index 1 and the user have moved it to index 4.

Is there a way to catch this Event???

Thanks

[Comintern]

Not that I'm aware of. What are you trying to do? There might be a workaround.

[billhuard]

I have many sheet disposed in a workbook this way: Sheet1-->Total, Sheet2-->Sub-Total1, Sheet3-->Sale1-1, Sheet4-->Sale1-2, Sheet5--> Sub-Total2, Sheet6-->Sales2-1, Sheet7-->Sales2-2

If the sheet7 that is in sub-total2 is moved before sheet5, it will now belong to sub-total1, So the name need to change and the value from sheet need to be added to sub-total1.

All the code for changing the name and adding the data to sub-total1 is done, I cannot just get the macro started because I'm not able to find an event that catch sheet movement.

Any idea??

[Comintern]

Are you referencing sheets by number in your code? All you need to do is change the references to the sheet names if that's the case:

ActiveWorkbook.Worksheets("Total")
'instead of
ActiveWorkbook.Worksheets(1)

[billhuard]

The code is working perfectly when I'm starting it from the debug.
The problem is to start the macro when a user is dragging a saleX-X sheet from a sub-total to another

[DKenny]

Why not set the ProtectStructure Property of the workbook to TRUE. This will disallow the users from manually moving the sheets at all.
Then you could add a proc to move a sheet and in that proc you could amend your sub-totals in a controled manner.

[billhuard]

Yes and No, the spec I got told me to let the user manually move sheets wherever he wants. If I'm locking the structure and they have to do a special procedure to move, I don't think they will be very pleased.


But if there is no way to catch that kind of event, I won't have the choice...

[Comintern]

Can you post some code? I'm still confused as to why the index of the sheets is relevent. About the only thing I can think of to use them for is getting worksheet references, and you have lots of other ways to do that in code.

[Comintern]

By the way, you can also reference sheets by CodeName (technically it would be the class name of the sheet object). That way, you would always be able to get a concrete reference to the sheet even if it was re-named. Sheet1 will always be Sheet1, even if the user re-names it a couple times. The only place you can change the CodeName of the sheet is in the VBE.

[billhuard]

I'll show you why sheet index is important.
Same sheets as earlier: Sheet1-->Total, Sheet2-->Sub-Total1, Sheet3-->Sale1-1, Sheet4-->Sale1-2, Sheet5--> Sub-Total2, Sheet6-->Sales2-1, Sheet7-->Sales2-2

The sheet Total is obtained by adding all Sub Total Sheets.
The sheet Sub Total is obtained by adding all the sheets next to it until it founds another sub total sheets or reach the end of the workbook.

So.... Here is how it works

---BIG TOTAL----
Total on sheet1 = 20

---Sub Total 1----
Sub-total on sheet2 = 15
Sales on sheet3 = 10
sales on sheet4 = 5

---SubTotal 2----
Sub-Total on sheet5 = 5
Sales on sheet6 = 2
Sales on sheet7 = 3

If sheet7 is moved from SubTotal2 to SubTotal1 by the user.
The user will have to see this

---BIG TOTAL----
Total on sheet1 = 20

---Sub Total 1----
Sub-total on sheet2 = 18
Sales on sheet3 = 10
sales on sheet4 = 5
Sales on sheet7 = 3

---SubTotal 2----
Sub-Total on sheet5 = 2
Sales on sheet6 = 2

The problem is to start, automacticly, the recalculation of the sheets when the sheet7 is moved from subtotal2 to subtotal1.

Hope it will helps understand the problem, because the code will not help.

[billhuard]

If there is no way to do this, the easiest way I've found is to put a button on the sheets. If sheets are moved, click Here and It will launch the macro that recalculate everything. But it will be pretty poor the user side

[DKenny]

Bill
There is a way to do this, I'm working on a rough draft.
Three question that just occured to me are
1/ What happens if the user moves a sub-total sheet?
2/ What happens if the user moves the grandtotal sheet?
2/ What happens if after the user moves a sheet there is a sub-total sheet with no adjacent base sheets? i.e. the next sheet is also a sub-total?

[billhuard]

1/ What happens if the user moves a sub-total sheet?All the sheets on the right side of the sub total sheet will be added until the next sub total sheet or the end of the workbook
2/ What happens if the user moves the grandtotal sheet?Nothing, Sheet Total only add Sub Total sheets, and sheet total are special, so if the total sheet shows up between 2 sales sheets, the total sheet is skipped
3/ What happens if after the user moves a sheet there is a sub-total sheet with no adjacent base sheets? i.e. the next sheet is also a sub-total?the sub total will equal to 0, but the sub total sheet will still exist and need to exist

[DKenny]

The Questions keep coming...
1/ Can the user add additional base sheets?
2/ Can the user add additional sub-total sheets?

3/ If 1/=TRUE and 2/=TRUE above, how will you tell the difference?

[Comintern]

Just put your code in the Activate Event of the subtotal and total sheets. If the sales data doesn't change, there is no need to recalcuate unless a totaling sheet is activated. To move a sales sheet, the total or subtotal sheet would have to be de-activated because the moving sheet activates when you grab it's tab.

All you would need to do would be to recalculate when the sheets with changing values are activated.

[billhuard]

No they can't.

They only thing they can do is change number on sheet sales and move sheet sales, and by mistake they can move sheet total and subtotal but are not suppose to

[billhuard]

Just put your code in the Activate Event of the subtotal and total sheets. If the sales data doesn't change, there is no need to recalcuate unless a totaling sheet is activated. To move a sales sheet, the total or subtotal sheet would have to be de-activated because the moving sheet activates when you grab it's tab.

All you would need to do would be to recalculate when the sheets with changing values are activated.

It Works but there is still a problem, I've made it simple to be easy to understand, but sometime there is over 25 sheets per workbook with over 30 000 fields to recalculate. If the calculation start everytime a sub-total or total sheet is activated the user have to wait 30 seconds each time.

We are making progress

[billhuard]

Okay, no rush for tonight.

Beer bells just rang in Montreal.
Be back tomorrow morning 8:30 eastern time......

[DKenny]

mañana

[Comintern]

It Works but there is still a problem, I've made it simple to be easy to understand, but sometime there is over 25 sheets per workbook with over 30 000 fields to recalculate. If the calculation start everytime a sub-total or total sheet is activated the user have to wait 30 seconds each time.

We are making progress

All you would need to do would be to store the current sheet order someplace, and have the activate event check to make sure they were in the same order. Then you would only recalculate if it changed. Another (more hardcore) way to do it would be to load an invisible modeless form when the workbook opened that subclassed the Excel tabstrip and did the same thing any time it re-painted. You'd just have to make sure that you removed the form on the close event.

[DKenny]

All you would need to do would be to store the current sheet order someplace, and have the activate event check to make sure they were in the same order.

Exactly what I was thinking, except I used the Deactivate event.
The attached workbook shows this in action.
I added a "ControlsSheet" that contains the stored data for each sheet. For each sheet I store

1. Index
2. Name
3. Type (Grand, Sub, Base or Hidden)
4. Moved - a boolean flag where my proc records all shets that have moved

The deactivate event performs the following steps

1. Record any sheets that have moved.
2. If one of the sheets that moved was a sub-total I trap for that - this is where you would call your proc to recalc the subtotals. (you only need to trap for sub-total sheets moving because in your example if I transposed sheet3 and sheet4 the sub-total on sheet2 does not change.
3. Record the revised sheet positions.

Play around with the example and let me know if you need anything else.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim rngPosition As Range
Dim lSheetIndex As Long
Dim wksSheet As Worksheet
Dim bSubMove As Boolean
    
    'Range where sheet positions are stored
    Set rngPosition = Worksheets("ControlsSheet").Range("rngSHEET_REF")
    
    
    For lSheetIndex = 1 To ThisWorkbook.Sheets.Count
        
        'Has the current sheet been moved?
        If ThisWorkbook.Worksheets(lSheetIndex).Name <> rngPosition.Cells(lSheetIndex, 2) Then
            
            'Mark that sheet as moved
            rngPosition.Cells(lSheetIndex, 4).Value = True
            
            'Has a sub-total sheet moved?
            If rngPosition.Cells(lSheetIndex, 3).Value = "Sub" Then
                bSubMove = True
            End If
        End If
    Next lSheetIndex
    
    'Only recalc if a sub-total has moved
    If bSubMove Then
        MsgBox "A sub-total has changed"
        'rest of code here - you can use the "Moved column" to determine
        'which sub-totals need to be recalc'd
        '..
        '....
    End If
    
    'Correct Position Range
    With rngPosition
        .Columns(4).Value = False
        For lSheetIndex = 1 To ThisWorkbook.Worksheets.Count
            .Cells(lSheetIndex, 1).Value = Worksheets(.Cells(lSheetIndex, 2).Text).Index
        Next lSheetIndex
        .Sort Key1:=.Cells(1, 1)
    End With
End Sub

[billhuard]

Good Job Guys for your advice and thanks Declan for your code.

It performs exactly what I needed.

Thanks