HammerJoe,
You could add code to the new workbook at runtime. See
http://www.cpearson.com/excel/vbe.aspx. I don't think I'd do it that way.
You could also just create an Excel template with the code in it and open it
like this:
Workbooks.Open (ThisWorkbook.Path & "\MyTemplate.xlt")
Of course then you'd be sending out a workbook with macros in it, but I
guess you could consult Chip's page above for how to remove it.
Here's one more possibility - in the code for your main workbook make a
class that "controls" the workbook. First, insert a class to your workbook
in the VBE. In the code module for that new class paste this code:
Option Explicit
Private WithEvents wbNewBook As Workbook
Private m_WB As Workbook
Private Sub Class_Terminate()
Set m_WB = Nothing
Set wbNewBook = Nothing
End Sub
Public Property Set Wb(PassedWb As Workbook)
Set m_WB = PassedWb
Set wbNewBook = m_WB
End Property
Public Property Get Wb() As Workbook
Set Wb = m_WB
End Property
Private Sub wbNewBook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
m_WB.Close savechanges:=False
Workbooks("test.xls").Activate
Call ContinueOnOurMerryWay
Application.EnableEvents = True
Cancel = True
End Sub
Private Sub wbNewBook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
MsgBox "Sorry, changes not allowed"
Cancel = True
End Sub
Private Sub wbNewBook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'this is not necessary for your purposes
'but shows how this class captures all
'events in your new book, just fyi
MsgBox "selection change"
End Sub
Then add a regular module, which uses Class1. (You can rename the class
from the default of "Class1" to whatever you want.)
Option Explicit
Public NewBook As Class1
Sub StartTheBallRolling()
ThisWorkbook.Sheets(1).Copy
Set NewBook = New Class1
Set NewBook.Wb = ActiveWorkbook
End Sub
Sub ContinueOnOurMerryWay()
Set NewBook.Wb = Nothing
Set NewBook = Nothing
MsgBox "Continuing"
End Sub
My understanding is that you never want the user to save the workbook. If
that's not true then the above is wrong. If it's true, and you decide to go
the template route above, you can use the BeforeClose and BeforeSave code
above. Just change "m_WB.Close" to "ThisWorkBook.Close"
hth,
Doug
<(E-Mail Removed)> wrote in message
news:380355ab-1a0d-486c-ba3a-(E-Mail Removed)...
> Hi,
>
> I have a macro in my main workbook that creates a new workbook and it
> copies from main workbook a report sheet and pastes only formats and
> values to it so it can be emailed.
>
> Because people can send it as an attachment, I decided to create a new
> workbook with just one sheet. It makes the file smaller and at the
> same time it removes the formulas from prying eyes.
>
> The problem is I can't use the sendmail function, because it requires
> saving the new workbook to the disk (as far as I know) and because of
> profile space limitations this is not workable for me).
>
> The other problem, when the workbook is created a button is created to
> allow the user to close the workbook and return to the main workbook.
> That works fine except when the email recipient opens the file.
>
> there is a prompt about broken links (which is obviously because of
> the button).
> I understand that it is not possible to avoid that prompt with excel
> 2003
>
> So I decided to get rid of the button and just wait for the new
> workbook to be closed before returning to the main workbook and
> continuing with the macro
> Another problem then, excel of course prompts to save the new
> workbook.
>
>
> Can I eliminate the Broken/Update link prompt when opening the
> workbook?
>
> OR
>
> How can I close the workbook without prompt??
> (i am not sre if I can inject code from a macro into the new workbook
> to detect close event??)
>
>
>
>
>
>
Similar Threads
