When I enter a new worksheet into a workbook (Alt+i+w), Excel always puts it to the left of the active worksheet. Is there an easy way to make it insert on the right? That seems to be where I always want it. Right now I just drag it over, which isn’t too much of a pain. But just for fun, I thought I’d write a macro to move it for me.
I added a class module named CAppEvent to Personal.xls with this code:
Public WithEvents myApp As Application
Private Sub myApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
Sh.Move , Wb.Sheets(Sh.Index + 1)
End Sub
Private Sub myApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
Sh.Move , Wb.Sheets(Sh.Index + 1)
End Sub
And added a variable to a standard module:
Public gclsApp As CAppEvent
And added this code to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set gclsApp = Nothing
End Sub
Private Sub Workbook_Open()
Set gclsApp = New CAppEvent
Set gclsApp.myApp = Application
End Sub
Set gclsApp = Nothing
End Sub
Private Sub Workbook_Open()
Set gclsApp = New CAppEvent
Set gclsApp.myApp = Application
End Sub
Is there some way that event code can fail?
Update: See Part II
I just tried it out and it’s a great idea! (I always forget how to set-up a class module to respond to events!)
The only way I can see it ‘failing’ is when your ‘personal.xls’ workbook is reset – the global variable (gclsApp) is lost. When I made changes which reset the project, I just clicked in the Workbook_Open event and hit F5.
Also I’m not sure on exactly what changes do and don’t trigger a reset of VBA projects… any help?
Hi Dick…
The below code works fine…:-)
Sub AddSheetToRight()
‘ KeyBoard Shortcut Ctrl+Shift+i
ActiveWorkbook.Sheets.Add After:=Worksheets(ActiveSheet.Index)
End Sub
Rick: I don’t know all the things that reset the project, but I’d like to.
I like this idea! I always want it to the right of my activesheet as well (I use shift-F11 to add it), and have always just moved it.
As Rick said, the project could reset (step through a proc, even just one line, then click the end button; design mode could be also be toggled) and you could lose the object variable. To get around that, I will often put application-level events into a separate add-in, which would stay small (quick load) and usually won’t get touched (also won’t conflict with any other code in personal.xls). You could also put something like this into some other events to ensure the object is working: “if gclsapp is nothing then set gclsapp=new cappevent”, though that kind of line works best if you set ‘myApp’ in the Class_Initialize event.
Also, not that it really makes much of a difference, but you don’t need the public variable in a standard module. Putting “Dim gclsApp As CAppEvent” in ThisWorkbook will work just as well.
Hi Dick,
I don’t think it would fail, as the “Sheets” collection will deal equally well with Chart sheets, Macro pages, etc… One thing that can scramble it a little bit is if you insert a file that has multiple pages. (i.e. Insert a multi-sheet template into your workbook.) It doesn’t move all pages to the end, but rather shuffles the order a little. (I can’t think that many people would do this though.)
Fwiw, I would agree with Matt that the best placement of this kind of thing is in a separate add-in, rather than in the personal.xls. It then doesn’t get tocuhed, and doesn’t accidentally collapse on you when you start tinkering with other code in the Personal.xls file. :)
… there goes Ken with his Addins ;-)
This is an example of “poor” MS design – this type of thing is ok in Excel 5, but by 2000 it should be putting it to the right – God knows why anyone would design it to put it to the left – maybe if your regional settings where right to left text?
Having said that I’m a mouse jockey, so once i click insert, i rename and drag it across – true though on a big wkb it can be a pain.
Keeping in mind that I think this is overkill…and one of the reasons it is overkill is the saving in time, which over an entire year might add up to a few seconds, will be more than ‘undone’ by writing, debugging, and maintaining the code…
This will fail in 2007 if you click the newly added ‘tab’ (or should it be called a button?) that is located to the right of all the sheet tabs. The new tab/button creates a new worksheet as the last sheet in the workbook. Similarly, the code will fail if one were to programmatically add a new sheet at the end of a workbook.
So, one should modify the code to be
Private Sub myApp_WorkbookNewSheet(ByVal Wb As Workbook, _
ByVal Sh As Object)
With Wb
If Sh.Index
Dunno why the VB tags did’nt work. Here’s the same post with the tags in upper case.
Keeping in mind that I think this is overkill…and one of the reasons it is overkill is the saving in time, which over an entire year might add up to a few seconds, will be more than ‘undone’ by writing, debugging, and maintaining the code…
This will fail in 2007 if you click the newly added ‘tab’ (or should it be called a button?) that is located to the right of all the sheet tabs. The new tab/button creates a new worksheet as the last sheet in the workbook. Similarly, the code will fail if one were to programmatically add a new sheet at the end of a workbook.
So, one should modify the code to be
Private Sub myApp_WorkbookNewSheet(ByVal Wb As Workbook, _
ByVal Sh As Object)
With Wb
If Sh.Index