Macro does not finish

[Ed Davis]

This macro stopped working on the "03" sheet it worked on the two sheets
before.
The Range "B1" in both sheets are a date the first sheet holds "01/08/09"
the first of the month.
the second sheet hold the date "31/08/09" the last day of the month.
All the sheets in between are day numbers "01" - "31".
It worked before when I put the start date as 2009,08,01
and the end date as 2009,08,31

Sub UnProtect_All_Sheets()
' Thanks to Dave Peterson for the below code.

Dim StartDate As Date
Dim EndDate As Date
Dim dCtr As Date
Set MyActCell = ActiveCell
Set MySelection = Selection

StartDate = Worksheets("01").Range("B1").Value
EndDate = Worksheets("Totals").Range("B1").Value
Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
For dCtr = StartDate To EndDate
With Worksheets(Format(dCtr, "dd"))
.Select
.Range("A1").Select
.Unprotect Password:="7135"
End With
Next dCtr
On Error GoTo 0
Application.Goto MySelection
MyActCell.Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Dave Peterson]

A few things for you to look at:

#1. How do you know it stops on the "03" worksheet?
That "on error resume next" line should be hiding any problem.


#2. Did you enter real dates in those cells--are you sure that it's not text
that look like dates.

I'd try:
=date(2009,08,01)
and
=date(2009,08,31)
in those two cells.


#3. If those things check out ok, try:

StartDate = Worksheets("01").Range("B1").Value2
EndDate = Worksheets("Totals").Range("B1").Value2

(Yep, .value2)

 

[Ed Davis]

The reason I know it stops there is because the first sheet that in not done
is "03" and every sheet after that
I have the following formula in that cell
adding 1 day to sheet "02" and the cells are formatted as date dd/mm/yy

='02'!$B$1+1

The totals sheet has the following formula.

=DATE(YEAR('01'!$B$1),MONTH('01'!$B$1)+1,0)
Giving me the last day of the month using the date in "01" B1

It worked fine without the ranges.

 

[Dave Peterson]

Comment that "on error resume next" line.

Run the macro. If you get an error, it may help diagnose the problem.

The reason I know it stops there is because the first sheet that in not done
is "03" and every sheet after that
I have the following formula in that cell
adding 1 day to sheet "02" and the cells are formatted as date dd/mm/yy

='02'!$B$1+1

The totals sheet has the following formula.

=DATE(YEAR('01'!$B$1),MONTH('01'!$B$1)+1,0)
Giving me the last day of the month using the date in "01" B1

It worked fine without the ranges.

 

[Ed Davis]

After commenting the line "on error resume next" it worked fine.
I have the same code in an "Unprotect macro" and that was doing the same
thing. That works also with the commented.

 

[Ed Davis]

Just tried it again and it only did 1 sheet "30".
That is with the on error commented out.



--
Thank You in Advance
Ed Davis

After commenting the line "on error resume next" it worked fine.
I have the same code in an "Unprotect macro" and that was doing the same
thing. That works also with the commented.

 

[Ed Davis]

I have tried it many times now and it does only 1 sheet. '30'


--
Thank You in Advance
Ed Davis

Just tried it again and it only did 1 sheet "30".
That is with the on error commented out.

 

[Dave Peterson]

What happened when you used the .value2 property?

Add this line right after you pick up the values from the worksheet:

msgbox format(startdate,"mmmm dd, yyyy") _
& vblf & format(EndDate, "mmmm dd yyyy")

What do you see in that message box.

You could use:
debug.print format(startdate,"mmmm dd, yyyy") _
& vblf & format(EndDate, "mmmm dd yyyy")

And copy|paste from the immediate window.

 

[Ed Davis]

I get the same date for both December 30 1899 not either of the correct
ones.


--
Thank You in Advance
Ed Davis

What happened when you used the .value2 property?

Add this line right after you pick up the values from the worksheet:

msgbox format(startdate,"mmmm dd, yyyy") _
& vblf & format(EndDate, "mmmm dd yyyy")

What do you see in that message box.

You could use:
debug.print format(startdate,"mmmm dd, yyyy") _
& vblf & format(EndDate, "mmmm dd yyyy")

And copy|paste from the immediate window.

 

[Dave Peterson]

That means that you're not looking at the correct cells.

Maybe the wrong worksheets--or maybe the wrong addresses. But something's
wrong.

 

[Ed Davis]

I have checked that and the cells are the correct ones.
I have the date for each sheet in cell B1, and also the total sheet.
I have even tried to reformat the cells and still get the same results.
If I put in the Macro code 2009,8,1 and 2009,8,31 it works.
I want to use this same sheet every month and do not want to have to
remember to go into the VBA code and change it.

I use the same type of code in several macros that open files using the same
range and it opens and save workbooks fine.

 

[Dave Peterson]

Did you put that debug.print line before or after the lines that got the two
dates?

If you put it after those lines, then you're not picking up the dates from the
correct cells.

If you put it before those lines, them move it after and try it again.

I have checked that and the cells are the correct ones.
I have the date for each sheet in cell B1, and also the total sheet.
I have even tried to reformat the cells and still get the same results.
If I put in the Macro code 2009,8,1 and 2009,8,31 it works.
I want to use this same sheet every month and do not want to have to
remember to go into the VBA code and change it.

I use the same type of code in several macros that open files using the same
range and it opens and save workbooks fine.

 

[Ed Davis]

I did not use the print.debug as I think it needs a printer as nothing
happened with it when I put it in.
The msgbox I put after and that is when I got the Dec, 30 1899

 

[Dave Peterson]

In the VBE, hit ctrl-g (to see the immediate window) and you'll see the
debug.print lines.

But Dec 30, 1899 is the value you'll see if you format 0 (or an empty cell) as a
date.

You're not picking up the right cells
or you're not picking up the right worksheet
or you're not picking up the right workbook.

I did not use the print.debug as I think it needs a printer as nothing
happened with it when I put it in.
The msgbox I put after and that is when I got the Dec, 30 1899

 

[Ed Davis]

I don't get it Dave, this time it worked perfect. I tried it three times
both the protect and unprotect.
All I did was put in the debug.print statement you gave me before and the
dates showed the correct dates also.
Thank you for being so patient with me and my problem.

 

[Dave Peterson]

The debug.print had nothing to do with the success or failure.

I'd keep an eye out for the problem recurring.

I don't get it Dave, this time it worked perfect. I tried it three times
both the protect and unprotect.
All I did was put in the debug.print statement you gave me before and the
dates showed the correct dates also.
Thank you for being so patient with me and my problem.

 

[Ed Davis]

I think I know what happened. I had several copies of the file and after
checking all of them I found that the start date and end date was commented
out on most of them.
I am so sorry I have deleted all the copies that were just trial copies.

Thank you very much for all your help.

 

[Dave Peterson]

Ahhh.

That makes more sense.

Glad it's working now.

I think I know what happened. I had several copies of the file and after
checking all of them I found that the start date and end date was commented
out on most of them.
I am so sorry I have deleted all the copies that were just trial copies.

Thank you very much for all your help.