PDA

View Full Version : Solved: WB + WS vba headache



joelle
04-13-2007, 03:37 PM
Dear Experts,
Now I dont like Friday 13th. So much to handle! Please help me.

I have a workbook with 4 worksheets ("one", "two", "three", "four") and I'd like to set an Auto_Run vba so that:

1. When the WB is open, the code will check to see if ws "one" is there. If yes, it checks cell A10 of ws "one". If A10 is equal to blank, it will run a submacro called "renew"
If ws "one" does not exist, it will do nothing

2. Then vba continues with doing the same thing with ws "two", "three", "four" meaning it will check to see ws "two" exist, if yes, it will check cell A10 of ws "two". If A10 is equal to blank, it will run a submacro called "renew" If ws "two" does not exist, it will do nothing.

3. vba will do the same thing with ws "three" and "four"

Is this possible? Many thanks.

mdmackillop
04-13-2007, 04:13 PM
Option Explicit
Private Sub Workbook_Open()
Dim Arr, a, sh As Worksheet
On Error Resume Next
Arr = Array("One", "Two", "Three", "Four")
For Each a In Arr
Set sh = Sheets(a)
If Not sh Is Nothing Then
If Err.Number = 9 Then
Err.Clear
Else
If sh.Range("A10") = "" Then renew a
End If
End If
Next
End Sub

Sub renew(a)
MsgBox a
End Sub

joelle
04-14-2007, 03:11 PM
Hello MD,
I work Saturday and I saw your postings earlier today to others.
Wow, thats very impressive !!!

Thank you for the donated code. It is beautiful. My team & I worked on this project earlier and majority decide to use the ws level vs wb level approach.
Even myself, I do not understand "arr" and we like to try something we can maintain later on if we are to make a little change to it.

So, I attach here a similar code for this project except that it is per ws, and I need help making the code run "whether or not" user clicks on WS "one" tab.
Right now, the code does not work if ws "one" is already the activate sheet when I save the file. Other words, it only works if user clicks on ws "one" (wb attached).
Your help is tremendous for this project of ours. :bow:

Private Sub Worksheet_Activate()

ActiveSheet.Range("A10").Select
If Range("A10").Value > 1 Then
Msgbox "OK"

Else
renewONE

End If
End Sub

Sub renewONE()

Dim exdate As Date
exdate = "8/31/2007"

' Please, how do I add 90 days to the exdate. Thanks

If Date > exdate Then
MsgBox "These forms have expired"
ThisWorkbook.Close

End If

End Sub

mdmackillop
04-14-2007, 03:37 PM
It looks like you need to store a date somewhere. As a suggestion, use the Document properties. This code will check for a date greater than 90 days beyond the "Date completed". This code will not be triggered on opening the workbook, only when Sheet 1 is activated after another sheet.

Private Sub Worksheet_Activate()
ActiveSheet.Range("A10").Select
If Range("A10").Value > 1 Then
Exit Sub
Else
renewONE
End If
End Sub

Sub renewONE()
Dim exdate As Date
exdate = ActiveWorkbook.CustomDocumentProperties("Date completed").Value
If Date - exdate > 90 Then
MsgBox "These forms have expired"
ThisWorkbook.Close
Else
MsgBox "Still Valid"
End If
End Sub

joelle
04-14-2007, 03:48 PM
Dear MD,

1. Is there a way to make the code in sheet "one" run without user having to click or activate it? Thats the very main purpose of this project.
Reason is user can delete other sheets that he does not need and saves just sheet "one" onto his disk. So of course, when he opens, sheet "one" is "preactivated". In this case, risk is the macro will never execute.
I know there is some ApplicationEnable event for WS so that the code residing in ws "one" for example will execute whether or not the ws "one" is activated or clicked on, but I dont know how to apply it.

2. About the exdate + xx days, I made it work by changing that piece of code to:
If Date > exdate + 4 Then
Msgbox "These forms have expired" I tested and this piece works.


I truely appreciate your precious Saturday time helping me out with no. 1 above.
Thank you Big dog.

mdmackillop
04-14-2007, 04:09 PM
You could add this code to each sheet. I'm not clear about adding 90 days to the date. If this is hard coded, you can add the date required; If not where does the date come from.

Option Explicit
Const ExDate = "8/31/2007"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Renew
End Sub

Private Sub Renew()
If Range("A10").Value > 1 Then Exit Sub

If Date - DateValue(ExDate) > 90 Then
MsgBox "These forms have expired"
ThisWorkbook.Close
End If
End Sub

joelle
04-14-2007, 09:26 PM
Hello MD,
Yes, I have now copied your 2nd vba below to each of my ws with an expiration set but it does seem to work.
1. I blank out cell A10 to strigger the expiration but nothing happens.
2. Also I change "exit sub" (line 8) to a msg to test if the code run without having to click on ws "one" but nothing happens.
Pls note that I do not have line number from the vba; I put them here so you can locate which line is which.

I hope you can help me out of this black tunnel. Many thanks.
(wb attached)


1. Option Explicit
2. Const ExDate = "8/31/2005"

3.Private Sub Worksheet_SelectionChange(ByVal Target As Range)
4. Renew
5. End Sub

6. Private Sub Renew()
7. If Range("A10").Value > 1 Then
8. Msgbox ("OK")

If Date - DateValue(ExDate) > 90 Then
MsgBox "These forms have expired"
ThisWorkbook.Close
End If
End Sub

mdmackillop
04-15-2007, 12:25 AM
Add Line 9: End If
This macro is triggered when you change cell selection within a sheet. Deleting cell A10 will not in itself run the code, but it will run when you select any other cell.

joelle
04-16-2007, 10:06 AM
Hello MD,
Thank you much for all your posts with this subject.
It now works very good.
As always, thanks for caring for smaller dogs - Very appreciative.
nee