Excel vba prevent dragging worksheet order

Question

is there a vba way to prevent rearranging of worksheets? For example: Sheet1, Sheet2, Sheet3 can't be drag to a new order of Sheet3, Sheet2, Sheet1.

For example: I have code Sheet1.cells(1, 1).value and by rearranging of sheets it changes object references. I need to disable this so the user doesn't inadvertently break code. Perhaps a failure in planning but none the less I'm stuck with it at this point until a rewrite is possible.

Would need a solution for 2003, 2007, 2010 and beyond if at all possible.

I do not see any topics or solutions on the net, in the object browser, or on stackoverflow so would appreciate your help/references you may have to offer.

Thanks.

Dragging a sheet to a different position doesn't change its codename, only its Index property. — Tim Williams
@AlexandreP.Levasseur Like I said it's not possible right now, I wish it were. I understand where you are coming from though. — BgreenDSI
Using sheet name is a good alternative to using the index. Set ws = Sheets("sheet_name") — Sam
@SamWard I need to be able to let the user change sheet name I would do it that way. Thanks. — BgreenDSI

BgreenDSI BgreenDSI · Accepted Answer · 2013-06-16T00:22:29

I see the issue with the code.

Main 1

Sub Main(sheetIndex as integer)

Dim ws as worksheet
Dim x as String

    Set ws = Sheets(sheetIndex)

    x = ws.Cells(1, 1).value

End sub

Changed to:

Main Sheet1

Sub Main(sheetIndex as Object)  

Dim x as String      

    x = sheetIndex.Cells(1, 1).value

End sub

Rearranging of worksheets doesn't break code now. Now I need to dig through the code and find more instances. Thanks Tim, Alexandre.

Excel vba prevent dragging worksheet order

1 Answers