Conditional Workbook_sheetchange

[hroberts29405]

OK I have gotten this to work configured like this. This is a combination of many different codes from this site:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$E$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$G$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$I$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$K$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$M$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$O$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$Q$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$S$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$U$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$W$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$Y$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$AA$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$AC$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$AE$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$AG$1" <SPAN style="color:#00007F">Or</SPAN> Target.Address = "$AI$1" <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> SameName

        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        
            ws.Activate
            
                <SPAN style="color:#00007F">If</SPAN> Range("A1") <> "" <SPAN style="color:#00007F">Then</SPAN>
                
                    ws.Name = Range("A1")
                    
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                
                <SPAN style="color:#00007F">If</SPAN> Range("A1") = "" <SPAN style="color:#00007F">Then</SPAN>
                
                    <SPAN style="color:#00007F">GoTo</SPAN> NoName
                    
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                
        <SPAN style="color:#00007F">Next</SPAN> ws
        
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
SameName:

    Range("A1").Value = InputBox("You can't have two sheets with the same name! Please enter a new name!")
    
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
NoName:

    Range("A1").Value = InputBox("You can't have a sheet without name! Please enter a name!")

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

Now can someone tell me why or what I need to do to make the IF statement run only if those cells change on a specific sheet, and also when it does run, how to make it return me back to the sheet I was working on

Thanks for this wonderful forum, and all of your help on this matter.

Harold

 

[Legacy 98055]

Hi Harold.

If your code only needs to run from a specific worksheet then it would be a better idea to simply place it in the worksheet change event procedure for that worksheet. You don't need workbook scope unless I'm missing something...?

Tom

 

[hroberts29405]

I want it to check the name of all tabs, but only do it if cells in a specific work sheet are changed

Does this make since?

Harold

 

[Legacy 98055]

We'll discuss optimization technique some other time. Here's your solution...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim MyActiveSheet As Worksheet
'make sure it's a worksheet
If Not (TypeOf Sh Is Worksheet) Then Exit Sub

If ActiveSheet.Name <> "Contractor Totals" then Exit Sub
Set MyActiveSheet = ActiveSheet

If Target.Address = "$A$1" Or Target.Address = "$E$1" Or Target.Address = "$G$1" Or Target.Address = "$I$1" Or Target.Address = "$K$1" Or Target.Address = "$M$1" Or Target.Address = "$O$1" Or Target.Address = "$Q$1" Or Target.Address = "$S$1" Or Target.Address = "$U$1" Or Target.Address = "$W$1" Or Target.Address = "$Y$1" Or Target.Address = "$AA$1" Or Target.Address = "$AC$1" Or Target.Address = "$AE$1" Or Target.Address = "$AG$1" Or Target.Address = "$AI$1" Then
Dim ws As Worksheet
    
    On Error GoTo SameName

        For Each ws In ActiveWorkbook.Worksheets
        
            ws.Activate
            
                If Range("A1") <> "" Then
                
                    ws.Name = Range("A1")
                    
                End If
                
                If Range("A1") = "" Then
                
                    GoTo NoName
                    
                End If
                
        Next ws
    
    MyActiveSheet.Select
    Exit Sub
    
SameName:

    Range("A1").Value = InputBox("You can't have two sheets with the same name! Please enter a new name!")
    
    MyActiveSheet.Select
    Exit Sub
    
NoName:

    Range("A1").Value = InputBox("You can't have a sheet without name! Please enter a name!")
    MyActiveSheet.Select
End If

End Sub

 

[hroberts29405]

What if I don't want to use the currently active sheet only on 1 specific sheet. For example I have a sheet that is entitled employees, When I put info into this sheet I do not want it to run the script, however when I input info into the sheet titled Sub Contractor Totals I do want it to check, I then want it to return me back to the Sub Contractor Total worksheet, that way I can continue to input the rest of the data.

Harold

 

[Legacy 98055]

See my edit Harold...

 

[hroberts29405]

Right Click you are a genius, Thank-you very much for your assistance on this. This will save me a lot of work on a reqular basis.

Again,

THANKS

 

[BJungheim]

If Target.Address = "$A$1" Or Target.Address = "$E$1" Or Target.Address = "$G$1" Or Target.Address = "$I$1" Or Target.Address = "$K$1" Or Target.Address = "$M$1" Or Target.Address = "$O$1" Or Target.Address = "$Q$1" Or Target.Address = "$S$1" Or Target.Address = "$U$1" Or Target.Address = "$W$1" Or Target.Address = "$Y$1" Or Target.Address = "$AA$1" Or Target.Address = "$AC$1" Or Target.Address = "$AE$1" Or Target.Address = "$AG$1" Or Target.Address = "$AI$1" Then

can be simplified as:

If Intersect(Target, Range("A1,E1,G1,I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1,AE1,AG1,AI1")) Is Nothing Then Exit Sub