Just like Tom says, the "Set wbk = Workbooks.Open...." instruction is what
opens Book2.
Note that the code below is a direct copy & paste of a fully working sample
that I wrote just for this post to demonstrate the problem. If anyone wants
to try it out then create two workbooks called Book1.xls and Book2.xls then
copy all code between the dashed lines and paste it in the ThisWorkbook
module of the two workbooks. You can save the workbooks anywhere you want as
long as both workbooks are in the same directory.
Regards,
Anders
"Tom Ogilvy" wrote:
> Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
>
> would seem to indicate what wbk refers to.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:C8AB684B-6D3A-4760-BC51-(E-Mail Removed)...
> > Maybe I'm missing something, but I don't see where you tell it to open
> > workbook2. You have wbk.Activate but it does not specify which one, so
> > the
> > logic will tell it to activate ThisWorkbook. Maybe you didn't post the
> > complete code and I'm reading it wrong.
> >
> > "Anders" wrote:
> >
> >> Hi,
> >>
> >> I have two workbooks, Book1 and Book2, both with macros (see bleow).
> >>
> >> Here's my problem: If I open only Book2 in Excel and click the save
> >> button
> >> or choose Save / Save As on the file menu, then the BeforeSave event
> >> fires
> >> and runs fine, just like expected. So far so good.
> >>
> >> When I open only Book1 and run the code in the dummy() sub, then when the
> >> ActiveWorkbook.SaveAs instruction (Ref 1) executes, the code execution
> >> continues in Book2 and the BeforeSave event fires, just like expected,
> >> and
> >> all the code in the event handler does runs (I can single step from line
> >> to
> >> line with F8) BUT none of the instructions do anything; the switch
> >> between
> >> sheet 1 and 2 doesn't happen and the unprotection of sheet 1 doesn't
> >> happen
> >> and when the "timestamp" is being written to cell A1 (Ref 2), I get
> >> run-time
> >> error saying the worksheet is protected.
> >>
> >>
> >> Any ideas why the code in BeforeSave doesn't work OK when the BeforeSave
> >> event is fired by the SaveAs instruction?. Same problem if I replace
> >> SaveAs
> >> with Save.
> >>
> >> I DO want the code in BeforeSave to run also when the SaveAs instruction
> >> executes.
> >>
> >> I'm using Excel2003 on Win2k and WinXP.
> >>
> >>
> >>
> >> Book1 has one worksheet, B1S1, which is password protected
> >>
> >> Book2 has two worksheets B2S1 and B2S2.
> >>
> >> The code in Book1 (located in ThisWorkbook) is:
> >>
> >> '------------- Begin Copy - Book1.xls ------------------------------------------
> >> Option Explicit
> >>
> >> Sub dummy()
> >> Dim wbk As Workbook
> >>
> >> On Error GoTo errHandler
> >>
> >> ' Set wbk to refere to the opened Book2 workbook
> >> Set wbk = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
> >>
> >> ' Switch back to this workbook and do some stuff...
> >> ThisWorkbook.Activate
> >> Range("A1").Value = Range("A1").Value + 1
> >>
> >> ' Switch to the opened workbook, Book2
> >> wbk.Activate
> >>
> >> ' Disable "Overwrite Y / N / C?" question
> >> Application.DisplayAlerts = False
> >>
> >> ' Save Book2
> >> ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Book2.xls" '
> >> (Ref
> >> 1)
> >>
> >> ' Close Book2
> >> wbk.Close
> >>
> >> ' Enable alerts
> >> Application.DisplayAlerts = True
> >>
> >> Exit Sub
> >>
> >> errHandler:
> >> Debug.Print Err.Number
> >> Debug.Print Err.Description
> >>
> >> End Sub
> >> '------------- End Copy - Book1.xls --------------------------------------------
> >>
> >>
> >> The code in Book2 (located in ThisWorkbook) is:
> >>
> >> '------------- Begin Copy - Book2.xls ------------------------------------------
> >> Option Explicit
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> >> Boolean)
> >> ' Switch to sheet 2
> >> B2S2.Activate
> >> '
> >> ' Some more code here....
> >> ' Do some stuff with sheet 2
> >> '
> >>
> >> ' Switch back to sheet 1
> >> B2S1.Activate
> >>
> >> ' Unprotect sheet 1
> >> B2S1.Unprotect "abc"
> >>
> >> ' Add timestamp to cell A1
> >> Range("A1").Value = Str(Date) & " " & Str(Time) ' (Ref 2)
> >>
> >> ' Protect sheet 1
> >> B2S1.Protect "abc"
> >> End Sub
> >> '------------- End Copy - Book2.xls --------------------------------------------
> >>
> >>
> >> Regards,
> >> Anders
>
>
>
|
Similar Threads
