filename problem in VB
 

Hi Everyone,

I'm having a problem when opening two excel files. one runs th
following code while closing. It is important for this code to run
however the part where is says WB.Select is my problem. I'm essentiall
trying to have two workbooks open, and close them at the same time, bu
select the workbook of interest and then run the code.

In simple terms:

Open Workbook(A), then open Workbook(B). have Workbook(B) selected an
then press the big X to close both workbooks, but then selec
Workbook(A) and run the following code.

I DO NOT want to use this book of code...
Workbooks("filename").Activate. because my users will change th
filename yearly.

ANY help would be truly appreciated.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This will delete all charts produces
Dim CH As Chart
Dim NumWorkSheets
NumWorkSheets = Worksheets.Count - 4
Application.DisplayAlerts = False
Dim WB As Workbook
Set WB = ActiveWorkbook
WB.Select 'Activate
Sheets("Welcome Screen").Select
For Each CH In ThisWorkbook.Charts
CH.Delete
Next CH
Application.DisplayAlerts = True
'cycles through the workbook and hides certain areas.
For i = 2 To NumWorkSheets
ThisWorkbook.Sheets(i).Select
Rows("265:290").Select
Selection.EntireRow.Hidden = True
Next
Sheets(1).Select
End Su

--
BrownTin
-----------------------------------------------------------------------
BrownTing's Profile: http://www.excelforum.com/member.php...fo&userid=3491
View this thread: http://www.excelforum.com/showthread.php?threadid=56436

filename problem in VB
 

Your code is confusing. For example, this does nothing:
WB.Select 'Activate
as the ActiveWorkbook is by definition activated (and you cannot .Select a
workbook). WB is never used again so serves no purpose.

You need some mean to know which WB is which, otherwise will (probably) end
up changing the wrong WB.
Depending how you open these files, you could:
Dim WB_A As Workbook
Dim FileName as String
FileName=Application.GetOpenFileName()
Set WB_A=workbooks.open(FileName)

Then use WB_A in you code to refer this WB.

Or loop through the Workbooks collection, checking the .Caption to find the
one(s) you need.


With ThisWorkbook
Application.DisplayAlerts = False
For Each CH In .Charts
CH.Delete
Next CH
Application.DisplayAlerts = True

'cycles through the workbook and hides certain areas.
For i = 2 To .Worksheets.Count
.Worksheets(i).Rows("265:290").EntireRow.Hidden = True
Next
.Worksheets(1).Select
End With

NickHK

"BrownTing" wrote
in message ...

Hi Everyone,

I'm having a problem when opening two excel files. one runs the
following code while closing. It is important for this code to run,
however the part where is says WB.Select is my problem. I'm essentially
trying to have two workbooks open, and close them at the same time, but
select the workbook of interest and then run the code.

In simple terms:

Open Workbook(A), then open Workbook(B). have Workbook(B) selected and
then press the big X to close both workbooks, but then select
Workbook(A) and run the following code.

I DO NOT want to use this book of code...
Workbooks("filename").Activate. because my users will change the
filename yearly.

ANY help would be truly appreciated.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This will delete all charts produces
Dim CH As Chart
Dim NumWorkSheets
NumWorkSheets = Worksheets.Count - 4
Application.DisplayAlerts = False
Dim WB As Workbook
Set WB = ActiveWorkbook
WB.Select 'Activate
Sheets("Welcome Screen").Select
For Each CH In ThisWorkbook.Charts
CH.Delete
Next CH
Application.DisplayAlerts = True
'cycles through the workbook and hides certain areas.
For i = 2 To NumWorkSheets
ThisWorkbook.Sheets(i).Select
Rows("265:290").Select
Selection.EntireRow.Hidden = True
Next
Sheets(1).Select
End Sub


--
BrownTing
------------------------------------------------------------------------
BrownTing's Profile:
http://www.excelforum.com/member.php...o&userid=34919
View this thread: http://www.excelforum.com/showthread...hreadid=564362