creating a sparkline using VBA

[Lifeseeker]

Hi there,

I'm trying to create a sparkline using VBA(just testing it out for fun)

Private Sub SparklineTest_Click()
Dim LINE As SparklineGroup

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

set LINE = WSL.range("F2:G2").SparklineGroups.add(Type:xlsparkline, sounrcedata:="Sheet1"!A2" & lastrow


End Sub

I keep getting stuck at the word "Type" in the codeline. Error is on "Expression"

Has anybody had any experience using sparkline in VBA? The code comes from some website, but I have tried to modify it to suit my needs.

Any ideas?

[StephenR]

You need a = after the "Type:", but I have no idea about sparklines!

[OnErrorGoto0]

set LINE = WSL.range("F2:G2").SparklineGroups.add(Type:=xlsparkline, sourcedata:="Sheet1!A2:A" & lastrow

should do it.

[Lifeseeker]

set LINE = WSL.range("F2:G2").SparklineGroups.add(Type:=xlsparkline, sourcedata:="Sheet1!A2:A" & lastrow

should do it.

I'm getting "object required" error

Dim LINE As SparklineGroup

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set LINE = WSL.Range("F2:G2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)


End Sub

Any ideas?

[OnErrorGoto0]

What is WSL? I assumed it was the codename of your worksheet.

[Lifeseeker]

What is WSL? I assumed it was the codename of your worksheet.

Oops, I think it's supposed to be the name of the worksheet.

Anyhow, I'm getting a different error now

"object variable or With block variable not set.

Any ideas?

Private Sub SparklineTest_Click()
Dim LINE As SparklineGroup
Dim ws As Worksheet

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set LINE = ws.Range("F2:G2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)


End Sub

[OnErrorGoto0]

Same problem - you have not assigned anything to ws. You need a statement like

set ws = sheets("sheet1")

for example.

[Lifeseeker]

still no luck with it hmm.

Private Sub SparklineTest_Click()
Dim LINE As SparklineGroup
Dim ws As Worksheet
Set ws = Sheets("Sheet1")

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set LINE = ws.Range("F2:H2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)

End Sub

I wonder if the LINE setup is even correct. I'm new to the concept of doing a sparkline in VBA, but I just wanted to try it out using VBA(so that we don't have to use formula in the formula bar)

Any ideas?

[OnErrorGoto0]

"No luck" means what exactly? (I am on a phone so cannot review your file at present)

[OnErrorGoto0]

Set LINE = Range("F2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)

The target range made no sense given your data set.

[Lifeseeker]

Private Sub SparklineTest_Click()
Dim LINE As SparklineGroup
Dim ws As Worksheet
Set ws = Sheets("Sheet1")

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Set LINE = ws.Range("F2:H2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)

End Sub

I am not exactly positive on how the ranges work, but I guessed my around and thought if I put F2: H2 in there, it would mean to have it display the sparkline from F2 to H2?

[OnErrorGoto0]

Sparklines appear in one cell each. If you want something larger, use a chart.
I am not sure why you posted the same workbook again.

[Lifeseeker]

It appears to be working.

Private Sub SparklineTest_Click()
Dim LINE As SparklineGroup
Dim ws As Worksheet
Set ws = Sheets("Sheet1")

Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set LINE = ws.Range("F2").SparklineGroups.Add(Type:=xlSparkLine, SourceData:="Sheet1!A2:A" & lastrow)

End Sub

When you said initially, the range doesn't make sense, which part of the code were your referring to?

[OnErrorGoto0]

I was referring to your use of F2:H2 - which is why I changed it.

[MARKJPAWLIK]

I'd greatly appreciate any help ....


this macro is meant to create a spark line for the selected range and put the results in the (row-2) range

[result on this row.......]
percent 11-Q1 11-Q2 11-Q3
a.Zero 1% 2% 3%
b.1-2 2% 3% 4%
c.3-5 1% 2% 3%
d.6-10 2% 3% 4%
e.>10 1% 1% 1%

I would select 1%...3% and the next 5 rows
I would want to put the sparklines in [result on this row.......]

thanks

Mark

Sub ADD_SPARK_FOR_SELECTION()
 
'
Dim LINE As SparklineGroup
Dim ws As Worksheet
Dim rng As Range
Dim Rng2 As Range
  
 

Set ws = current.Sheet      ' KEEP GETTING OBJECT REQUIRED ERROR
 
 strow = Selection.Row
 ROWCT = Selection.Rows.Count
 stcol = Selection.Column
 colct = Selection.Columns.Count
 addr = Selection.Address
 
 
' IT IS MEANT TO USE THE SELECTION ON THE SHEET AND PUT RESULTS IN TWO ROWS BEFORE
 
 Set LINE = ws.Range(Cells(strow - 2, stcol), Cells(strow - 2, stcol + colct - 1)).SparklineGroups.Add(Type:=xlSparkLine, SourceData:=rng)
        
    
    Selection.SparklineGroups.Item(1).SeriesColor.ThemeColor = 5
    Selection.SparklineGroups.Item(1).SeriesColor.TintAndShade = -0.499984740745262
    Selection.SparklineGroups.Item(1).Points.Negative.Color.ThemeColor = 6
    Selection.SparklineGroups.Item(1).Points.Negative.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Markers.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Markers.Color.TintAndShade = -0.499984740745262
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.TintAndShade = 0.399975585192419
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.ThemeColor = 5
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.TintAndShade = 0.399975585192419
End Sub

[arlu1201]

MarkJPawlik,

Welcome to the forum. 2 pointers -

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Also you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.