Workbook_Sheetchange not firing add-in possible?

C

CRVerdusco

New Member
Joined
Nov 22, 2013
Messages
6
I am trying to get a workbook_Sheetchange with a select case to work on multiple spreadsheets. I had a similar code working just fine in each worksheet but felt like it was bogging down too much, I am trying to streamline better.

The workbook has a sheet for every day of the month and then a monthly totals sheet at the end that calls from each of the daily sheets. The select case applies specific formulas and formatting based on the billing code placed in Column "A"

Any help or feedback would be appreciated. I was also curious if I could call macros for each case to if that would be better.

I don't know if it's an option we have 7 different properties that use a different workbook each, we also have an add-in bar created could the custom formulas and sheetchange be in the add-in while calling constants from a module in each individual workbook?

Code: 
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
            Select Case Target
                Case "C", "c"  ' COMPLIMENTARY ERROR '
                    MsgBox "Please enter a more descriptive Complimentary Code", , "COMPLIMENTARY CODE ERROR"
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 0
                    ActiveSheet.Range("Q" & Target.Row).Value = "ERROR"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = 3
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "ERROR"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 3
                    ActiveSheet.Range("V" & Target.Row) = ""
                    ActiveSheet.Range("W" & Target.Row) = ""
                    ActiveSheet.Range("X" & Target.Row) = ""
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row) = ""
                Case "CO", "co", "Co", "cO"  ' COMPLIMENTARY OTHER EQUIPMENT '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 45
                    ActiveSheet.Range("Q" & Target.Row).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "COMP-O"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 45
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).Value = "COMP-O"
                    ActiveSheet.Range("X" & Target.Row) = ""
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row).FormulaR1C1 = "=CompO((rc[-9],rc[-11],rc[-13]),(rc[-10],rc[-6]))"
                    ActiveSheet.Range("AA" & Target.Row) = ""
                Case "CI", "ci", "Ci", "cI"  ' COMPLIMENTARY INTERNET '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 41
                    ActiveSheet.Range("Q" & Target.Row).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "COMP-I"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 41
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).Value = "COMP-I"
                    ActiveSheet.Range("X" & Target.Row) = ""
                    ActiveSheet.Range("Y" & Target.Row).FormulaR1C1 = "=CompI(rc[-5]:rc[-8])"
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row) = ""
                Case "CD", "cd", "Cd", "cD"  ' COMPLIMENTARY DISCOUNT '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 41
                    ActiveSheet.Range("Q" & Target.Row).Value = "COMP-D"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = 41
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "COMP-D"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 41
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).Value = "COMP-D"
                    ActiveSheet.Range("X" & Target.Row) = ""
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row).FormulaR1C1 = "=CompD((rc[-9],rc[-11],rc[-13],rc[-7]),(rc[-10],rc[-6]))"
                    ActiveSheet.Range("AA" & Target.Row) = ""
                Case "E", "e"  ' TAX EXEMPT '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 0
                    ActiveSheet.Range("Q" & Target.Row).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "EXEMPT"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 4
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                    ActiveSheet.Range("X" & Target.Row).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row).FormulaR1C1 = "=Tax(rc[-7]:rc[-9])"
                Case "D", "d"  ' DISCOUNT '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 0
                    ActiveSheet.Range("Q" & Target.Row).Value = "DISC"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = 44
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).FormulaR1C1 = "=Tax(rc[-1]:rc[-3])"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                    ActiveSheet.Range("X" & Target.Row).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row) = ""
                Case "DE", "de", "De", "dE"  ' DISCOUNT TAX EXEMPT '
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 0
                    ActiveSheet.Range("Q" & Target.Row).Value = "DISC"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = 44
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).Value = "EXEMPT"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = 4
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                    ActiveSheet.Range("X" & Target.Row).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row).FormulaR1C1 = "=Tax(rc[-7]:rc[-9])"
                Case Else
                    ActiveSheet.Range("M" & Target.Row).Font.ColorIndex = 0
                    ActiveSheet.Range("Q" & Target.Row).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                    ActiveSheet.Range("Q" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("R" & Target.Row).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                    ActiveSheet.Range("U" & Target.Row).FormulaR1C1 = "=Tax(rc[-1]:rc[-3])"
                    ActiveSheet.Range("U" & Target.Row).Interior.ColorIndex = xlColorIndexNone
                    ActiveSheet.Range("V" & Target.Row).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                    ActiveSheet.Range("W" & Target.Row).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                    ActiveSheet.Range("X" & Target.Row).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                    ActiveSheet.Range("Y" & Target.Row) = ""
                    ActiveSheet.Range("Z" & Target.Row) = ""
                    ActiveSheet.Range("AA" & Target.Row) = ""
            End Select
        End If
        
    End If
    Application.EnableEvents = True
        
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hi and Welcome to MrExcel,

One approach that you might find easier to maintain would be to use a template sheet that has ranges setup with the desired formulas and formats for each of the scenarios.

Instead of using VBA to apply formulas, values and formatting to the row, you could just copy-paste the desired template range.

That could be a hidden worksheet in your file or an Add-In. The Add-In would be better if you are going to use this on multiple files.

It isn't clear to me from your description and thread title whether you have tried to get this to work in an Add-In yet.

To have the Add-In hook the _Change event, you can use code like added to the ThisWorkbook module of your Add-In.

Code: 
Public WithEvents App As Application

Private Sub Workbook_Open()
     Set App = Application
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'---your code here
'.....
'.....

End Sub
 
Upvote 0
I would probably find a more elegant way also, but to build on your existing code I would straemline it a bit like this...

Code: 
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim ws As Worksheet, currentRow As Long

    Application.EnableEvents = False
    If TypeName(sh) <> "Worksheet" Then GoTo exitEnabled
    Set ws = sh
    With ws
        If Target.Cells.Count = 1 Then
            If Not Intersect(Target, .Range("A:A")) Is Nothing Then
                currentRow = Target.Row
                Select Case UCase(Target)
                    Case "C"        ' COMPLIMENTARY ERROR '
                        MsgBox "Please enter a more descriptive Complimentary Code", , "COMPLIMENTARY CODE ERROR"
                        .Range("M" & currentRow).Font.ColorIndex = 0
                        .Range("Q" & currentRow).Value = "ERROR"
                        .Range("Q" & currentRow).Interior.ColorIndex = 3
                        .Range("R" & currentRow).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "ERROR"
                        .Range("U" & currentRow).Interior.ColorIndex = 3
                        .Range("V" & currentRow) = ""
                        .Range("W" & currentRow) = ""
                        .Range("X" & currentRow) = ""
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow) = ""
                    Case "CO"       ' COMPLIMENTARY OTHER EQUIPMENT '
                        .Range("M" & currentRow).Font.ColorIndex = 45
                        .Range("Q" & currentRow).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                        .Range("Q" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("R" & currentRow).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "COMP-O"
                        .Range("U" & currentRow).Interior.ColorIndex = 45
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).Value = "COMP-O"
                        .Range("X" & currentRow) = ""
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow).FormulaR1C1 = "=CompO((rc[-9],rc[-11],rc[-13]),(rc[-10],rc[-6]))"
                        .Range("AA" & currentRow) = ""
                    Case "CI"       ' COMPLIMENTARY INTERNET '
                        .Range("M" & currentRow).Font.ColorIndex = 41
                        .Range("Q" & currentRow).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                        .Range("Q" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("R" & currentRow).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "COMP-I"
                        .Range("U" & currentRow).Interior.ColorIndex = 41
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).Value = "COMP-I"
                        .Range("X" & currentRow) = ""
                        .Range("Y" & currentRow).FormulaR1C1 = "=CompI(rc[-5]:rc[-8])"
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow) = ""
                    Case "CD"       ' COMPLIMENTARY DISCOUNT '
                        .Range("M" & currentRow).Font.ColorIndex = 41
                        .Range("Q" & currentRow).Value = "COMP-D"
                        .Range("Q" & currentRow).Interior.ColorIndex = 41
                        .Range("R" & currentRow).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "COMP-D"
                        .Range("U" & currentRow).Interior.ColorIndex = 41
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).Value = "COMP-D"
                        .Range("X" & currentRow) = ""
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow).FormulaR1C1 = "=CompD((rc[-9],rc[-11],rc[-13],rc[-7]),(rc[-10],rc[-6]))"
                        .Range("AA" & currentRow) = ""
                    Case "E"        ' TAX EXEMPT '
                        .Range("M" & currentRow).Font.ColorIndex = 0
                        .Range("Q" & currentRow).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                        .Range("Q" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("R" & currentRow).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "EXEMPT"
                        .Range("U" & currentRow).Interior.ColorIndex = 4
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                        .Range("X" & currentRow).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow).FormulaR1C1 = "=Tax(rc[-7]:rc[-9])"
                    Case "D"        ' DISCOUNT '
                        .Range("M" & currentRow).Font.ColorIndex = 0
                        .Range("Q" & currentRow).Value = "DISC"
                        .Range("Q" & currentRow).Interior.ColorIndex = 44
                        .Range("R" & currentRow).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).FormulaR1C1 = "=Tax(rc[-1]:rc[-3])"
                        .Range("U" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                        .Range("X" & currentRow).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow) = ""
                    Case "DE"       ' DISCOUNT TAX EXEMPT '
                        .Range("M" & currentRow).Font.ColorIndex = 0
                        .Range("Q" & currentRow).Value = "DISC"
                        .Range("Q" & currentRow).Interior.ColorIndex = 44
                        .Range("R" & currentRow).FormulaR1C1 = "=equipDiscSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).Value = "EXEMPT"
                        .Range("U" & currentRow).Interior.ColorIndex = 4
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                        .Range("X" & currentRow).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow).FormulaR1C1 = "=Tax(rc[-7]:rc[-9])"
                    Case Else
                        .Range("M" & currentRow).Font.ColorIndex = 0
                        .Range("Q" & currentRow).FormulaR1C1 = "=svcC(rc[-4]:rc[-3])"
                        .Range("Q" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("R" & currentRow).FormulaR1C1 = "=equipSUB(rc[-5],rc[-3],rc[-2])"
                        .Range("U" & currentRow).FormulaR1C1 = "=Tax(rc[-1]:rc[-3])"
                        .Range("U" & currentRow).Interior.ColorIndex = xlColorIndexNone
                        .Range("V" & currentRow).FormulaR1C1 = "=AVTotal(rc[-4]:rc[-1])"
                        .Range("W" & currentRow).FormulaR1C1 = "=HAVS(rc[-2]:rc[-5])"
                        .Range("X" & currentRow).FormulaR1C1 = "=Hotel(rc[-3]:rc[-6])"
                        .Range("Y" & currentRow) = ""
                        .Range("Z" & currentRow) = ""
                        .Range("AA" & currentRow) = ""
                End Select
            End If  'Not Intersect(Target, .Range("A:A")) Is Nothing
            
        End If      'Target.Cells.Count = 1
    End With        'ws
exitEnabled:
    Application.EnableEvents = True
        
End Sub

First of all, remember that the event returns a reference to the worksheet, so you don't have to use the ActiveSheet Object. The sh Object passed into the event handler is of type Object because it could be a WorkSheet or a Chart, I forced it to be a WorkSheet by invoking a local variable. This is more efficient and also more convenient for intellitype prompting.

Secondly, I eliminated the multiple resolutions of the target row by creating a local variable.

Thirdly, I used a With statement to eliminate all of the WorkSheet.Range references, which are also inefficient.

I also used the Ucase function to streamline the Select Statement.
 
Last edited:
Upvote 0
Hi Cool blue,

Those are good suggestions for working with the existing approach.

One part of your modified code is interesting...
Code: 
If TypeName(sh) <> "Worksheet" Then GoTo exitEnabled

Do you know of a scenario where that check is necessary?
As far as I know, only a Worksheet can trigger this event (not a Chart).

Since they chose to use Object instead of Worksheet as the data type for sh...I might be missing something.
 
Upvote 0
Hi Jerry, thanks, my advice was of course in addition to yours which (yours) is an elegant generalisation of the OP's approach and a succinct (and necessary) extensions to allow for deployment as an add-in.:)

In answer to your question, I wanted to cast the object to take advantage of early binding and I didn't really think too much about the possible types that would be passed in.
The principle I was operating on, in dropping in the line you mentioned, was just to eliminate ALL ambiguity to avoid any possibility of a run-time error, now or in the future. Risk management I guess you could call it, or "defensive programing"?

Another way to put it is that rather than assume that I fully understand how VBA works, I assume that I'm not sure. Which, for me, is generally a fair assumption! ;)

I think the reason they chose to use the Object Type is because the Charts and WorkSheets are both members of the Sheets Collection and this is the object that is referenced by the Application Object in throwing this event.

After reading your comment I got conscientious and checked and confirmed that you are of course correct, but, having said that, I would remain defensive and conservative none the less. :)
 
Upvote 0
Thinking a bit more about it (longer than the edit limit!), I guess I should add that I have not confirmed that there are no combination of mouse clicks and key strokes on any type of Chart Sheet in any version of excel that could lead to a Sheet Change Event firing from a Chart Sheet. And I'm not aware of any definitive specification on any aspect of VBA from MS, so I guess that's why I tend to be more defensive than inquisitive at times :p
 
Upvote 0
Jerry,

Thank you for the input, I will try and add this. I do have an add-in already but it is just driving macros for creating the monthly workbooks from a template and allowing users to verify totals (change color index of cell). I like both of your suggestions, how would I use the template to copy the formulas? Since I need the sheet to be able to adjust to different row lengths and refer to other cells how would the referencing work?

I definitely want to keep the code in the add-in if possible, so I will try the app call later today.

Cool Blue,
Thank you for helping streamline this. The changes you made all make sense to me, I am new to coding so the input from both you and Jerry was greatly appreciated. I have not been able to sit back down to test these codes yet with the New year but hope to later today. My goal is to take the combination and be able to integrate it into my add-in.

Happy New Year to the both of you, thank you for your help I will update as soon as I have tried these later.
 
Last edited:
Upvote 0
Jerry and Cool Blue,
Thank you for your help, the codes that you provided worked like a charm. We are up and running a lot smoother than it was before.

I was hoping you could help me with one other question though. Is there any way to limit the target that it is looking at to just a couple named sheets. Since it is living in the add-in whenever the add in is loaded and you working cell A you can get issues with the Else command. Can I limit the Target to only looking in sheets named 01, 02, 03,... (The sheets are named by the day of the month)?


Thank you again for all your help!
Corey
 
Upvote 0
The event cannot be focused on individual sheets if that's what you mean, but you can just test the name of the sh object at the start and exit if it's not included. Probably best to do it with a Select Case Statement on sh.Name
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
SUMIFS in VBA - return values on multiple columns dependent on a cell header
Replies
2
Views
421
ramona_manea
R
M
  • Question
Cleaning up Code
Replies
0
Views
707
MHamid
M
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
191
gtd526
gtd526
M
  • Question
Extract data from Word to Excel
Replies
1
Views
1K
MHamid
M
J
  • Question
filtering during vba take way to long
2 3
Replies
28
Views
2K
krzcho
K

Forum statistics

Threads
1,217,381
Messages
6,136,228
Members
450,000
Latest member
jgp19

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