Requesting help with tweaking my macro

Hello

Would someone please help me in tweaking this macro? I asked for help with this on another forum [ here ] but for some reason the person helping me said I was taking advantage of their kindness and I most certainly was not.

I simply asked them to tell me where I need to alter the macro shown below to suit my needs since he never used the information I provided regarding sheets/column/cell references.

I'm a novice when it comes to this and sometimes I'm not even sure of the correct terminology to use so I hope someone will please help me.


Original Code = My comments in Bold Red

Notes to the code

In this code we assume

1- Completion Date is in column B in the range B19:B32 – Pulling from sheets A-Z, Range AZ19:AZ50

2- Due Date is in column C on similar range C19:C32 – Pulls from hidden sheet “.” Range J14:J45

3- Item/compliance is in column A on similar range A19:A32 – Pulls from hidden sheet “.” Range C14:C45 OR Pulls from sheets A-Z, range AV19:AV50

4 - 26 of the total sheets on your workbook are named with the alphabet letters from A to Z - Do I have to update this in the code somewhere?

Replace the ranges in the code with the ones in your scenario -
Replace the messages according to your needs

''''***********************************************************

Private Sub Workbook_Open()

Dim CompletionRng As Range

Dim rng As Range

Dim DueDate As Range

Dim compliance As Range

Dim workingdays As Double

Dim Sh As Worksheet

Dim i As Integer



For i = 65 To 90 '' Loop thru sheets A to Z

Set Sh = Sheets(Chr(i))



With Sh

On Error GoTo getOut

Set CompletionRng = .Range("B19:B32").SpecialCells(xlCellTypeBlanks)

For Each rng In CompletionRng

Set DueDate = .Range("C" & rng.Row)

Set compliance = .Range("A" & rng.Row)

workingdays = Application.WorksheetFunction.NetworkDays(Date, DueDate)

Select Case workingdays

Case Is = 3, 10

MsgBox Prompt:="Alert:" & vbNewLine & "REF. COMPLIANCE: " & compliance & vbNewLine & "Due Date expires in " & workingdays & " working days", Buttons:=vbExclamation, Title:="DUE DATE REMINDER"

Case Is = 1

MsgBox Prompt:="THAT'S IT, you are DONE", Buttons:=vbCritical, Title:="DUE DATE REMINDER"

End Select

Next rng



End With

Next i

getOut:



End Sub

''''''''''*******************************************************************

Please do not suggest conditional formatting. I do not need nor want to do that. This code will be used in 3 different workbooks that each contain approximately 60 sheets.

Please know that I appreciate any assistance I can get with this.

I have read your post three times and I am unclear as to what your question is. What does this code do, and what do you want it to do instead? If you can answer that concisely then that will be a good title, see below.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Most posts here are asking for help to tweak VBA. Can you please update it to give a hint as to what kind of tweak you are looking for?

Please take a moment to amend your thread title. Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Did it work?

Hello

Would someone please help me in tweaking this macro? I asked for help with this on another forum [ here ] but for some reason the person helping me said I was taking advantage of their kindness and I most certainly was not.

I simply asked them to tell me where I need to alter the macro shown below to suit my needs since he never used the information I provided regarding sheets/column/cell references.

I'm a novice when it comes to this and sometimes I'm not even sure of the correct terminology to use so I hope someone will please help me.


Original Code = My comments in Bold Red

Notes to the code

In this code we assume

1- Completion Date is in column B in the range B19:B32 – Pulling from sheets A-Z, Range AZ19:AZ50

2- Due Date is in column C on similar range C19:C32 – Pulls from hidden sheet “.” Range J14:J45

3- Item/compliance is in column A on similar range A19:A32 – Pulls from hidden sheet “.” Range C14:C45 OR Pulls from sheets A-Z, range AV19:AV50

4 - 26 of the total sheets on your workbook are named with the alphabet letters from A to Z - Do I have to update this in the code somewhere?

Replace the ranges in the code with the ones in your scenario -
Replace the messages according to your needs

Please Login or Register  to view this content.

Please do not suggest conditional formatting. I do not need nor want to do that. This code will be used in 3 different workbooks that each contain approximately 60 sheets.

Please know that I appreciate any assistance I can get with this.

Quote Originally Posted by 6StringJazzer View Post
I have read your post three times and I am unclear as to what your question is. What does this code do, and what do you want it to do instead? If you can answer that concisely then that will be a good title, see below.
Thank you! Please let me explain about this code.

I am responsible for maintaining secure data across 3 excel workbooks. These workbooks are the same, just that each one belongs to a specific team. Each workbook contains close to 60 sheets, many of which are hidden but necessary for pulling data.

Sheets A-Z are dedicated to individual team members and each sheet has a section dedicated to compliance tracking. The compliance name and due date are being pulled from a Master sheet named "." (just the period). The completion dates, however, will be different per individual since that information is manually input by each person upon completing compliances.

So this macro is supposed to run upon EITHER Open of the workbook OR when a person opens their sheet. It's supposed to check sheets A-Z for completion dates and due dates. If a due date is 10 business days away and no completion date has been entered, a pop-up reminder is supposed to appear for that individual's sheet. Example: Taylor's sheet is G and has a due date coming up in 10 days but he hasn't entered a completion date yet. When Taylor accesses his sheet, G, the pop up should appear. If a completion date has not been entered and the due date is now in 3 business days, the reminder should appear. Once Taylor enters a completion date, that pop-reminder should NOT appear for that particular compliance. He may have others that are due in X number of days requiring a reminder.

I hope this is making sense. I've included the snippets of the sheets below:


Snippet 1: This is to show how the compliance data looks in the workbook on both the Master sheet and sheets A-Z. The only part of this that is NOT included in the Master sheet is the completion date as it's not needed on the Master.
Attachment 709087


Snippet 2: This is to show the sheet tabs along the bottom of the workbook. Please keep in mind that are many hidden sheets. I only have the necessary ones visible for management and team purposes.
Attachment 709088

And the sheet/column/cell range references for my needs are:

1- Completion Date is in column B in the range B19:B32 – Pulling from sheets A-Z, Range AZ19:AZ50

2- Due Date is in column C on similar range C19:C32 – Pulls from hidden sheet “.” Range J14:J45

3- Item/compliance is in column A on similar range A19:A32 – Pulls from hidden sheet “.” Range C14:C45 OR Pulls from sheets A-Z, range AV19:AV50

4 - 26 of the total sheets on your workbook are named with the alphabet letters from A to Z - Do I have to update this in the code somewhere?