Make VBA codes according to one specific sheet and not all entire workbook?

Lacan

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
237
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, 😉👍

Hope all of you are fine!👌🍻
Would like your help in this question:

Have this 3 different codes below that work very fine but would like to use them in one specific sheet and not all entire workbook?
What kind of changes have to add in each one code?


VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(20, 2).Value = Format(Date, "dd-mmm")
End If
End Sub

-----------------------------------------
VBA Code: 
Sub Linha()
'
' Linha Macro
'

'
    Rows("24:24").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C24:S24").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("C24:S24").Select
End Sub

Sub Novo()
'
' Novo Macro
'

'
    Rows("20:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B20").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B24").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C20:S20").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("C20:S24"), Type:=xlFillDefault
    Range("C20:S24").Select
    Range("B20:B24").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B20:S24").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("C20:S24").Select
End Sub

Customer File.xlsb
ABCDEFGHIJKLMNOPQRST
1
2BioUn. Tipo
3RANK
4Coordenador
5Limpezas
6Horário de Visita
7Observações
8
9N.º
10
11
12
13
14
15
16
17
18
19ANOTE O QUE DE RELEVANTE ACONTECEU NA PRESENTE ENTREVISTA E ESCREVA A FORMA COMO VAI COMEÇAR A PRÓXIMA ENTREVISTA DE VENDAS.
20
21
22
23
24OBJECTIVO
25
26
ORGANIZATION
Cells with Data Validation
CellAllowCriteria
B2List=#REF!$A$3#
C2:G2Any value
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(20, 2).Value = Format(Date, "dd-mmm")
End If
End Sub
Should be in the worksheet module of the actual worksheet

Both the other codes should have the below as the first line as you are selecting cells

Rich (BB code): 
Sheets("Your_Sheet_Name").Activate

I assume Your_Sheet_Name should be ORGANIZATION
 
Upvote 0
MARK858 said:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(20, 2).Value = Format(Date, "dd-mmm")
End If
End Sub
Should be in the worksheet module of the actual worksheet

Both the other codes should have the below as the first line as you are selecting cells

Rich (BB code): 
Sheets("Your_Sheet_Name").Activate

I assume Your_Sheet_Name should be ORGANIZATION
Click to expand...

Dear @MARK858

Thank you very much for the help.
Let me try it first then give the feedback in return.

Until then please confirm - in the same worksheet module it is possible to write different VBA Codes?

Thank you very much!!! 💪👍👍🍺🍺
 
Upvote 0
Lacan said:
Until then please confirm - in the same worksheet module it is possible to write different VBA Codes?
Click to expand...
You should really keep to using worksheet event codes in a worksheet module and keep regular subs in a regular module and qualify the ranges with the worksheet.

You can only have one specific type of of worksheet event code in the same worksheet module (you can have have multiple different types of worksheet event code in the same worksheet module)
 
Upvote 0
MARK858 said:
You should really keep to using worksheet event codes in a worksheet module and keep regular subs in a regular module and qualify the ranges with the worksheet.

You can only have one specific type of of worksheet event code in the same worksheet module (you can have have multiple different types of worksheet event code in the same worksheet module)
Click to expand...
@MARK858

Thank you.
To be more pratical with this example.
Have this Visual VBA Project would like this VBA Code only work in Sheet "ORGANIZATION":

1727126560336.png


1.Where should write it?
2.What changes have to do in it ( VBA Code below )?

Think it should be better explained step by step.
Thanks again!!!!! 👍👍🍺🍺

VBA Code: 
Sub Novo()
'
' Novo Macro
'

'
    Rows("20:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B20").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B24").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C20:S20").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("C20:S24"), Type:=xlFillDefault
    Range("C20:S24").Select
    Range("B20:B24").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B20:S24").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("C20:S24").Select
End Sub
 
Last edited by a moderator:
Upvote 0
Lacan said:
1.Where should write it?
Click to expand...
It should really be in Module 1, 2 or 3 by your pic
Lacan said:
2.What changes have to do in it ( VBA Code below )?
Click to expand...
Because you are using Select / Selection you need to activate the sheet

Rich (BB code): 
Sub Novo()
    '
    ' Novo Macro
    '

    Sheets("ORGANIZATION").Activate

    Rows("20:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B20").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B24").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C20:S20").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("C20:S24"), Type:=xlFillDefault
    Range("C20:S24").Select
    Range("B20:B24").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B20:S24").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("C20:S24").Select
End Sub
 
Upvote 0
Solution
MARK858 said:
It should really be in Module 1, 2 or 3 by your pic

Because you are using Select / Selection you need to activate the sheet

Rich (BB code): 
Sub Novo()
    '
    ' Novo Macro
    '

    Sheets("ORGANIZATION").Activate

    Rows("20:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B20").Select
    Selection.NumberFormat = "dd-mmm"
    Range("B24").Select
    ActiveCell.FormulaR1C1 = "OBJECTIVO"
    Range("C20:S20").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("C20:S24"), Type:=xlFillDefault
    Range("C20:S24").Select
    Range("B20:B24").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B20:S24").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("C20:S24").Select
End Sub
Click to expand...

Thank you very much @MARK858 !!!👍👍
Works perfect!!! 🔝🔝
All the best!!!!
 
Upvote 0
Dear @MARK858

Hope you are fine! 👍
With your help and after all the work thinking is was just perfect just discover a conflicting VBA code error.
To explain:

1728206633421.png


This Code changes the name of the sheet accordingly 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 one doesn´t match the Sheet name after Sheet´s name has been changed in previously 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

How can we solve this one?
Thanks again for all the help!👍👍🍻
 
Upvote 0
If the code is in the worksheet you are applying the code (which is the only reason it should be in a worksheet event code) then the
VBA Code: 
Sheets("NOME INSTITUIÇÃO").Activate
shouldn't be there
 
Upvote 0
MARK858 said:
If the code is in the worksheet you are applying the code (which is the only reason it should be in a worksheet event code) then the
VBA Code: 
Sheets("NOME INSTITUIÇÃO").Activate
shouldn't be there
Click to expand...

Dear @MARK858

Try to add a new module and clean
VBA Code: 
Sheets("NOME INSTITUIÇÃO").Activate
even though gets conflict.
What should do?
Thank you! 💪💪👍
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
324
Micron
M
A
  • Question Question
Save as dialogue box for excel, but pre populating filename from source word document
Replies
8
Views
282
RoryA
RoryA
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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