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

Lacan

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
228
Office Version
  1. 365
Platform
  1. Windows
Hello Guys,
Have a conflict with between 2 VBA´s code wich gets an error.

Explaining:

1728854388183.png



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
ABCDEFGHIJKLMNOPQRSTU
1
2Brick - Un. Tipo
3 - RANK
4CoordenadorFarmácias Brick
5Seguranças -
6Horário de Visita
7Observações
8
9N.º Total Médicos
10
11
12
13
14
15
16
17
18
19
20
21ANOTE 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
26OBJECTIVO
27
28
29
30
NOME INSTITUIÇÃO
Cell Formulas
RangeFormula
I2I2=UNIQUE(FILTER(PAINEL_MÉDICO_VS_ALL[Brick],PAINEL_MÉDICO_VS_ALL[Centro]=B2," - "))
I3I3=XLOOKUP(I2,PAINEL_MÉDICO_VS_ALL[Brick],PAINEL_MÉDICO_VS_ALL[MAT Rank MKT]," - ",0)
N5N5=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
CellConditionCell FormatStop If True
P5:Q11Expression=CONTAR.SE.S('BASE DADOS'!$K$3:$K$23000;P5;'BASE DADOS'!$M$3:$M$23000;"S")textNO
N5:O11Expression=CONTAR.SE.S('BASE DADOS'!$K$3:$K$23000;N5;'BASE DADOS'!$M$3:$M$23000;"S")textNO
Cells with Data Validation
CellAllowCriteria
B2List='BASE DADOS'!$B$2#
C2:G2Any value
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Just delete this line:

VBA Code: 
Sheets("NOME INSTITUIÇÃO").Activate
 
Upvote 0
DanteAmor said:
Just delete this line:

VBA Code: 
Sheets("NOME INSTITUIÇÃO").Activate
Click to expand...

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!💪💪👍

1728937993576.png




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
 
Upvote 0
Neither of the codes in the sheets should have a Worksheet.Activate line
 
Upvote 0
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
Click to expand...
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.
 
Upvote 0
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.
Click to expand...
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.
 
Upvote 0
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.

1729031657505.png

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.
🤗
 
Upvote 0
Solution
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.
🤗
Click to expand...

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!!!👍👍🍻🍻🍻
 
Upvote 0
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!!!👍👍🍻🍻🍻
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Fetch heads up stats between 2 teams
Replies
2
Views
275
AYouQueTai
A
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M
W
  • Question Question
Defined coloring of dynamic diagrams
Replies
0
Views
401
WalmitAal
W
willow1985
  • Solved
Assistance with VBA error handling - a cell not matching data set
2
Replies
10
Views
1K
willow1985
willow1985
Lacan
  • Solved
Make VBA codes according to one specific sheet and not all entire workbook?
2
Replies
11
Views
364
Lacan
Lacan

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top