PDA

View Full Version : Solved: Please check one line of my code



BENSON
12-19-2007, 11:48 PM
I have a work book with seven sheets labeled MON TUES WED THURS SAT SUN MON.The code below we only work if it runs on a Wednesday the line of code which seems to be the problem is


With Worksheets(Format(Weekday(Date), "ddd"))


The whole code is below

Private Sub Workbook_Open()
With Worksheets(Format(Weekday(Date), "ddd"))
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Date
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wSheet As Worksheet
Application.ScreenUpdating = False
For Each wSheet In Worksheets



Next



Dim Arr, Dy As Long
Dim Rng As Range, cell As Range
Dim Start As Boolean
Dim Prompt As String, RngStr As String

Prompt = "PLEASE CHECK YOUR DATA ENSURING ALL REQUIRED " & _
"CELLS ARE COMPLETE." & vbCrLf & "YOU WILL NOT BE ABLE " & _
"TO CLOSE OR SAVE THE DAILY REPORT UNTIL ALL THE REQUIRED CELLS ARE FILLED " & _
"OUT COMPLETELY. " & vbCrLf & vbCrLf & _
"THE CELLS LISTED BELOW CONTAIN NO DATA AND HAVE BEEN HIGHLIGHTED RED:" _
& vbCrLf & vbCrLf

Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = 0 To Weekday(Now, vbTuesday) - 1
With Worksheets(Format(Weekday(Date), "ddd"))
Start = True
Set Rng = TgtRange(.Name)
'highlights the blank cells
For Each cell In Rng
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 3 '** color red
If Start Then RngStr = RngStr & cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & cell.Address(False, False) & " , "
Else
cell.Interior.ColorIndex = 0 '** NO color

End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 0)
End With
Next
If RngStr <> "" Then
MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
Cancel = True
Else
'saves the changes before closing
ThisWorkbook.Save
Cancel = False
End If

For Each wSheet In Worksheets

Next
Application.ScreenUpdating = True
End Sub

Function TgtRange(ShtName As String) As Range
With Sheets(ShtName)
Select Case ShtName

Case "TUES"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "WED"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "THURS"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "FRI"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "SAT"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "SUN"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "MON"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
End Select
End With
End Function


Thanks for any help

Andy Pope
12-20-2007, 02:00 AM
Today it will problem only run on sheet THU ;)

Within the loop you are using the sheet based on todays date rather than an element from the array. So each time it will be the same sheet.
Use the loop index variable Dy when constructing the sheet name.

Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = 0 To Weekday(Now, vbTuesday) - 1
With Worksheets(Format( Dy , "ddd"))

' force upper case so the select in TgtRange will work regardless of actual sheet name case.
Set Rng = TgtRange(ucase(.Name))



The for loop above goes from zero to the value for the current day. You may want to process all the days when the workbook closes. If so then use


Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = lbound(arr) to ubound(arr)
With Worksheets(Format( arr(Dy) , "ddd"))


Or if you only want to do current day remove the loop and just set the variable Dy to the correct value.

Bob Phillips
12-20-2007, 02:55 AM
Seems a bit of overkill Andy



For dy = LBound(arr) To UBound(arr)
With Worksheets(Format(arr(dy), "ddd"))


all you need is



For dy = LBound(arr) To UBound(arr)
With Worksheets(arr(dy))

Andy Pope
12-20-2007, 03:21 AM
Your right Bob, it is actual completely redundent ;)
The value being formatted would be "THU" so actually does not require formatting.

BENSON
12-20-2007, 06:16 AM
Thanks for the help, the closing part of the macro works fine.However on opening the date gets posted in this instance ( To day being Thursday 20/12/07 ) into sheets ( SAT SUN MON) and not just THURS

THANKS

Bob Phillips
12-20-2007, 06:21 AM
Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Now, vbTuesday))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub

rory
12-20-2007, 06:22 AM
What code did you put in the Open event? Incidentally, I think you would find life easier if you changed the sheet names to 3 characters. Then formatting as "ddd" would actually match - i.e. MON, TUE, WED, THU, FRI, SAT, SUN
Also, your original post had two MON sheets and no FRI - was that correct?

BENSON
12-20-2007, 06:50 AM
Have just copied code from El Xid this pastes the the word "FRI" into the friday sheet. Yes my original post I did make a mistake ,it should read TUES through to MON not to Mondays

Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Now, vbTuesday))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub

Bob Phillips
12-20-2007, 06:57 AM
Sorry, I omitted catering for array bounds



Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Date, vbTuesday) - 1 + LBound(Arr))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub

BENSON
12-20-2007, 07:12 AM
Nearly there,it pastes to the correct worksheet in this case THURS is it possible to show the date as well as the word "THUR" 1i: THURS 20th DEC 07

Thanks for all the help so far

Norie
12-20-2007, 09:59 AM
Instead of Weekday(Date) just use Date.

If you used Weekday(Date) today the result would be 5.

Now since Excel stores dates as integers that's the equivalent of 5th January 1900 which was a Thursday but that could just be a coincidence.

unmarkedhelicopter
12-21-2007, 04:44 AM
Norie, Ah ! be careful Mac's interpret dates differently (I'm probably making this up but I think they start at 1908), though I've yet to see a definitive statement from a mac user. On this or date interpretation differences. To me this seems idiotic as if I write a date which is stored as 412 then whether it gets opened on a PC or a MAC I'd still like to see the same date. Maybe it's an urban legend !!! ... :think:

Any MAC user PLEASE Comment !!!

Edit: I wasn't making it up, but I did get the date wrong, MAC's assume 1904/01/01 as 1 not 1900/01/01

Edit2: Just to stop you PC user's getting smug (about what I don't know) at least the above "feature" stops them having a bug which tells you that 1900/02/29 is a valid day, funny cos 1900 is NOT a leap year.

Bob Phillips
12-21-2007, 04:51 AM
My posts just don't seem to be stcking at the moment. I did this yesterday, but it's not here, so I'll repeat it



Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Date, vbTuesday) - 1 + LBound(Arr))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy & " " & _
Format(Date, "d mmmm yyyy")
End With
End Sub

Norie
12-21-2007, 08:34 AM
unmarkedhelicopter

That's sort of my point, using just Date will avoid any problems as to how dates are stored or where the start point is.

Like I said it's probably just luck that yesterday was Thursday and so was 5th January 1900.

BENSON
12-24-2007, 05:27 AM
WORKING PERFECT THANKS TO YOU ALL