How to Unprotect a Worksheet in MS Excel 1

[PorscheGT2]

Hello all!

Before I go on, here's the code I'm working on:

ActiveSheet.Unprotect "GT2"

Dim BASE, RX, BUILDUP, PRES, QSUM, RIB, SQ, BASEDIR, RXDIR, _
BUILDUPDIR, PRESDIR, QSUMDIR, RIBDIR, SQDIR

BASE = Range("b9")
BASEDIR = Dir("x:\" & BASE)
RX = Range("b12")
RXDIR = Dir("x:\" & RX)
BUILDUP = Range("b15")
BUILDUPDIR = Dir("x:\" & BUILDUP)
PRES = Range("b18")
PRESDIR = Dir("x:\" & PRES)
QSUM = Range("b21")
QSUMDIR = Dir("x:\" & QSUM)
RIB = Range("b24")
RIBDIR = Dir("x:\" & RIB)
SQ = Range("b27")
SQDIR = Dir("x:\" & SQ)

If BASEDIR = "" Then
Range("i9").Value = "File Not Found!"
Else: Range("i9").Value = "OK!"
End If

If RXDIR = "" Then
Range("i12").Value = "File Not Found!"
Else: Range("i12").Value = "OK!"
End If

If BUILDUPDIR = "" Then
Range("i15").Value = "File Not Found!"
Else: Range("i15").Value = "OK!"
End If

If PRESDIR = "" Then
Range("i18").Value = "File Not Found!"
Else: Range("i18").Value = "OK!"
End If

If QSUMDIR = "" Then
Range("i21").Value = "File Not Found!"
Else: Range("i21").Value = "OK!"
End If

If RIBDIR = "" Then
Range("i24").Value = "File Not Found!"
Else: Range("i24").Value = "OK!"
End If

If SQDIR = "" Then
Range("i27").Value = "File Not Found!"
Else: Range("i27").Value = "OK!"
End If

Range("a1").Select

ActiveSheet.Protect Password:="GT2"

End Sub


I am having a problem unprotecting the worksheet after protecting it. I am not sure if I'm using the right syntax or not but I tried to record a macro and it's giving me the same syntax. BTW, the macro is meant to prevent other users from modifying the worksheet, and what it should do is unprotect the worksheet, do the modifications specified above and reprotect it. Any help would be greatly appreciated. Thanks.

 

[SkipVought]

Hi,

Try,

ActiveSheet.UnProtect Password:="GT2"

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hello Skip,

Thanks for your reply, but I already tried that. I thought the syntax for both protect and unprotect would be the same but if you check the help menu, it gives you a different syntax for each method. By the way, the error I'm getting is:

Run Time Error 1004

Unprotect Method of Worksheet Class Failed.

Lemme know if you have any other solutions.

Thanks,

Marvin

 

[SkipVought]

ActiveSheet.Unprotect password:="GT2"

works!

Are you ABSOLUTELY SURE that the sheet in question is ACTIVE??? That's the problem!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hello Skip,

I'm so sorry, I forgot to mention I'm making this macro work from a commandbutton. Sure, it does work if you run it as a macro, but from a commandbutton, sure does give me that error.

Thanks for your help!

Marvin

 

[SkipVought]

Then Activeate the sheet and select a cell first.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hello Skip,

I just did that after I clicked on Submit and it works now! Thanks, I'll give you a star.

Marvin