PDA

View Full Version : Solved: strange problem-macro fails every ALTERNATE time!!



sunilmulay
10-17-2008, 07:12 PM
hi guys
i'm having a strange problem with an Macro that I've linked to a button. Every ALTERNATE time I click on the button, the macro fails, and every OTHER alternate time I click on it, it works!!

the code is here:

Sub SetBaseline01()
'
' SetBaseline01 Macro
' Macro recorded 6/10/2008 by Sunil Mulay
'
'
ActiveSheet.Unprotect Password:=pWord
Range("Z20:Z999").Select
Selection.Copy

Sheets("Stage01-Actuals").Select
ActiveSheet.Unprotect Password:=pWord
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect Password:=pWord

Sheets("Stage01-Timeline").Select
ActiveSheet.Unprotect Password:=pWord
Range("D78:BE78").Select
Selection.Copy
Range("D19:BE19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect Password:=pWord
Sheets("Stage01-Planning").Select
ActiveSheet.Protect Password:=pWord
End Sub


It fails (every alternate time) on the following code after line: "Range("H20").Select"

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

any ideas?

GTO
10-17-2008, 11:40 PM
Greetings,

Didn't quite catch what was erroring, but this appears to work.

Mark

Sub SetBaseline01_01()
Dim shtActuals As Worksheet
Dim shtTimeline As Worksheet
Dim shtPlanning As Worksheet
Dim wksWorksheet As Worksheet

Set shtActuals = Worksheets("Stage01-Actuals")
Set shtTimeline = Worksheets("Stage01-Timeline")
Set shtPlanning = Worksheets("Stage01-Planning")

For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Protect Password:=pWord, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
Next

shtPlanning.Range("Z20:Z999").Copy

shtActuals.Range("H20:H999") _
.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

With shtTimeline
.Range("D78:BE78").Copy

.Range("D19:BE19") _
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
End With
End Sub

malik641
10-18-2008, 12:08 AM
Hey,

That is weird. I found that if you unprotect Stage01-Actuals and then comment out the protection part of that sheet:

Option Explicit

Sub SetBaseline01()
'
' SetBaseline01 Macro
' Macro recorded 6/10/2008 by Sunil Mulay
'
'
Const pWord As String = vbNullString

ActiveSheet.Unprotect Password:=pWord
Range("Z20:Z999").Select
Selection.Copy

Sheets("Stage01-Actuals").Select
'ActiveSheet.Unprotect Password:=pWord
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'ActiveSheet.Protect Password:=pWord

Sheets("Stage01-Timeline").Select
ActiveSheet.Unprotect Password:=pWord
Range("D78:BE78").Select
Selection.Copy
Range("D19:BE19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Protect Password:=pWord
Sheets("Stage01-Planning").Select
ActiveSheet.Protect Password:=pWord
End Sub
That the code works everytime.


So what's been happening with you is that the code will unprotect the worksheet, you say "End" to stop the code and now the sheet Stage01-Actuals stays unprotected. The next time you do this it works because the sheet was initially unprotected, but SOMEHOW the code is reprotecting the worksheet when the code doesn't give you the error message.

It will error out everytime if the sheet Stage01-Actuals is protected every time you run the code.

I want to say I remember hearing a problem similar to this before, but I can't say for sure. Something about Excel has a problem with protection / unprotection of sheets when you "Select" them by code.

If I unprotect all the sheets before-hand, execute the copying, then re-protect the sheets the code works everytime:
Option Explicit

Sub SetBaseline01()
'
' SetBaseline01 Macro
' Macro recorded 6/10/2008 by Sunil Mulay
'
'
Const pWord As String = vbNullString

' unprotect sheets
Sheets("Stage01-Planning").Unprotect Password:=pWord
Sheets("Stage01-Actuals").Unprotect Password:=pWord
Sheets("Stage01-Timeline").Unprotect Password:=pWord

Range("Z20:Z999").Select
Selection.Copy

Sheets("Stage01-Actuals").Select
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Sheets("Stage01-Timeline").Select
Range("D78:BE78").Select
Selection.Copy
Range("D19:BE19").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Sheets("Stage01-Planning").Select


' re-protect sheets
Sheets("Stage01-Planning").Protect Password:=pWord
Sheets("Stage01-Actuals").Protect Password:=pWord
Sheets("Stage01-Timeline").Protect Password:=pWord
End Sub
And FYI, you can edit the macro a little more to make it more efficient. You don't have to use "Select" in your code. Also, we can make it that the screen doesn't "flicker" so much when we run the code. Knowing this, we can change the code a little more:

Option Explicit

Sub SetBaseline01()
'
' SetBaseline01 Macro
' Macro recorded 6/10/2008 by Sunil Mulay
'
'
' stop making the screen flicker
Application.ScreenUpdating = False

Const pWord As String = vbNullString

' unprotect sheets
Sheets("Stage01-Planning").Unprotect Password:=pWord
Sheets("Stage01-Actuals").Unprotect Password:=pWord
Sheets("Stage01-Timeline").Unprotect Password:=pWord

' COPYING - PASTING SECTION
Sheets("Stage01-Planning").Range("Z20:Z999").Copy

Sheets("Stage01-Actuals").Range("H20").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Sheets("Stage01-Timeline").Range("D78:BE78").Copy
Sheets("Stage01-Timeline").Range("D19:BE19").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


' re-protect sheets
Sheets("Stage01-Planning").Protect Password:=pWord
Sheets("Stage01-Actuals").Protect Password:=pWord
Sheets("Stage01-Timeline").Protect Password:=pWord

' reset the ScreenUpdating
Application.ScreenUpdating = True
End Sub
Hope this helps :)

EDIT: GTO beat me too it :) ah well

sunilmulay
10-18-2008, 12:21 AM
thanks a lot for the tips, and the VBA lesson!
It must be pretty obvious I'm a newbie!!
Thanks
S

malik641
10-18-2008, 12:25 AM
No problem. Glad to help out!

And even if you are a newbie, that's a problem with Excel. Your code should have worked.

GTO
10-18-2008, 12:32 AM
@Sunimulay: Glad to be of help and have a good weekend :-)

@malik641: Glad to meet you Joseph, and thanks for the chuckle, as I am the one who usually types too slow or am waiting on my POL (Poor ol' laptop)

Anyways, a question: I have noted that many seem to prefer unprotecting the worksheet(s) and reprotecting. Have you run into problems setting .Protect's 4rth arg (UserInterfaceOnly) to True? Just curious, as I've used this frequently, and haven't noted any goobers/glitches.

Have a great weekend!

Mark

malik641
10-18-2008, 12:44 AM
Hey Mark, glad to meet you too!

I don't use protection too often, and I have never used UserInterfaceOnly so I can't say I have seen anything yet. Although Sunimulay's problem should be the same for anybody because it applies to Protection / Unprotection in General, it doesn't matter which options you choose (I think you knew that, but just to be clear).

What do you use UserInterfaceOnly for? The help file has a crummy definition:


True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

GTO
10-18-2008, 01:00 AM
Hey Joseph,

I WAY agree that the help topic is, well... less than helpful. I seriously must have read it a dozen times before I "got" what it was saying. In short though, if you set it to True like:

For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Protect Password:=pWord, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
Next

...then the sheet(s) that it is applied to are protected from the user (as in it still stops user from entering data in a locked cell), but vals (or in this case, PasteSpecial) can still happen programatically! (I think I just mis-spelled that something terrible.)

So in a lot of cases, I never have to unprotect the sheet at all :thumb

Blonde moment as I cannot recall if you can change other attributes such as borders, but I think so.

Well, off to get a beer, but by golly, if there's not an article on it, I might just have my first tiny contribution, maybe?

Have a good one, I'm out...

Mark