Resetting variable at every workbook open

Question

I've got a macro that draws a chart from given data. There's possibility that in the same workbook there will be made several more charts. I'd like to set chart to a certain place and make it certain size by using the same macro. The thing is, only thing I came up to to make this happen had this line:

ActiveSheet.Shapes("Chart 1").<whatever option goes here>

(or another number if it's next chart)

I'd like make it like this:

ActiveSheet.Shapes("Chart " & x).<option>

The only thing I can't figure out is how to make that x reset at every time I open the workbook to make it work properly. I kinda know it's related to workbook_open() and probably Public x As Integer but I have no idea how to put it together. I've tried some combinations of it but didn't work.

My whole code:

Sub import()


Fname = Application.GetOpenFilename

If Fname = False Then Exit Sub

Sheets("Arkusz2").Select
Columns("A:F").Select
Selection.ClearContents
Selection.ColumnWidth = 8.43

Dim wks As Worksheet

For Each wks In Worksheets
    If wks.ChartObjects.Count > 0 Then
        wks.ChartObjects.Delete
    End If
Next wks





Workbooks.OpenText Filename:= _
    Fname, Origin:=437 _
    , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
    TrailingMinusNumbers:=True, DecimalSeparator:=".", ThousandsSeparator:=","


Range("B1").Value = DateValue(Range("B1").Value)

Range("B4, B5, B6, B8").Activate
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Range("B21:C100").Select
    Selection.NumberFormat = "0.000E+00"

Range("B2").Select
Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"




Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit


ark = ActiveWorkbook.Name

Columns("A:E").Select
Selection.Copy
Windows("import danych.xlsm").Activate
Sheets("Arkusz2").Select
ActiveSheet.Paste
Range("A1").Select

Windows(ark).Activate
Application.CutCopyMode = False
ActiveWindow.Close savechanges = True
Windows("import danych.xlsm").Activate
Application.CutCopyMode = False

Run "graph"




End Sub

Sub graph()



target = Range("B11").Value
dat = Range("B1").Value

Dim tim As Date
tim = Range("b2").Value
typ = Range("B7").Value

Cells.Find(What:="Ref signaal [A]", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate


ActiveCell.Offset(1, 0).Select

c = ActiveCell.Row - 1

d = ActiveCell.Value

Do Until d = ""

c = c + 1

ActiveCell.Offset(1, 0).Select
d = ActiveCell.Value

Loop

Cells.Find(What:="Ref signaal [A]", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 2).Select

r1 = ActiveCell.Address

ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "Target"

ActiveCell.Offset(1, 0).Select
r2 = ActiveCell.Address

ActiveCell.FormulaR1C1 = target
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(r2 & ":F" & c)


Range(r1 & ":F" & c).Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Arkusz2!$E$21:$F$" & c)

ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Line
    .ForeColor.RGB = RGB(255, 0, 0)
End With

ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlLineMarkers

ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = dat & " " & tim & " " & typ

Range("A1").Select
End Sub

A good start would be not to use ActiveSheet. What if user opens the workbook in a different sheet? Well I'll tell you what happens, it will throw a reference error because there's (likely) no Chart1 in that sheet — Samuel Hulla
Well I've could precised what exactly my code does...It imports data from .txt file to the Sheet2, sorts data in columns and rows and draws chart from a certain set of data. So that thing with ActiveSheet is not that inappropriate IMO. — Dave
I still stand by the fact it's a bad coding habit. AcitveSheet can often result in unexpected behaviour. Just create a Worksheet object variable and reference a specific worksheet instead. It's even shorter to write ws1.Range then ActiveSheet.Range — Samuel Hulla
As @Rawrplus says, you are setting yourself up for bug prone code. More of your code might help. You can loop worksheets in worksheets collection, and charts within a chart collection (if they already exist) ; you can then work with the current worksheet variable for example For ws in ThisWorkbook.Worksheets: For each cht in ws.Charts............ — QHarr
Makes sense to me. So: dim ws1 as worksheet? And what's next? Can you navigate me how to move and change size of chart? — Dave

Samuel Hulla Samuel Hulla · Accepted Answer · 2018-08-16T09:30:31

Perhaps the question you should be asking yourself, instead of selecting (not in the literal Select sense) individual specific charts indexed in array-like order, you should simply loop through all the charts instead and not worry about indexing at all!

There are two types of charts

The Chart sheets
The charts embedded to individual worksheets.

I presume you want to deal with the second option here, as there can be multiple charts as indiciated by what you described with ChartX in the question.

Now with theory out of the way, let's get down to the nitty-gritty!

Private Sub loop_through_charts(where as Worksheet)
   If where.ChartObjects.Count > 0 Then 'if the sheet has Charts
       Dim cht as ChartObject
       For Each cht in where.ChartObjects
            ' do something
       Next cht
   End If
End Sub

The elegance of this is, we can pass an individual Worksheet to the procedure, eg.

 Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
 loop_through_charts(ws2)

Or even apply this procedure to every sheet

Dim ws as Worksheet
For Each ws in Thisworkbook.Worksheets
     loop_through_charts(ws)
Next ws

EDIT:

So... upon more info in comments, simply add the following code to the bottom of your import procedure (once the Chart is drawn)

presumably you have variable ws referencing the Worksheet from import button

Dim newestChart As Integer
newestChart = ws.ChartObjects.Count

With ws.ChartObjects(newestChart)
   ' do something
End With

Resetting variable at every workbook open

1 Answers