Calculating dates in reverse; always 'land on' a Friday

[wdwells]

David
The attached workbook has been revised as follows:

• Added a standard module "Action"
• Revised the Worksheet_Change procedure in the Calendar worksheet
• Added an optional argument to the Worksheet_Activate procedure in the Calendar worksheet
• Changed all of the font size on the Calendar worksheet to 10. this will make the validationdrop-down list more readable.

What is your problem regarding the unhiding of files? Are you unable to unprotect the sheet? You must do this prior to any hiding/unhiding or format changes.

[wdwells]

OOPS
Sorry David
>>Added an optional argument to the Worksheet_Activate procedure in the Calendar worksheet
Adding that argument was a mistake. The attached file has been corrected.

[dmcnab]

Good morning, Don...thank you for this--I was trying it out when I saw your update .... the email hyperlinking on the Calendar sheet is working fine now...the problem that remains is that I cannnot do some formatting (eg: if I want to hide col E or F, or AB:AJ, I cannot shade multiple the columns and hide multiple columns at once....I have to hide them individually....and once hidden, I cannot unhide them).....this happens even if it is not protected...and if it is protected, even once I unprotect the worksheet, it still doesn't allow this.....try it--hide column E and you will be unable to unhide it...and you can't hold the shift key down and move across multiple columsn......??..??

[wdwells]

[quote name='dmcnab' post='784135' date='11-Jul-2009 10:39']Good morning, Don...thank you for this--I was trying it out when I saw your update .... the email hyperlinking on the Calendar sheet is working fine now...the problem that remains is that I cannnot do some formatting (eg: if I want to hide col E or F, or AB:AJ, I cannot shade multiple the columns and hide multiple columns at once....I have to hide them individually....and once hidden, I cannot unhide them).....this happens even if it is not protected...and if it is protected, even once I unprotect the worksheet, it still doesn't allow this.....try it--hide column E and you will be unable to unhide it...and you can't hold the shift key down and move across multiple columsn......??..??[/quote]

At one point in the development I was concerned that I would be referring to the Selection, so I disallowed multiple cell selection. Cautious people these Loungers . On review I can see no need for this action. In the attached workbook I have:

• Removed the offending code from several modules; and
• corrected a Call command in the Workbook_Open procedure of the ThisWorkbook module.

Let me know how it goes.

[wdwells]

I noted that the borders on column N had a tendency to disappear. The attached workbook corrects this problem.

[dmcnab]

Hi Don....thanks for that...I willopen it and see if I can copy the code and insert it into my real workbook...have a question: it looks like you changed the code for columbns P, Q and U.....I have copied it below....you'll see references to AB13-1, AB13-2, AB13-3, AB13-4 etc....on the far right, I have bolded some text...should the one on the 3rd line be AB13-4 or should it be AB13-3....?

=IF(J13="","",
IF(OR(WEEKDAY(INDEX($AJ:$AJ,AB13-1))>WEEKDAY(Q13),WEEKDAY(INDEX($AJ:$AJ,AB13-1))=6),INDEX($AJ:$AJ,AB13-1),
IF(OR(WEEKDAY(INDEX($AJ:$AJ,AB13-2))>WEEKDAY(Q13),WEEKDAY(INDEX($AJ:$AJ,AB13-2))=6),INDEX($AJ:$AJ,AB13-2),
IF(OR(WEEKDAY(INDEX($AJ:$AJ,AB13-3))>WEEKDAY(Q13),WEEKDAY(INDEX($AJ:$AJ,AB13-3))=6),INDEX($AJ:$AJ,AB13-4),
IF(OR(WEEKDAY(INDEX($AJ:$AJ,AB13-4))>WEEKDAY(Q13),WEEKDAY(INDEX($AJ:$AJ,AB13-4))=6),INDEX($AJ:$AJ,AB13-4),
INDEX($AJ:$AJ,AB13-5))))))

And beyond this, I think there is something weird in col P,Q,U and W.....I extended the formula down the columns--here's why: if someone were booked on line 13, the deadlines on line 13 showed up in lines 14, 15, 16 etc as bookings were entered on those lines...however, if the booking on line 13 canceled, it removed all of the deadline dates on lines below....so, I figured that extending the formula from 13 to lines below would correct this -- however, it doesn't work (retunrs (VALUE) and I am wondering if it has something to do with the formula you used in col AG (Cal 1) where you start the formula on line 11 and maybe it should start on line 12 ??...??...I don't know but it seems like the formula in col AG all refer to a date in the line above..??......see retruned sample

[wdwells]

>> it looks like you changed the code for columbns P, Q and U
I refer back to para 5e of Explanation.doc provided to you here.

>> in col P,Q,U and W.....I extended the formula down
In doing this you will also need to extend down the formulae in columns AB:AE. This approach will slow down the calculation, though you might not notice it.

[dmcnab]

Hi Don....sorry to be so dense about some of this, but I am sort of losing my way here, I think.......I was having quite a bit of trouble trying to unravel some of the code you have been so good to do for me, and I always feel the need to understand it so that I can fix it or adjust it if something goes awry....anyway, I am back to where I was a day or so ago b/c the code that was in the last 2 or 3 files wasn't working (eg: it wouldn't print the Confirmation sheet), so that's why I went back to the last thing that worked as it should....I have written myself a bunch of macros for unrelated matters such as realigning the columns, or erasing the data that gets copied into the confirmation sheet etc etc.....I have straightened out all of my formula that calculate the deadline dates.....all of the macros are in "Module 1" in modules, including the 2 macros that you sent me that (I gather) relate to the printing of the Confirmation sheet....you called them macro7 and macro8. As far as calculating the deadline dates, on the Calendar sheet, I have right-clicked, selected VIEW CODE and copied the following:
[codebox]
Dim EntryVal As Variant

Option Explicit

Public Sub Worksheet_Activate()
If Selection.Cells.Count > 1 Then ActiveCell.Select
EntryVal = ActiveCell

End Sub

Private Sub Worksheet_Change(ByVal target As Range)
Dim lRow As Long
Dim isect As Range

If target = EntryVal Then Exit Sub

EntryVal = target

Set isect = Intersect(target, Range("v:v"))
If isect Is Nothing Then
Set isect = Intersect(target, Range("J:K"))
If isect Is Nothing Then Exit Sub
End If

lRow = target.Row
If Range("v" & lRow) = "" _
Or Range("J" & lRow) = "" _
Or Range("K" & lRow) = "" Then Exit Sub

With Sheets("Deadlines sheet")
.Range("d7") = Range("K" & lRow)
.Range("d10") = Range("J" & lRow)
.Range("d13") = Format(Range("E" & lRow), "ddd,")
.Range("e13") = Format(Range("e" & lRow), "mmmm d/yyyy")
.Range("h13") = Range("u" & lRow)
.Range("h18") = Format(Range("o" & lRow), "ddd, mmmm d/yyyy")
.Range("h24") = Format(Range("s" & lRow), "ddd, mmmm d/yyyy")
.Range("h30") = Format(Range("t" & lRow), "ddd, mmmm d/yyyy")
.Range("h49") = Format(Range("w" & lRow), "ddd, mmmm d/yyyy")
.Range("d54") = Range("v" & lRow)
.Range("i54") = Now()

Sheets("Deadlines sheet").Select
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
ActiveWindow.Zoom = 75
ActiveSheet.Range("A1").Select

If MsgBox("Print?", vbQuestion + vbYesNo) = vbYes Then _
.PrintOut Copies:=2, Collate:=True

Sheets("Calendar").Select
End With

End Sub
[/codebox]
You will recognize this as (slightly modified) code that does the deadlines calculations.....everything is working except for the hyperlinking..........is there a simpler way to do the hyperlinking?...I have redesigned part of the worksheet so that I have a name going into col L, phone # in col M, email address in col N......and provided for a 2nd name in col P, phone # in col Q and email address in col R.........I need the email addresses that will be drawn into col N and col R (using the formula you gave me) to be 'active'/hyperlinked so that I can click on them to send an email....any suggestions that are simple enough for me to dissect and understand..????

[wdwells]

[quote name='dmcnab' post='784159' date='11-Jul-2009 17:04']Hi Don....sorry to be so dense about some of this, but I am sort of losing my way here, I think.....[/quote]
David
I recommend that you go back and open the file which I sent with Post #20 (Return_to_David_column_fixed__version_4_.xls) and step through the code there to gain an understanding. Note: I do not recognize Macro7 or 8. You should be working with code from the "Action" module, SetHypLnk and PopulateConfSht procedures. These are both called by the Worksheet_Change procedure of the Calendar worksheet module.

I'm afraid that I cannot spend any more time on this today, but if you have trouble understanding any of the code feel free to ask specific questions about it--but pose the question in reference to the file which I sent with Post #20. and I will respond as soon as I can.

[dmcnab]

Hi Don...thank you for your replies....b/c I am not that familiar with VBA (I can make macros, but don't have that much experience with VBA code) I am trying to combine what I have assembled to date, in a sort-of VBA combo (???)....I am attaching the code that I have combined....at this point, if I use End With in line 45, and enter data in col AA, it seems to run properly and prints the Confirmation sheet........if I enter data in col AG, nothing happens. I have tried other 'qualifiers' but b/c my VBA knowledge is limited, I don't know the full range of 'if's that will make the 2dn part of the code run if data is entered in col ag.

I know that this is probably the long way around, but I think I have a better chance of understanding the functionality of it (anf adjusting if it need be) than if I try to use ACTION (I don't even know how to get to 'Action') or CallModule.Procedure (these are unknown to me)......anyway, I hope you'll see how I am trying to get to the end of this......thanks again

[wdwells]

[quote name='dmcnab' post='784251' date='12-Jul-2009 17:04']Hi Don...thank you for your replies....b/c I am not that familiar with VBA (I can make macros, but don't have that much experience with VBA code) I am trying to combine what I have assembled to date, in a sort-of VBA combo (???)....I am attaching the code that I have combined....at this point, if I use End With in line 45, and enter data in col AA, it seems to run properly and prints the Confirmation sheet........if I enter data in col AG, nothing happens. I have tried other 'qualifiers' but b/c my VBA knowledge is limited, I don't know the full range of 'if's that will make the 2dn part of the code run if data is entered in col ag.

I know that this is probably the long way around, but I think I have a better chance of understanding the functionality of it (anf adjusting if it need be) than if I try to use ACTION (I don't even know how to get to 'Action') or CallModule.Procedure (these are unknown to me)......anyway, I hope you'll see how I am trying to get to the end of this......thanks again[/quote]
Correct me if I am wrong and confirm if you agree but I believe that you want to:

• Print the Confirmation sheet if the change was made in: AA; J; K; or L and all of these cells are populated with the possible exception of K.
• Print the Daily Manifest sheet if the data was entered in AG; J; K; or L and all of these cells are populated with the possible exception of K.
• Print both sheets if the data was entered in J; K; or L and all four of the cells (J; L; AA; and AG) are populated

[wdwells]

On the wild assumption that my understanding (presented in the previous post), is correct try the following:

Change line 12:
From: If isect Is Nothing Then Exit Sub
To: If isect Is Nothing Then GoTo Pop_Mfest

Delete lines 45 and 47.

Replace line 49 with the label 'Pop_Mfest:' (without the quotes). The label must be hard up against the left margin.

H.T.H.

[dmcnab]

Hi Don....re: printing the Confirmaion sheet, I want to be able to have that happen if:
(a) there is a change to AA, and,
( data has been entered in col J,K,L and M

re: printing the Manifest, that happens if:
(a) there is a change to AG, and,
( data has been entered in col H, J, K, L, M, N, S, R, W, Y, Z, AC, AD


......I don't need to worry about printing both sheets simultaneously b/c that would never happen.........thanks...

[wdwells]

[quote name='dmcnab' post='784272' date='12-Jul-2009 20:52']Hi Don....re: printing the Confirmaion sheet, I want to be able to have that happen if:
(a) there is a change to AA, and,
( data has been entered in col J,K,L and M

re: printing the Manifest, that happens if:
(a) there is a change to AG, and,
( data has been entered in col H, J, K, L, M, N, S, R, W, Y, Z, AC, AD


......I don't need to worry about printing both sheets simultaneously b/c that would never happen...... ...thanks...[/quote]
Which if any of the following columns are populated by formula and therefore do not need to be tested for content? ( H, J, K, L, M, N, S, R, W, Y, Z, AC, AD)

[wdwells]

[quote name='dmcnab' post='784272' date='12-Jul-2009 20:52']......I don't need to worry about printing both sheets simultaneously b/c that would never happen...... ...thanks...[/quote]

What action should be taken if one of trhe manually entered fields are cleared?