Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

NewSheet doesn't handle copied sheet

  Asked By: Ashley    Date: Mar 03    Category: MS Office    Views: 4547
  

an event to respond to a worksheet (from a different
workbook) being copied into the current workbook.

Description: The user has my app open and his own workbook. He
copies a data worksheet into my app (i.e. Move or Copy, Move
Selected Sheet to Book:, Before Sheet:). Then in my app the proposed
Workbook_SheetFromOtherWorkbook event runs.

NewSheet events don't handle it: Workbook level (Workbook_NewSheet)
and application level (App_WorkbookNewSheet) events only handle
adding a new, blank worksheet (i.e. Insert, General, Worksheet).
These events don't respond to a worksheet being copied in from a
different workbook.

Unsuccessfully tried: Using class module clsAppEvent and module
modAppEvents (New clsAppEvent, InitializeApp) to create an
application level App_SheetActivate. The idea was that when a new
worksheet is copied in by the user, it is then activated by excel.
Then test the name of the newly copied in worksheet, and run the sub
if it passes. This became overly complicated because anytime any
sheet in the app was activated, the event ran. And the InitializeApp
(Set X.app = application) sub had to be called more often than
merely in Workbook_Open.

Thus a Workbook_SheetFromOtherWorkbook event seems to be lacking in
the Excel object model.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Hugo Williams     Answered On: Mar 03

Here is a possible solution, but I doubt anyone will
like it because it involves an infinite loop.

Add a VBA module  to your app  with the following code:

Option Explicit
Public SheetCount As Integer
Public CopiedSheet As Boolean

Sub Auto_Open()
SheetCount% = Sheets.Count
CopiedSheet = False
Do While True
If Sheets.Count > SheetCount% Then
If FindLastCell <> "ERROR" Then
CopiedSheet = True
MsgBox "Sheet copied  from " & _
"other workbook"
End If
End If
SheetCount% = Sheets.Count
DoEvents
Loop
End Sub

Public Function FindLastCell() As String
'Returns address of last cell used
'(highest row & col) on active sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With ActiveSheet
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, _
LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

Public Function FindLastCell() As String
'Returns address of last cell used (highest row & col)
on active sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With ActiveSheet
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&,
LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

In my tests, it always detected when a sheet  with data
was copied in from another workbook. It does not react
to inserting a new  blank worksheet  or copying in a
blank worksheet from another workbook. Hopefully, it
may be a starting point for you.

 
Answer #2    Answered By: Amelia Schmidt     Answered On: Mar 03

It worked great, and
your suggestion was interesting with a few techniques I wasn't aware of. But
after much thought I realized that it was getting way too complicated  for Excel,
so I am going to apply the "keep it simple stupid" principle and stay away from
over-automating the app  for the users.

 
Didn't find what you were looking for? Find more on NewSheet doesn't handle copied sheet Or get search suggestion and latest updates.




Tagged: