hroberts29405
New Member
- Joined
- Dec 4, 2005
- Messages
- 11
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
<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