Have a conflict between 2 VBA´s code which brings an error

Lacan · Oct 13, 2024

Hello Guys,
Have a conflict with between 2 VBA´s code wich gets an error.

Explaining:

This first Code below changes the name of the sheet accordingly with the name written in cell B2 (after press Buton "Enter") in Sheet "NOME INSTITUIÇÃO":

Sub Enter()
ActiveSheet.Name = Range("B2").Value
End Sub

And this second code below combined with Sheet´s name "NOME INSTITUIÇÃO" doesn´t match because early this sheet´s name has been changed previously code one.

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("NOME INSTITUIÇÃO").Activate

If Not Intersect(Target, Range("C22")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(22, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

Can you please help?
More information feel free to ask.
Thanks again for all the help!

F Cliente_MÉDICO_AGO.24.xlsb

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

2

Brick

-

Un. Tipo

3

-

RANK

4

Coordenador

Farmácias Brick

5

Seguranças

-

6

Horário de Visita

7

Observações

8

9

N.º Total Médicos

10

11

12

13

14

15

16

17

18

19

20

21

ANOTE O QUE DE RELEVANTE ACONTECEU NA PRESENTE ENTREVISTA E ESCREVA A FORMA COMO VAI COMEÇAR A PRÓXIMA ENTREVISTA DE VENDAS.

22

23

24

25

26

OBJECTIVO

27

28

29

30

NOME INSTITUIÇÃO

Cell Formulas
Range	Formula
I2	I2	=UNIQUE(FILTER(PAINEL_MÉDICO_VS_ALL[Brick],PAINEL_MÉDICO_VS_ALL[Centro]=B2," - "))
I3	I3	=XLOOKUP(I2,PAINEL_MÉDICO_VS_ALL[Brick],PAINEL_MÉDICO_VS_ALL[MAT Rank MKT]," - ",0)
N5	N5	=IFERROR(LET(s,SORT(SORT(UNIQUE(FILTER(PAINEL_MÉDICO_VS_ALL[[NOME CLIENTE.1]:[DECIL_2]],PAINEL_MÉDICO_VS_ALL[Brick]=I2," - ")),2,-1),2,-1),IFNA(HSTACK(TAKE(s,5),DROP(s,5)),""))," - ")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P5:Q11	Expression	=CONTAR.SE.S('BASE DADOS'!$K$3:$K$23000;P5;'BASE DADOS'!$M$3:$M$23000;"S")	text	NO
N5:O11	Expression	=CONTAR.SE.S('BASE DADOS'!$K$3:$K$23000;N5;'BASE DADOS'!$M$3:$M$23000;"S")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	='BASE DADOS'!$B$2#
C2:G2	Any value

DanteAmor · Oct 13, 2024

Just delete this line:

VBA Code:

Sheets("NOME INSTITUIÇÃO").Activate

Lacan · Oct 14, 2024

DanteAmor said:
Just delete this line:

VBA Code:

Sheets("NOME INSTITUIÇÃO").Activate

Dear @DanteAmor

Thanks for the help.
Will give more information because if delete what you suggest will get a new conflict in VBA code in Sheet "NOME MÉDICO".
Can you please help.
Thanks in advance!

In sheet "NOME INSTITUIÇÃO" :

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("NOME INSTITUIÇÃO").Activate

If Not Intersect(Target, Range("C22")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(22, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

Module 1

VBA Code:

Sub Linha()
'
' Linha Macro
'

'
    Rows("26:26").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C26:S26").Select
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    Range("C26:S26").Select
End Sub

Module 2

VBA Code:

Sub Enter()
ActiveSheet.Name = Range("B2").Value
End Sub

Module 3

VBA Code:

Sub Novo()
'
' Novo Macro
'

'
    Rows("22:27").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B22").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B26").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C22:S22").Select
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.AutoFill Destination:=Range("C22:S26"), Type:=xlFillDefault
    Range("C22:S26").Select
    Range("B22:B26").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B22:S26").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    Range("C22:S26").Select
End Sub

In sheet "NOME MÉDICO":

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("NOME MÉDICO").Activate

If Not Intersect(Target, Range("C18")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(18, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

Módulo 1

VBA Code:

Sub Novo()
    '
    ' Novo Macro
    '

    Sheets("NOME MÉDICO").Activate

    Rows("18:23").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B18").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B22").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C18:P18").Select
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.AutoFill Destination:=Range("C18:P22"), Type:=xlFillDefault
    Range("C18:P22").Select
    Range("B18:B22").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B18:P22").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    Range("C18:P22").Select
End Sub

Módulo 2

VBA Code:

Sub Linha()
'
' Linha Macro
'

'

Sheets("NOME MÉDICO").Activate

    Rows("22:22").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C22:P22").Select
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .Font.Bold = True
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Font
        .Color = -65536
        .TintAndShade = 0
    End With
    Range("C22:P22").Select
End Sub

MARK858 · Oct 14, 2024

Neither of the codes in the sheets should have a Worksheet.Activate line

DanteAmor · Oct 14, 2024

Lacan said:
This first Code below changes the name of the sheet accordingly with the name written in cell B2 (after press Buton "Enter") in Sheet "NOME INSTITUIÇÃO":

Sub Enter()
ActiveSheet.Name = Range("B2").Value
End Sub

What is the purpose of renaming the sheet?

If you change the name of the macro and later use the "old" name in the macro, you will have an error.
So my suggestion is: if you don't have a background goal that involves changing the sheet name, then don't change the name and your macros will work without a problem.

Lacan · Oct 15, 2024

DanteAmor said:
What is the purpose of renaming the sheet?

If you change the name of the macro and later use the "old" name in the macro, you will have an error.
So my suggestion is: if you don't have a background goal that involves changing the sheet name, then don't change the name and your macros will work without a problem.

Dear @DanteAmor

Thanks for the suggestion.
My goal is to create a follow up file for each one customer.
Each sheet apparently should have a personal customer name like: Lisa, Peter, John and so on... in order to take some notes in there each time we meet together.
Because are serveral people its more easier for me to search for the sheet name of the person like a file in an archive.

If you find a way please let me know!

Thank you very much.

DanteAmor · Oct 15, 2024

It's not very clear to me if you are going to make a copy of the book.
But check the following to see if it helps you.

Sheets have 3 values that you can reference: The sheet name, the code name and the sheet index.

In your example, the sheet names are:

"NOME INSTITUIÇÃO"

"NOME MÉDICO"

"BASE DADOS"

The code name are:

Folha1

Folha2

Folha3

The sheet index are:

1

2

3

So, for example in Módulo 1:

Rich (BB code):

Sub Novo()
    '
    ' Novo Macro
    Sheets("NOME MÉDICO").Activate
    Rows("18:23").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B18").Select
'....

Instead of this:

Sheets("NOME MÉDICO").Activate

You can use:

Folha2.Activate

Or you can use:

Sheets(2).Activate

I hope it helps you.

Lacan · Oct 16, 2024

DanteAmor said:
It's not very clear to me if you are going to make a copy of the book.
But check the following to see if it helps you.

View attachment 118111
Sheets have 3 values that you can reference: The sheet name, the code name and the sheet index.

In your example, the sheet names are:

"NOME INSTITUIÇÃO"
"NOME MÉDICO"
"BASE DADOS"

The code name are:

Folha1
Folha2
Folha3

The sheet index are:

1
2
3

So, for example in Módulo 1:

Rich (BB code):

Sub Novo() ' ' Novo Macro Sheets("NOME MÉDICO").Activate Rows("18:23").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("B18").Select '....

Instead of this:

Sheets("NOME MÉDICO").Activate

You can use:

Folha2.Activate

Or you can use:

Sheets(2).Activate

I hope it helps you.

What a Lesson @DanteAmor !!!!!!

Excel didn´t even Blink!!!

Just simple Love when you break my mind through your knowledge and competence in Excel! Beautiful, beautiful and beautiful!!!!
Thank you very much my Friend!!!

smozgur · Oct 16, 2024

Lacan said:
What a Lesson @DanteAmor !!!!!!
Excel didn´t even Blink!!!

Just simple Love when you break my mind through your knowledge and competence in Excel! Beautiful, beautiful and beautiful!!!!
Thank you very much my Friend!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

Have a conflict between 2 VBA´s code which brings an error

Lacan

Board Regular

Excel Facts

DanteAmor

Well-known Member

Lacan

Board Regular

MARK858

MrExcel MVP

DanteAmor

Well-known Member

Lacan

Board Regular

DanteAmor

Well-known Member

Lacan

Board Regular

smozgur

BatCoder

Similar threads

Forum statistics

Share this page

Have a conflict between 2 VBA´s code which brings an error

Board Regular

Excel Facts

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

Board Regular

BatCoder

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock