after transferring code it works but should i change name

[dane_m_steyne]

Hi

I transferred this code from a workbook to a new workbook and it is working but should I change code. The old code looks at "Data" sheet but I have changed to "Main" sheet for new workbook.This part is correct...Not sure about next though..The old workbook looked at Team and new workbook refers to Group......should I change Private Function and Dim names from Team to Group etc..etc..

The old workbook used chart code below

Private Function m_GetTeamSheet(Name As String) As Worksheet

    On Error Resume Next
    Set m_GetTeamSheet = ThisWorkbook.Worksheets(Name)
    If m_GetTeamSheet Is Nothing Then
        ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set m_GetTeamSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        m_GetTeamSheet.Name = Name
    End If
    Exit Function
    
End Function
Private Sub CommandButton1_Click()
    Dim lng As Long
    Dim rngLabels(1 To 3) As Range
    Dim rngData(1 To 3) As Range
    Dim lngChartIndex As Long
    Dim lngTeamIndex As Long
    Dim lngHGap As Long
    Dim lngVGap As Long
    Dim lngHeight As Long
    Dim lngWidth As Long
    Dim lngLeft As Long
    Dim shtOutput As Worksheet
    
    lngWidth = 1000
    lngHeight = 250
    lngHGap = 10
    lngVGap = 10
    
    With Worksheets("Data")
        ' reference team 1
        Set rngLabels(1) = .Range("E8:E11")
        Set rngLabels(2) = .Range("E6:E7,E12:E13")
        Set rngLabels(3) = .Range("E14:E17")
    End With
    
    For lng = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(lng) Then
            Set shtOutput = m_GetTeamSheet(ListBox1.List(lng))
            With shtOutput
                If .ChartObjects.Count Then
                    .ChartObjects.Delete
                End If
                
                lngLeft = 30
                lngTeamIndex = lngTeamIndex + 1
                For lngChartIndex = 3 To 3
                    ' reference teams actual data
                    Set rngData(lngChartIndex) = rngLabels(lngChartIndex).Offset(0, Me.ComboBox1.ListIndex + 1)
                    With .Shapes.AddChart.Chart
                        .Parent.Width = lngWidth
                        .Parent.Height = lngHeight
                        .Parent.Top = lngVGap
                        .Parent.Left = lngLeft
                        lngLeft = lngLeft + .Parent.Width + lngHGap
                        .ChartType = xlColumnClustered
                        .ChartGroups(1).GapWidth = 50
                        .PlotBy = xlRows
                        With .SeriesCollection.NewSeries
                            .Values = rngData(lngChartIndex)
                            .XValues = rngLabels(lngChartIndex)
                        End With
                        .HasLegend = False
                        .HasTitle = True
                        .ChartTitle.Text = "Team " & lng + 1
                        
                        With .Axes(xlValue)
                            .MinimumScale = 0
                            .MaximumScale = 100
                            .MajorUnit = 10
                        End With
                        
                        With .Axes(xlCategory)
                            .HasTitle = True
                            .AxisTitle.Text = ComboBox1.Value
                        End With
                    End With
                Next
            End With
            ' move to next team
            For lngChartIndex = 1 To 3
                Set rngLabels(lngChartIndex) = rngLabels(lngChartIndex).Offset(14, 0)
            Next
        End If
    Next lng
    
    If shtOutput Is Nothing Then
    Else
        Application.Goto shtOutput.Range("A1")
    End If
    Unload Me
    
End Sub
Private Sub UserForm_Activate()
    
    Me.ComboBox1.List = Application.Transpose(Worksheets("Data").Range("F5").Resize(1, 7).Value)
    Me.ComboBox1.ListIndex = 0
    Me.ListBox1.List = Array("team-1", "team-2", "team-3")
    
End Sub

The new workbook I m using this code

Private Function m_GetTeamSheet(Name As String) As Worksheet
    On Error Resume Next
    Set m_GetTeamSheet = ThisWorkbook.Worksheets(Name)
    If m_GetTeamSheet Is Nothing Then
        ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set m_GetTeamSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        m_GetTeamSheet.Name = Name
    End If
    Exit Function
    
End Function
Private Sub CommandButton1_Click()
    Dim lng As Long
    Dim rngLabels(1 To 3) As Range
    Dim rngData(1 To 3) As Range
    Dim lngChartIndex As Long
    Dim lngTeamIndex As Long
    Dim lngHGap As Long
    Dim lngVGap As Long
    Dim lngHeight As Long
    Dim lngWidth As Long
    Dim lngLeft As Long
    Dim shtOutput As Worksheet
    
    lngWidth = 1000
    lngHeight = 250
    lngHGap = 10
    lngVGap = 10
    
    With Worksheets("MAIN")
        ' reference team 1
        Set rngLabels(1) = .Range("E8:E11")
        Set rngLabels(2) = .Range("E6:E7,E12:E13")
        Set rngLabels(3) = .Range("E14:E17")
    End With
    
    For lng = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(lng) Then
            Set shtOutput = m_GetTeamSheet(ListBox1.List(lng))
            With shtOutput
                If .ChartObjects.Count Then
                    .ChartObjects.Delete
                End If
                
                lngLeft = 30
                lngTeamIndex = lngTeamIndex + 1
                For lngChartIndex = 3 To 3
                    ' reference teams actual data
                    Set rngData(lngChartIndex) = rngLabels(lngChartIndex).Offset(0, Me.ComboBox1.ListIndex + 1)
                    With .Shapes.AddChart.Chart
                        .Parent.Width = lngWidth
                        .Parent.Height = lngHeight
                        .Parent.Top = lngVGap
                        .Parent.Left = lngLeft
                        lngLeft = lngLeft + .Parent.Width + lngHGap
                        .ChartType = xlColumnClustered
                        .ChartGroups(1).GapWidth = 50
                        .PlotBy = xlRows
                        With .SeriesCollection.NewSeries
                            .Values = rngData(lngChartIndex)
                            .XValues = rngLabels(lngChartIndex)
                        End With
                        .HasLegend = False
                        .HasTitle = True
                        .ChartTitle.Text = "Group " & Chr(lng + 65)
                        
                        With .Axes(xlValue)
                            .MinimumScale = 0
                            .MaximumScale = 100
                            .MajorUnit = 10
                        End With
                        
                        With .Axes(xlCategory)
                            .HasTitle = True
                            .AxisTitle.Text = ComboBox1.Value
                        End With
                    End With
                Next
            End With
            ' move to next team
            For lngChartIndex = 3 To 3
                Set rngLabels(lngChartIndex) = rngLabels(lngChartIndex).Offset(14, 0)
            Next
        End If
    Next lng
    
    If shtOutput Is Nothing Then
    Else
        Application.Goto shtOutput.Range("A1")
    End If
    Unload Me
    
End Sub
Private Sub UserForm_Activate()
    
    Me.ComboBox1.List = Application.Transpose(Worksheets("MAIN").Range("C7").Resize(1, 7).Value)
    Me.ComboBox1.ListIndex = 0
    Me.ListBox1.List = Array("Group A", "Group B", "Group C")
    
End Sub

 

[Snakehips]

dane,

The changes that you have made are giving the correct result in the new workbook so from that viewpoint there is no reason to alter anything else.

What you may wish to consider is whether the variables used originally that are meaning full in the original code because they relate to Teams e.g. lngTeamIndex are now or at some time in the future, going to be confusing to you or whoever may need to interpret or amend the code as applied to Groups. You might even want to consider changing both to a common set of more universally meaningful variables?

Hope that helps.

 

[dane_m_steyne]

Thanks Tony