VBA Generated Spider/Radar Plot Not Displaying After Macro Run

toiner

New Member
Joined
Oct 5, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
I have a task to update an Excel tool we use at my job to update/improve functionality. Unfortunately I am building on some already existing VBA code which has made things slightly trickier. The functionality I was trying to add was a simple macro (button press) to create a spider/radar plot of the data along with some extra layers which represent points of progression through a project. I've managed to get all of the functionality working that I wanted but the issue I'm having is that the macro runs and creates the chart, all the data is selected correctly, but the image doesn't show until I save the spreadsheet, close and open again. I've attached my code and a couple of screenshots to try to explain it clearer.
Spider.jpg

Apologies in advance for the state of my coding, as I said I'm not that experienced and have pieced it together from various learnings on the internet.

Code:
Sub Spider()



'''''''''''''''''''''''''''''''''''''''''''''''

'Delete Spider Chart Sheets That Already Exist'

'''''''''''''''''''''''''''''''''''''''''''''''



Dim mySheetName As String

mySheetName = "Spider Chart"

Application.DisplayAlerts = False

On Error Resume Next

ThisWorkbook.Sheets(mySheetName).Delete

On Error GoTo 0

Application.DisplayAlerts = True



'''''''''''''''''''''''''''

'Check How Many Rows Exist'

'''''''''''''''''''''''''''



Dim LastRow As Integer

   LastRow = 10                   

   While Sheets("TRLs").Cells(LastRow, 1) <> ""   

       LastRow = LastRow + 1

   Wend

   LastRow = LastRow - 1



''''''''''''''''''''''''''''''''''''''

'Set Up Constant Value Arrays To Plot'

''''''''''''''''''''''''''''''''''''''



Dim arr_length As Integer

arr_length = LastRow - 10

Dim Concept() As Integer

ReDim Concept(arr_length)

Dim Ass_Phase() As Integer

ReDim Ass_Phase(arr_length)

Dim Dev_Phase() As Integer

ReDim Dev_Phase(arr_length)

Dim Risk_Red() As Integer

ReDim Risk_Red(arr_length)

Dim Qualif() As Integer

ReDim Qualif(arr_length)

Dim Production() As Integer

ReDim Production(arr_length)

Dim Filled() As Integer

ReDim Filled(arr_length)

    Dim intI As Integer

    For intI = 0 To arr_length

    Concept(intI) = 1

    Ass_Phase(intI) = 3

    Dev_Phase(intI) = 4

    Risk_Red(intI) = 5

    Qualif(intI) = 6

    Production(intI) = 7

    Filled(intI) = 9

    Next intI

    

'''''''''''''''''''''''''''''''''''''

'Set Up New Chart Sheet and Populate'

'''''''''''''''''''''''''''''''''''''

    

Dim ChartSheet_Spider As Chart







Set ChartSheet_Spider = Charts.Add

With ChartSheet_Spider

    .ChartType = xlRadarFilled

    .HasTitle = True



    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

            

    With .SeriesCollection(1)

        .XValues = "='TRLs'!B10:B" & LastRow ' New Titles Range"

        .Values = Filled

        .Name = "Full TRL"

    End With

    

    With .SeriesCollection(2)

        .XValues = "='TRLs'!B10:B" & LastRow ' New Titles Range"

        .Values = Production

        .Name = "Production Phase"

    End With

        

    With .SeriesCollection(3)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Qualif

        .Name = "Qualification Phase"

    End With

    

    With .SeriesCollection(4)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Risk_Red

        .Name = "Risk Reduction Phase"

    End With

    

    .SeriesCollection.NewSeries

    With .SeriesCollection(5)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Dev_Phase

        .Name = "Development Phase"

    End With

    

    With .SeriesCollection(6)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Dev_Phase

        .Name = "Development Phase"

    End With

    

    With .SeriesCollection(7)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Ass_Phase

        .Name = "Assessment Phase"

    End With

    

    With .SeriesCollection(8)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Concept

        .Name = "Concept Design Phase"

    End With

    

    With .SeriesCollection(9)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = "='TRLs'!C10:C" & LastRow ' "

        .Name = "Current Levels"

        .Format.Fill.Solid

        .Format.Fill.Transparency = 0.2

    End With

    

    .ChartTitle.Text = Sheets("DMA Syst Eval").Range("A4") & " TRL"

    .Name = "Spider Chart"

    

End With



Worksheets("TRLs").Activate



End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,122,977
Messages
5,599,151
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top