Chart sheet in Excel not working when second sheet open with all cells selected

Question

I have a weird situation with a chart sheet in excel vba. I normally have a Sheet with data and a button with a macro that creates a chart sheet from that data. Like this:

Set ChartSheet1 = Charts.Add(After:=Sheets(Sheets.Count))

But some users have some code in the excel and it opens with two sheets, one of which has all of the cells selected, both of them empty( second sheet with all cells selected).The data for the chart is on the first sheet and I use nothing from the second sheet (which is empty anyways).

In this situation when I want to create the chart nothing happens and the focus changes to the 2nd sheet. If I deselect the cells then the chart works. Also if a random range of cells is selected, the chart works fine. It's only when everything's selected.

Do you have any clue as to why this happens? And is there a solution besides deselecting everything before running the chart creation?

Edited to add code:

Sub chartSheet()



Dim wb As Workbook
Set wb = ActiveWorkbook

Dim ws As Worksheet
Set ws = wb.ActiveSheet

If ws Is Nothing Then
    MsgBox ""
    Exit Sub
End If

Set ChartSheet1 = Charts.Add(After:=Sheets(Sheets.Count))

With ChartSheet1
          .SetSourceData Source:=ws.Range(namedRange)
          .FullSeriesCollection(1).XValues = ws.Range(otherNamedRange)
          .FullSeriesCollection(1).Name = ws.Range(namedRange).Item(1)


        While (condition) 

                     .SeriesCollection.NewSeries
                     .FullSeriesCollection(X).Values = ws.Range(namedRange)
                     .FullSeriesCollection(X).XValues = ws.Range(otherNamedRange)
                     .FullSeriesCollection(X).Name = ws.Range(namedRange).Item(1)
                X = X + 1

        wend
  end sub

Please show all the relevant code. The issue is not in the line of code you show. Do you have .Select or Selection statements in your code? — Pᴇʜ
When you create a chart it typically tries to use any already-selected data. If the whole sheet is selected then that might cause a problem. Making sure the whole sheet is not selected would be a good solution. — Tim Williams
@Pᴇʜ Thx for taking the time. The code is more complex, I tried to simplify the situation. I use named ranges to build the chart, I don't use selected ranges. I don't use any selection or .Selects, only "direct" objects. I'll try to shift through the code to find some relevant pieces. — Anastasia V
@AnastasiaV Well, then it looks like what Tim said. Put something like ActiveSheet.Range("A1").Select before you start adding the chart sheet. So the entire cells selection disappears. — Pᴇʜ
@Pᴇʜ Thx for the answers. I was hoping to find a solution without having to deselect the ranges as the users might need them. Any other range selected in this second sheet is no problem, only when everything is selected :/ — Anastasia V

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-05-15T08:45:13

As a workaround do something like that:

Dim OldSelection As Range
Set OldSelection = Selection

ActiveSheet.Range("A1").Select 'de-select old selection

'run your code here …

OldSelection.Select 're-select it

So in the end the same cells are selected as before your code was executed.

Chart sheet in Excel not working when second sheet open with all cells selected

1 Answers