If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
This directed to John Wilson/or anyone else
Hi Guys & Gals:
John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#2
|
|||
|
|||
This directed to John Wilson/or anyone else
I don't understand what you are really trying to do. But, the first thing
you need to do is take a look at the LEFT function.. "Radio848" wrote in message ... Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#3
|
|||
|
|||
This directed to John Wilson/or anyone else
Wayne,
Not sure what you changed on your original workbook. I do remember that I had originally set this up to work on sheets starting with 19 & 20 as you had sheets named as years (1999, 2000, 2001, etc.) and not on any other sheets. Coding for that was as follows: If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then What did you change your sheet names to and if this is a different workbook that the original one that I helped you with, what are you trying to make it do?? John Radio848 wrote: Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#4
|
|||
|
|||
This directed to John Wilson/or anyone else
John: Hello;
The macro is unchanged, with the exception that there are now 42 pages and the sheet tabs are named "4.04, 5.04, 6.04" etc. The tab names reflect month.year. This is a different workbook. Entitled "Electric" And is covers a period of three years. I realized that I would have to adjust the tabs names in the macro and the number of pages, but I failed .. For some reason the macro is forcing me to name each page, in groups of two. Is that clear? "John Wilson" wrote in message ... Wayne, Not sure what you changed on your original workbook. I do remember that I had originally set this up to work on sheets starting with 19 & 20 as you had sheets named as years (1999, 2000, 2001, etc.) and not on any other sheets. Coding for that was as follows: If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then What did you change your sheet names to and if this is a different workbook that the original one that I helped you with, what are you trying to make it do?? John Radio848 wrote: Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#5
|
|||
|
|||
This directed to John Wilson/or anyone else
Wayne,
From your original post in this thread.... If Left(ActiveSheet.Name, 42) = "4.04" is looking at the leftmost 42 characters of the sheet names??? You could use the "MID" function to try to find the "04" but it might be easier to do this another way. Remembering how your other workbook was set up, you only had a couple of sheets named differently than your data sheets. You could use something like: If Activesheet.Name = "Database" OR Activesheet.Name = "abc" Then Exit Sub Else ' place your code here End If The above would let the macro run on every sheet except "Database" and "abc" (you can add more "OR"'s if necessary). John Radio848 wrote: John: Hello; The macro is unchanged, with the exception that there are now 42 pages and the sheet tabs are named "4.04, 5.04, 6.04" etc. The tab names reflect month.year. This is a different workbook. Entitled "Electric" And is covers a period of three years. I realized that I would have to adjust the tabs names in the macro and the number of pages, but I failed .. For some reason the macro is forcing me to name each page, in groups of two. Is that clear? "John Wilson" wrote in message ... Wayne, Not sure what you changed on your original workbook. I do remember that I had originally set this up to work on sheets starting with 19 & 20 as you had sheets named as years (1999, 2000, 2001, etc.) and not on any other sheets. Coding for that was as follows: If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then What did you change your sheet names to and if this is a different workbook that the original one that I helped you with, what are you trying to make it do?? John Radio848 wrote: Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#6
|
|||
|
|||
This directed to John Wilson/or anyone else
John:
Figured it out. I wasn't putting in enough "OR" 's.... 'THERE WILL BE SEVERAL' TeeHee "Radio848" wrote in message . .. John: Hello; The macro is unchanged, with the exception that there are now 42 pages and the sheet tabs are named "4.04, 5.04, 6.04" etc. The tab names reflect month.year. This is a different workbook. Entitled "Electric" And is covers a period of three years. I realized that I would have to adjust the tabs names in the macro and the number of pages, but I failed .. For some reason the macro is forcing me to name each page, in groups of two. Is that clear? "John Wilson" wrote in message ... Wayne, Not sure what you changed on your original workbook. I do remember that I had originally set this up to work on sheets starting with 19 & 20 as you had sheets named as years (1999, 2000, 2001, etc.) and not on any other sheets. Coding for that was as follows: If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then What did you change your sheet names to and if this is a different workbook that the original one that I helped you with, what are you trying to make it do?? John Radio848 wrote: Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
#7
|
|||
|
|||
This directed to John Wilson/or anyone else
Thank you once again John..
"Radio848" wrote in message . .. John: Hello; The macro is unchanged, with the exception that there are now 42 pages and the sheet tabs are named "4.04, 5.04, 6.04" etc. The tab names reflect month.year. This is a different workbook. Entitled "Electric" And is covers a period of three years. I realized that I would have to adjust the tabs names in the macro and the number of pages, but I failed .. For some reason the macro is forcing me to name each page, in groups of two. Is that clear? "John Wilson" wrote in message ... Wayne, Not sure what you changed on your original workbook. I do remember that I had originally set this up to work on sheets starting with 19 & 20 as you had sheets named as years (1999, 2000, 2001, etc.) and not on any other sheets. Coding for that was as follows: If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then What did you change your sheet names to and if this is a different workbook that the original one that I helped you with, what are you trying to make it do?? John Radio848 wrote: Hi Guys & Gals: John provided me with the enclosed macro, (I have made modifications, so the problem isn't his fault) Problem: I can only get the enclosed to work on the first & last page. There are a total of 42 pages. I've tried doing the "First Page" - "Last Page", routine and that didn't work. The enclosed is supposed to be put in "This Workbook" module. It needs to make the date/time entry on each page at the given offset address. What am I doing wrong? Thanks!! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) = "3.07" Then If Not Intersect(Target, Range("i8:i46")) Is Nothing Then If Target.Count 1 Then Exit Sub If Target.Value "" Then If Target.Offset(0, 5) = "" Then Target.Offset(0, 5) = Now End If Else Target.Offset(0, 5) = "" End If End If End If End Sub |
Thread Tools | |
Display Modes | |
|
|