Disable moving sheets

[michael.beckinsale]

Hi All,

I wondered if there is a method to disable the moving of sheets other
than protecting the workbook structure.

Any code to achieve this would be gratefully appreciated

TIA

Michael B

 

[Guest]

Protecting the structure is the only direct method to control this. Using
code is simply defeated by disabling macros. In fact, if security is set to
high and the macro is not certified and accepted by the user, then the macros
would be disabled without the user even knowing it existed.

there is no trappable event directly related to moving a worksheet.

 

[Don]

Private Sub SetSheetOrder()
'Check Sheet Order
Application.ScreenUpdating = False
If Worksheets("Sheet2").Index = 1 Then
If Worksheets("Sheet1").Index = 2 Then
If Worksheets("Sheet3").Index = 3 Then Exit Sub
End If
End If

'Re-Order Sheets
Worksheets("Sheet2").Move before:=Sheets(1)
Worksheets("Sheet1").Move after:=Sheets(1)
Worksheets("Sheet3").Move after:=Sheets(2)
End Sub

You could add as many layers as you want. If you need to make sure
that they never change the sheets, run this sub in the
Worksheet_Deactivate() sub of each sheet. If you just need to
determine the sheet order before you run some of your own macros, you
only need the Re-Order Sheets portion of the sub and you don't need to
put it in each sheets Worksheet_Deactivate sub.

 

[michael.beckinsale]

Hi Don,

Many thanks for the code. It isn't quite what l want but l can adapt
the concept to give me what l want.

I wasn't aware of the .Index method

Regards

Michael B

 

[Don]

Hi Don,

Many thanks for the code. It isn't quite what l want but l can adapt
the concept to give me what l want.

I wasn't aware of the .Index method

Regards

Michael B

I never had an formal Excel training or education so there is probably
a better way... Enjoy!