View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
BrownTing[_6_] BrownTing[_6_] is offline
external usenet poster
 
Posts: 1
Default 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