CODEWITHSUNDEEP
vbaexcelchartsactivex
Mexxer
Mexxer

Reputation: 1777

VBA in Excel: Creating a chart from ActiveX Textboxes

I'm trying to create a chart from 5 ActiveX Textboxes which I put in my Worksheet. First the user types in the values and then presses a button which generates the chart.

This is my approach but it doesn't work. It says something like "Object necessary"

Private Sub CommandButton1_Click()

Dim arKurse(4) As Double

arKurse(0) = Conver.ToDouble(TextBox1.Text)
arKurse(1) = Conver.ToDouble(TextBox2.Text)
arKurse(2) = Conver.ToDouble(TextBox3.Text)
arKurse(3) = Conver.ToDouble(TextBox4.Text)
arKurse(4) = Conver.ToDouble(TextBox5.Text)


Dim oChtObj As ChartObject
Set oChtObj = ActiveSheet.ChartObjects.Add(Left:=445, Width:=385, Top:=10, Height:=245)

With oChtObj.Chart
    .SeriesCollection.NewSeries
    .SeriesCollection.Values = arKurse
    .SeriesCollection.XValues = Array("1", "2", "3", "4", "5")

    .ChartType = xlLine
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Caption = "Chart"

End With

End Sub

Upvotes: 1

Views: 1036

Answers (1)

user857521
user857521

Reputation:

There are a few things that don't work

  1. Conver.ToDouble should be CDbl unless you have a good reason to use a custom method?
  2. TextBox.Text should be Textbox.value on a userform and also best to qualify the location of the textbox with it's parent. (in this case Userform1 but could be Sheet1 if textbox is on worksheet)
  3. Need to define which seriescollection

The below is updated to work hopefully

Option Explicit

Sub SetupUserformToTest()
UserForm1.Show
UserForm1.TextBox1.Value = 1
UserForm1.TextBox2.Value = 2
UserForm1.TextBox3.Value = 3
UserForm1.TextBox4.Value = 4
UserForm1.TextBox5.Value = 1
CommandButton1_Click
End Sub

Private Sub CommandButton1_Click()

Dim arKurse(4) As Double

arKurse(0) = CDbl(UserForm1.TextBox1.Value)
arKurse(1) = CDbl(UserForm1.TextBox2.Value)
arKurse(2) = CDbl(UserForm1.TextBox3.Value)
arKurse(3) = CDbl(UserForm1.TextBox4.Value)
arKurse(4) = CDbl(UserForm1.TextBox5.Value)

Dim oChtObj As ChartObject
Set oChtObj = ActiveSheet.ChartObjects.Add(Left:=445, Width:=385, Top:=10, Height:=245)

With oChtObj.Chart
    .SeriesCollection.NewSeries
    'Assumed series 1
    .SeriesCollection(1).Values = arKurse
    .SeriesCollection(1).XValues = Array("1", "2", "3", "4", "5")

    .ChartType = xlLine
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Caption = "Chart"
End With

End Sub

Upvotes: 2

Related Questions