Have VBA only work on certain workbook

Question

I have the following VBA code, and when I have the workbook with this open but I go on a different workbook it comes up with an error saying "subscript out of bounds". I have added the line specifing the workbook thinking this would stop it, but it doesn't seem to be solving anything. What alterations need to be made to avoid the error?

Option Explict Private Sub Worksheet_Calculate() Dim cht As Chart Dim wks As Worksheet Dim wb As Workbook Set wb = Workbooks("The wookbook I want it to work in") Set wks = Sheets("The sheet I want it to work in") Set cht = wks.ChartObjects("Chart 1").Chart...

Patrick Honorez · Accepted Answer · 2018-01-29 16:22:53Z

1

I think you want the macro to work with the active workbook:

Set wb = ActiveWorkbook Set wks = wb.Activesheet for each cht in wks.ChartObjects .... next cht

answered Jan 29, 2018 at 16:22

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beavis Over a year ago

Hi Patrick, this didn't quite work as it isn't the active workbook, but by doing Set wks = wb.Worksheets("the sheet") it did work.

Collectives™ on Stack Overflow

Have VBA only work on certain workbook

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related