0
I’m making a VBA form with information about some municipalities (that are in Combobox) and their respective goals to be hit (which is like a direct formula in the spreadsheet).
In the form I insert the information I want (some data from the municipalities) and a specific column in the spreadsheet contains the formula that makes the percentage of the completed goal so far.
I would like to make, when closing the form, automatically generate me a graph with the percentage of the goal and the name of the municipality, but this information of the goal is not in the code but directly in the spreadsheet.
Private Sub CommandButton1_Click()
Dim range1 As Range
Dim objeto As Control
Sheets("REGISTRO").Select
If Range("A2").Value = "" Then
Set range1 = Range("A2")
Else
Set range1 = Range("A1").End(xlDown).Offset(1, 0)
End If
range1.Value = UserForm2.ComboBox1.Value
range1.Offset(0, 1).Value = UserForm2.TextBox1.Value
range1.Offset(0, 2).Value = UserForm2.TextBox2.Value
range1.Offset(0, 4).Value = UserForm2.TextBox3.Value
range1.Offset(0, 5).Value = UserForm2.TextBox4.Value
range1.Offset(0, 6).Value = UserForm2.TextBox5.Value
range1.Offset(0, 7).Value = UserForm2.TextBox6.Value
'criando gráfico
range1.Select
range1.Activate
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("REGISTRO!A2,REGISTRO!H2")
'fechando a aplicação
UserForm2.Hide
For Each objeto In UserForm2.Controls
On Error Resume Next
objeto.Value = ""
Next
The column of the goal percentage is J. I tried to record a macro to do this, but I wanted to make the graph be generated from the last line, IE, the last municipality registered in the form already return me the chart with the completed percentage until that time.
I believe the problem is in this line "Source", but I don’t understand how to do it the way I want.
Thank you in advance.