Ndtyjky

I made a macro that prepares data to build a graph of a personality test. It works as follows:

• 
  1) The user fills a form where he or she answers questions using numbers ranging from -10 to 10. The form is in a worksheet called
  Minha História.


• 
  2) The graph is on the worksheet Meu Grafico. When the correspondent tab of Meu Grafico is select, the Macro is loaded
  through the Chart Activate event.


• 
  3) Additionally, the macro adjusts the X-axis of the graph to the data origin, since the quantity of data on the graph varies with the
  age of the user.

The problem: This macro runs perfectly on Windows, but when I tried to run it on a Mac computer it failed to run, even when macros are habilitated to the Mac computer.

What is strange: In the VBE Project window of the Windows computer I can see that the code is correctly put in the Chart object module called _Planilha2(Meu Grafico)_. But on the Mac, the system:

a) ignores that assignment and renames the chart object module to _Planilha21(Meu Grafico)_;

b) erases the code in this module and put it in a new module called _Gr.fico2_ (Why?).

Obs: This Macro does not construct the graph. It only made some calculations with the answers in order that they be reflected on the graph. It has to work at any moment the user changes the answer of any question in the form. And, if fact, it do it on the Windows PC, only.

Can anyone can help me with this?

enter image description here

Code:

Private Sub Chart_Activate()

'Macro criada por Ramon Barbosa Rosa em 29/10/2018

'SANTÉ COACHING

Dim Dados As Worksheet

Dim Base As Worksheet

Dim j As Integer

Dim Idade As Integer

Dim Coluna As Integer

Set Dados = Worksheets("Minha História")

Set Base = Worksheets("Base")

Idade = -1



'Calcula idade do coachee

For j = 2 To 95

    If Base.Cells(2, j) <> "" Then

    Idade = Idade + 1

    End If

Next j



Coluna = Idade + 2 'Indica o número da coluna do ano corrente



'Apaga valores remanescentes de anos anteriores

Base.Range(Base.Cells(5, Coluna + 1), Base.Cells(5, 100)).ClearContents

Base.Range(Base.Cells(8, Coluna + 1), Base.Cells(8, 100)).ClearContents

Base.Range(Base.Cells(11, Coluna + 1), Base.Cells(11, 100)).ClearContents



'Série PESSOAL: Preenche valor referente ao ano de nascimento

If Base.[b4] = 0 Then

    Base.[b5] = Base.[b3]

Else

    Base.[b5] = Base.[b4]

End If



'Série PESSOAL: preenche demais anos (Planilha Ref, oculta)

For i = 3 To Idade + 2

    If Base.Cells(4, i - 1) > 0 And Base.Cells(4, i) > 0 Then 'Laranja

        Base.Cells(5, i) = Base.Cells(5, i - 1) + Base.Cells(4, i)

    ElseIf Base.Cells(4, i - 1) > 0 And Base.Cells(4, i) < 0 Then 'verde água

        Base.Cells(5, i) = Base.Cells(3, i) + Base.Cells(4, i)

    ElseIf Base.Cells(4, i - 1) <= 0 And Base.Cells(4, i) > 0 Then 'Roxo

        Base.Cells(5, i) = Base.Cells(4, i) + Application.WorksheetFunction.Max(Base.Range(Base.Cells(5, 2), Base.Cells(5, i - 1)))

    ElseIf Base.Cells(4, i - 1) <= 0 And Base.Cells(4, i) < 0 Then 'Azul

        Base.Cells(5, i) = Base.Cells(5, i - 1) + Base.Cells(4, i)

    ElseIf Base.Cells(4, i) = 0 Then 'Rosa

        Base.Cells(5, i) = Base.Cells(3, i)

    End If

Next i



'Série PROFISSIONAL: Preenche valor referente ao ano de nascimento

If Base.[b10] = 0 Then

    Base.[b11] = 0

Else

    Base.[b11] = Base.[10]

End If



'Série PROFISSIONAL: preenche demais anos (Planilha Ref, oculta)

'Considera resultado como igual a zero até a primeira experiência profissional

For i = 3 To Idade + 2

    If Application.WorksheetFunction.Sum(Base.Range(Base.Cells(10, 2), Base.Cells(10, i))) = 0 Then

        Base.Cells(11, i) = 0

    ElseIf Base.Cells(10, i - 1) > 0 And Base.Cells(10, i) > 0 Then 'Laranja

        Base.Cells(11, i) = Base.Cells(11, i - 1) + Base.Cells(10, i)

    ElseIf Base.Cells(10, i - 1) > 0 And Base.Cells(10, i) < 0 Then 'verde água

        Base.Cells(11, i) = Base.Cells(3, i) + Base.Cells(10, i)

    ElseIf Base.Cells(10, i - 1) <= 0 And Base.Cells(10, i) > 0 Then 'Roxo

        Base.Cells(11, i) = Base.Cells(10, i) + Application.WorksheetFunction.Max(Base.Range(Base.Cells(11, 2), Base.Cells(11, i - 1)), Base.Cells(3, i)) 'O máximo é um valor histórico ou o baseline do ano

    ElseIf Base.Cells(10, i - 1) <= 0 And Base.Cells(10, i) < 0 Then 'Azul

        Base.Cells(11, i) = Base.Cells(11, i - 1) + Base.Cells(10, i)

    ElseIf Base.Cells(10, i) = 0 And Application.WorksheetFunction.Sum(Base.Range(Base.Cells(10, 2), Base.Cells(10, i - 1))) <> 0 Then 'Rosa

        Base.Cells(11, i) = Base.Cells(3, i)

    ElseIf Base.Cells(10, i) > 0 And Application.WorksheetFunction.Sum(Base.Range(Base.Cells(10, 2), Base.Cells(10, i - 1))) = 0 Then 'Vermelho

        Base.Cells(11, i) = Base.Cells(3, i) + Base.Cells(10, i)

    ElseIf Base.Cells(10, i) < 0 And Application.WorksheetFunction.Sum(Base.Range(Base.Cells(10, 2), Base.Cells(10, i - 1))) = 0 Then 'Verde Claro

        Base.Cells(11, i) = Base.Cells(10, i)

    End If

Next i



'Série SOCIAL: Preenche valor referente ao ano de nascimento

If Base.[b7] = 0 Then

    Base.[b8] = 0

Else

    Base.[b8] = Base.[7]

End If



'Série SOCIAL: preenche demais anos (Planilha Ref, oculta)

For i = 3 To Idade + 2

    If Base.Cells(7, i - 1) >= 0 And Base.Cells(7, i) > 0 Then 'Laranja

        Base.Cells(8, i) = Base.Cells(8, i - 1) + Base.Cells(7, i)

    ElseIf Base.Cells(7, i - 1) >= 0 And Base.Cells(7, i) < 0 Then 'verde água

        Base.Cells(8, i) = Base.Cells(7, i)

    ElseIf Base.Cells(7, i - 1) < 0 And Base.Cells(7, i) > 0 Then 'Roxo

        Base.Cells(8, i) = Base.Cells(7, i)

    ElseIf Base.Cells(7, i - 1) < 0 And Base.Cells(7, i) < 0 Then 'Azul

        Base.Cells(8, i) = Base.Cells(7, i)

    ElseIf Base.Cells(7, i) = 0 Then 'Rosa

        Base.Cells(8, i) = 0

    End If

Next i



'Ajusta a fonte de dados do gráfico

With Charts("Meu Grafico")

.SeriesCollection(1).FormulaR1C1 = "=SERIES(BASE!R5C1,BASE!R2C2:R2C" & Coluna & ",BASE!R5C2:R5C" & Coluna & ",1)"  'Pessoal

.SeriesCollection(2).FormulaR1C1 = "=SERIES(BASE!R8C1,BASE!R2C2:R2C" & Coluna & ",BASE!R8C2:R8C" & Coluna & ",2)" 'Social

.SeriesCollection(3).FormulaR1C1 = "=SERIES(BASE!R11C1,BASE!R2C2:R2C" & Coluna & ",BASE!R11C2:R11C" & Coluna & ",3)" 'Profissional

.SeriesCollection(4).FormulaR1C1 = "=SERIES(BASE!R3C1,BASE!R2C2:R2C" & Coluna & ",BASE!R3C2:R3C" & Coluna & ",4)" 'Série 4 (Baseline)

End With

End Sub