Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Otto.......Unprotect and re-Protect
Thanks Otto...........
I wanted to know how to Unprotect and re-Protect a sheet in VBA and I looked up this old response of yours and it does the trick just fine...........no waiting............thanks for being there....... Vaya con Dios, Chuck, CABGx3 Unprotect the sheet before you start copying/pasting and protect it when you're through. Use the following code. HTH Otto Sheets("SheetName").Protect Password:="rob1" Sheets("SheetName").Unprotect Password:="rob1" "gav meredith" wrote in message ... Hi, i have a code that copies and pasted data based on values in a cell however, i want this data to be protected once it is pasted to the new worksheet. If i protect the worksheet the data is pasting to, the data will not paste. (cells protected). How can i get around this. This is my code: Private Sub Commandbutton2_click() CopyData Range("E9:E94"), "OPTIONS" End Sub Private Sub CopyData2(rngE As Range, Target As String) Dim rng As Range, cell As Range Dim rng1 As Range, rng2 As Range Dim rng3 As Range Dim nrow As Long, rw As Long Dim sh As Worksheet nrow = Application.CountIf(rngE, "0") If nrow = 0 Then Exit Sub Set sh = Worksheets("Quote2") Set rng = sh.Columns(1).Find(What:=Target, _ After:=sh.Range("A1"), _ LookIn:=xlFormulas, _ Lookat:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ' Set rng1 = sh.Columns(1).FindNext(rng) ' Set rng3 = sh.Range(rng, rng1) Set rng3 = rng rng.Offset(1, 0).ClearContents If Application.CountA(rng3) 2 Then ' Set rng3 = rng1.End(xlUp).Offset(2, 0) Else Set rng3 = rng.Offset(2, 0) End If rw = rng3.Row rng3.Resize(nrow * 2, 1).EntireRow.Insert For Each cell In rngE If Not IsEmpty(cell) Then If IsNumeric(cell) Then If cell 0 Then Cells(cell.Row, 1).Range("A9,B9").Copy _ Destination:=sh.Cells(rw, 1) rw = rw + 2 End If End If End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Otto.......Unprotect and re-Protect
u try to use programe for password recovery e.g.
Advanced office password recovery or Advance VBA password recovery, the former program is use to tickle office password include word & excel while later is only unprotect VBA password, u can search in google.com and find out these program with trial version (3 characters) Regards, William "CLR" ¦b¶l¥ó ¤¤¼¶¼g... Thanks Otto........... I wanted to know how to Unprotect and re-Protect a sheet in VBA and I looked up this old response of yours and it does the trick just fine...........no waiting............thanks for being there....... Vaya con Dios, Chuck, CABGx3 Unprotect the sheet before you start copying/pasting and protect it when you're through. Use the following code. HTH Otto Sheets("SheetName").Protect Password:="rob1" Sheets("SheetName").Unprotect Password:="rob1" "gav meredith" wrote in message ... Hi, i have a code that copies and pasted data based on values in a cell however, i want this data to be protected once it is pasted to the new worksheet. If i protect the worksheet the data is pasting to, the data will not paste. (cells protected). How can i get around this. This is my code: Private Sub Commandbutton2_click() CopyData Range("E9:E94"), "OPTIONS" End Sub Private Sub CopyData2(rngE As Range, Target As String) Dim rng As Range, cell As Range Dim rng1 As Range, rng2 As Range Dim rng3 As Range Dim nrow As Long, rw As Long Dim sh As Worksheet nrow = Application.CountIf(rngE, "0") If nrow = 0 Then Exit Sub Set sh = Worksheets("Quote2") Set rng = sh.Columns(1).Find(What:=Target, _ After:=sh.Range("A1"), _ LookIn:=xlFormulas, _ Lookat:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ' Set rng1 = sh.Columns(1).FindNext(rng) ' Set rng3 = sh.Range(rng, rng1) Set rng3 = rng rng.Offset(1, 0).ClearContents If Application.CountA(rng3) 2 Then ' Set rng3 = rng1.End(xlUp).Offset(2, 0) Else Set rng3 = rng.Offset(2, 0) End If rw = rng3.Row rng3.Resize(nrow * 2, 1).EntireRow.Insert For Each cell In rngE If Not IsEmpty(cell) Then If IsNumeric(cell) Then If cell 0 Then Cells(cell.Row, 1).Range("A9,B9").Copy _ Destination:=sh.Cells(rw, 1) rw = rw + 2 End If End If End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect, unprotect | Excel Discussion (Misc queries) | |||
Protect/ unprotect worksheet | Excel Discussion (Misc queries) | |||
Password - Protect, UnProtect | Excel Programming | |||
Is there any way that you can protect or unprotect a group of wor. | Excel Discussion (Misc queries) | |||
protect / unprotect worksheets using VBA | Excel Programming |