In my workbook, I have three possible sheets which can start my code (Site1, Site2, Site3). They all have the following code to kick off the process.
Option Explicit
' Script intended to run upon any change in worksheet.
Private Sub worksheet_change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Main.Switchboard Target
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
All of my data validation and formatting rules are defined in a 4th sheet (VBARefSht) so a user can update certain criteria without touching the code (e.g. date range, location, product, order types, etc).
This is an example of one of the checks:
Sub FacilityCheck(ActiveRow As Integer, ActiveCol As Integer, _
WorkingSheet As String)
' If the PO is being entered on the Site2 sheet,
' the plant/facility will be set to Site2.
' Site3 is technically still Site1, but is just
' a different storage area requiring a different sheet.
Dim Fac1 As String
Dim Fac2 As String
Dim FacOut As String
Sheets(VBARefSht).Activate
Fac1 = Cells(Fac1Row, FacCol).Value
Fac2 = Cells(Fac2Row, FacCol).Value
If StrComp(WorkingSheet, Fac2, vbTextCompare) = 0 Then
FacOut = Fac2
Else
FacOut = Fac1
End If
Sheets(WorkingSheet).Activate
Cells(ActiveRow, ActiveCol).Value = FacOut
' FacOut is changed to the process order type for _
' the purpose of determining which formatting rules to apply.
FacOut = Cells(ActiveRow, TypeColIndex).Value
Rules_AllFormatting.RulesSelection ActiveRow, ActiveCol, _
WorkingSheet, FacOut
End Sub
When the code runs, it flickers between the ActiveSheet and the VBARefSht.
Is there a way to make it only show the ActiveSheet?
As others have mentioned, it's almost entirely unnecessary in VBA to Active and Select things. Activating a sheet and selecting things is what us humans need to do to interact with Excel through its UI. When in VBA we are interacting with excel through it's programming API.
Changing your code around to avoid the human-like activity:
Sub FacilityCheck(ActiveRow As Integer, ActiveCol As Integer, _
WorkingSheet As String)
' If the PO is being entered on the Site2 sheet,
' the plant/facility will be set to Site2.
' Site3 is technically still Site1, but is just
' a different storage area requiring a different sheet.
Dim Fac1 As String
Dim Fac2 As String
Dim FacOut As String
'Just get the values directly from the sheet/cell without activating
Fac1 = Sheets(VBARefSht).Cells(Fac1Row, FacCol).Value
Fac2 = Sheets(VBARefSht).Cells(Fac2Row, FacCol).Value
If StrComp(WorkingSheet, Fac2, vbTextCompare) = 0 Then
FacOut = Fac2
Else
FacOut = Fac1
End If
Sheets(WorkingSheet).Cells(ActiveRow, ActiveCol).Value = FacOut
' FacOut is changed to the process order type for _
' the purpose of determining which formatting rules to apply.
FacOut = Sheets(WorkingSheet).Cells(ActiveRow, TypeColIndex).Value
Rules_AllFormatting.RulesSelection ActiveRow, ActiveCol, _
WorkingSheet, FacOut
End Sub
The big change here, besides removing the .Activate lines, is that we are qualifying all of the .Cells() with the sheet name for that cell object.
While this likely fixes your problem, it's a good idea to read the answer here: How can I avoid using Select in Excel VBA? (which was posted in the comments as well) as it covers this subject in depth and will help a ton next time you write some VBA to avoid this anti-pattern.