Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Script To Unprotect A Worksheet, Edit, Re-protect?


I'm using Excel to store some historical user events. I don't want a user to be able to open these sheets offline w/out a password, therefore I need protection on.

How can I do this in VBA?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Unprotect / Protect Workbook & Worksheet Using VBA For Excel'97
I just can't seem to get this to work. That is unprotecting / protecting workbooks & worksheets using VBA code.

I have tried "Activesheet.Unprotect ("backup") but get an error:
"Unprotect method of Worksheet class failed"

Is there a different way?

Thanks,

Protect/unprotect
i have got a template which i need to have protected.
what i need to do is find out how to unprotect in vba and so that i can write to it using some formula in vba, and then protect the document again.

Automatic Protect/Unprotect
I noticed that a lot of code is wasted on Protect/Unprotect functions when modifying protected worksheets and workbooks in code and would like to somehow automate this process. It would be nice if any changes done from code would be automatically allowed but any manual attempts would still be blocked by the worksheet/workbook passwords.

Anyone tried this before or has any good examples?

How To Protect And Unprotect Sheets ??
Hi Friends,

I am working on Excel File and VBA. Can anyone tell me what's wrong with following command:

Private Sub optMonday_Click()
    Sheet1.Unprotect
    'Now here I want to enter some values.....and in the next line want to re-protect sheet....
    Sheet1.Protect
End Sub

Tha above always displays an error as:

Method 'Unprotect' of object '_Worksheet' failed

similarly, Method 'Protect' of object '_Worksheet' failed 'when I try to Protect it......

Please let me know, how I can Protect and Unprotect the Sheet dynamically...

Thanx in advance.

Unprotect Then Protect W/o Clearing Contents
Is there a way to unprotect a protected Word Form, and then reprotect it in a macro without clearing out all the contents in the data fiels?

Also, how would you form an IF statement to check if the form is already protected. ie. If form1.doc.IsProtected Then ...

although I don't believe that is a built in function!

Protect & Unprotect Excel Sheets
Hi Guys,

I've found pretty much info on how to protect an Excel workbook etc. but with this protection it is still possible to change data on a sheet... what I want I can't really find. Hope you can help...

In my program in VB 6.0 I can open specific Excel files. If I want to put some info on it, the protection of the sheet(s) has to be disabled. After all the info is put on the sheet(s), the protection has be enabled.

How can I do this? I've found that you can use

PROTECT.DOCUMENT(contents, windows, password, objects, scenarios)

But I don't understand how I can use this code.

----

First step, if I open the wanted Excel workbook I say:

objExcel.Workbooks.Open (FileBestand.Path & "" & FileBestand.FileName)

The second step is to disable the protection of the first sheet ("Single") of this workbook:

objExcel.Workbooks(FileBestand.FileName).Worksheets("Single")....what next ?

Third step, putting some info on the Excel sheet

Fourth step, enabling the protection of the above mentioned sheet.

----

This is pretty much what has to happen. Can anyone help?

Kind regards,
Arjan

Excel Unprotect And Protect Password In VBS
Tying to created a password protected copy of an Excel spreadsheet. The "master" spreadsheet is password protected and I use the following to unprotect it in order to customize the copy:

Sheets("SheetName").Select
Application.SendKeys "Password{enter}"
Sheets("SheetName").Unprotect

I've tried several ways to protect the copy that is made before saving but it either does not work or it pauses and displays the copy. I need to do it for several hundred copies and do not want a pause for user input for each one.

Any help?

Protect/unprotect And .Activate Not Working In Excel 97
My saga continues. I've learned that there are going to be users using this VBA program in Excel 97 (This wasn't the case originally). So now I'm getting random errors on certain things.
The first one is I had been using sheet1.protect and sheet1.unprotect in my code. I'm getting the following error in Excel 97:
"Run-time error '1004': Method 'Unprotect' of object '_worksheet' failed"
It runs fine in 2000 but not here. I see no reason for this not to work.
What's even odder is that I recorded a macro of doing an unprotect and a protect. It works fine when I run the macro through tools/macros/run macros and then I click "Run", but if I were to assign the macro to let's say a command button I get an error.

Second problem:
I have been using .Activate to set the focus on a textbox, checkbox, etc...
When it gets to any .activate code in Excel 97 I get the following:
"Run Time Error '1004': Activate method of OLEObject class failed"
I'm beginning to thing it's just this install of Excel that's corrupt or something. I'll go try it on another machine and see what happens. In the meantime...
Thanks all

Check If User Tries To Unprotect Worksheet
Is there a way to check if the user tries to unprotect a worksheet? I can make a if/then statement to check for the network ID (limit only specific users to that function), but I don't know how to check for the command. I ran a macro recorder and it only had "ActiveSheet.Unprotect" as the command called. Basically, I'd like it to read like:


Code:
'Need code here that is called on the unprotect event
If NetID <> "Me" Then
Exit Sub
Else
ActiveSheet.Unprotect (password="password")
End If

How To Protect A Worksheet
i'm having problems protecting a worksheet,
what i want is to protect the worksheet but what happens is
some cells are protected while others are not...
please tell me what's wrong... here is my code....

Worksheets(intt_sheetCount).Activate
Worksheets(intt_sheetCount).Protect Password:="CANcan", DrawingObjects:=True, Contents:=True,
Scenarios:=True

thanks in advance....

Protect Multiple Worksheet?
Need to be able to protect and unprotect multiple worksheets via the excel protection menu.
Need to be able to trap the password the user enters and password protect all other opened worksheets automatically.

Does anybody know how to do this.
Thanks

Protect Some Cells Of A Worksheet
hello to all,

How do you protect some part of a worksheet, not the entire sheet , like "activesheet.protect" in Excel? any idea??

thanks,

TK

Protect Method On A Worksheet
Hello
I found this line in my code, i tried looking at the properties online but MS does not give a good description. can you guys tell me whether this line of code ensures that i cannot open the worksheet anywehere else but in excel?

myFile.Worksheets("mysheet").protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Protect Sheet With Worksheet Change
I have a worksheet where I want users to be able to edit only certain cells. I know you can lock the cells and use protection, however that stops people from being able to autofilter which is important. I experimented with using the Worksheet_SelectionChange event to store the cell value and then Worksheet_Change to reset it if it is not in one of the allowable cells. This seems to work fine. However, the data in the sheet is also tied to a database which refreshes the data whenever the sheet is opened. Since all of the cells get changed during this refresh, the program seems to go into an infinite (or at least very long) loop.

My question is, is there a way to have the worksheet events only start working AFTER the initial refresh is done? Or does anyone have any other suggestions on a good way to do this?

Thanks

Protect A Worksheet, Specify Password &amp; Allow Filtering
I have a workbook that our sales guys use to create quotes. On the sheet that uses the data they've entered in other sheets to create the formal quote document, I have a reset button, which - as you might guess - resets the whole page. Here's my code:

Sub cmdReset_Click()

On Error Resume Next

Dim ItemsWks As Worksheet
Dim QuoteWks As Worksheet

Set ItemsWks = Worksheets("Items, Cat")
Set QuoteWks = Worksheets("Quote Body")

Application.ScreenUpdating = False

With ItemsWks
.Unprotect ("xxxx")
If .FilterMode = True Then
.ShowAllData
End If
.Protect ("xxxx")

End With

It works. It protects the sheet with the password I've specified. But I also want to AllowFiltering on the protected sheet. I haven't been able to make that happen. I've tried:

...
.Protect ("xxxx", DrawingObjects:=True, Scenarios:=True,_ AllowFiltering:=True)

- that returns "Compile error: Expected: ="

So I tried:

.Protect (Password:="xxxx", AllowFiltering:=True)

- same error.

I've tried many different variations of code with no luck. I'm not sure what I'm doing wrong or how to fix it. I've tried recording a macros & copying the code it generates, but that doesn't work either. It generates an 'ActiveSheet.Protect' command that I guess doesn't work with my 'Dim' statements? That's just a guess - this is my first attempt at VB since I was in college, and we won't talk about how long ago that was

Can someone help, please?

Thanks,
Jennifer

Cant Protect Excel Worksheet.. Sort Of??
Ok. I have an app that protects / unprotects sheets in excel..

I have one workbook that it wont work correctly on.

It sets protection with password... you can expand / collapse subtotals (says its protected) good...
BUT.. I can edit Data!??

If I move to outside the print area... its protected... cant edit
but in the print area.. where I need the data protected.. it wont do it!

AAUUUGHH! been trying for 3 hours now to figure this out.

Any clue anyone?

Edit An Excel Worksheet Using VB
I have a small procedure that I'm running in a module that is supposed to open an Excel file, modify a single cell, then shut down. I'm getting a runtime error - 1004 application defined or object defined error. This is in VB 6.0 Here is my script :


Private Sub CompuType()
ConvertedFileName = "C:CSPPROGRAMSTests.xls"
Set objExcel = GetObject(ConvertedFileName)
objExcel.application.Visible = False
objExcel.application.cells(1, 1) = "Dummy"
objExcel.application.quit
End Sub


Any suggestions? I have looked EVERYWHERE I can think of, but I still gett this error. It is assigned to the cells(1,1)="Dummy" line. Your assistance is appreciated.

Respectfully,
Delljohnb

Open/Edit Embedded Worksheet In WORD
I have been trying desperately to get a macro in word (and thus hopefully my VB app.) to simply find the embedded Excel sheet within a word document.
That was the easy part, the thing I am now believing may in fact be impossible, is getting it to then open the embedded worksheet (in MS Word) and 'Select-All' then 'Paste' it into an existing worksheet wihthin Excel.
By recording a new Macro, I try and do this (having it track the changes I make and thus writing a script for me), but ONLY when the macro is recording, the program will NOT allow me to either double-click the spreadsheet OR right click it (so I could then choose 'Edit' or 'Open').
I hope this function is not completely impossible, either through Word or VB, so If any of you experts out there know anyhting about this, your help would be GREATLY appreciated! thanks...

Unprotect A Sheet
Hi All,

what do I have to write in VB to unprotect a EXCEL Worksheet (protected without Password --> Tools --> Protection --> Protect Sheets...) and then to reprotect it at the end of the procedure????

Thanks for the answers...

Unprotect Sheet
how do i hardcode the password in order to unprotect it...i try using marcos but the code show only activesheet.unprotect...

Unprotect Problem
hi
if anybody could help me, i've got a problem with unprotecting a sheet in MS Excel.
I've tried to do it in different ways, but it's still crashing. I also tried to record a macro unprotecting and protecting active sheet and then copied the code to the button click event. Once I started macro - it worked OK, then I clicked on the button and it crashed with
Run-time error '1004'
Protect method of worksheet class failed

I really do not know what to do. I need to assign unprotect method to this event.

Unprotect A Project Via Its Own Code?
When a protected project (Tools | VBAProject Properties | Protection | Lock project for viewing) encounters a fatal error, the user is presented with essentially only one option: the End button.

Is there a way to programmatically unprotect a project? While to an extent defeating the purpose of protection, it would allow the author or an administrator (on such rare? occasions) to debug/dignose on the fly.

Many thanks!

*** Unprotect Ecxel Sheet ***
Hi

I want to know how i can unprotect a excel sheet if i dont know the password to unprotect it.
Is there some tool or code that can do that for me.

Thanx

How Do You Unprotect Controls On A Form?
I've got an application that I need to modify and I'm having trouble re-arranging the controls on the form. They won't move.
How can I reset them or unprotect them?

Thanks,
Jeff

Unprotect Existing Excel
I am not a programmer but was given an excel file built with macros and visual basic.  It is a document for formal review and appraisal processes but needs tweeked to company specifics.  Long story short, a user can click on buttons and be brought to specific worksheets to fill out.  The originator doesnt remember the unprotect password. Is there any work around to unlock?????  I would not be able to learn this in the time period required.

Unprotect Sheet During Copy &amp; Paste.
I don't have an explanation of this behavior and need help.
1) My current sheet is protected.
2) all cells from header row and above are locked
3) all cells from data row and below are unlocked.
scenario:
1) user selects a cell in data row, right click, select Copy
2) user selects a cell below
3) Worksheet_SelectionChange is handled
4) a macro is invoked to update the cell Validation if needed
5) this macro first Unprotect current sheet
6) after this line is executed, right click on the cell below has “Paste” option disabled. Before that “Paste” option was available. Data is still in clipboard.

What make “Paste” not available?

UnProtect All Excel Files In A Folder
hi,

I have got many .xls files in a folder. I want to remove the password (protection) from all the files.

How can i do this?

thanx in advance

Unprotect An Excel Work Sheet
Does anyone know how to unprotect a Microsoft Excel Work Sheet that is password protected?

A client created one with VB Macros behind it and now forgets the password.

Unprotect A VBA Project Using VBA In The BeforeSave Command????
I have a password protected workbook, and have also password protected my VBA code. However, when I go back into the spreadsheet and try to save it, I get a message stating that I cannot save the spreadsheet in "this version" of excel while the VBA Project is protected. The spreadsheet is an Office97-2000 version which is requried due to multiple platform across the environment.

I am trying to write some code on the "Workbook_BeforeSave" function to unprotect the sheet with the password, perform the save, and reprotect the VBA Project with the password. However I can't find a way to unprotected the VBA Proejct in code. I can get as far as:

Set Application.VBE.ActiveVBProject.Protection = vbext_pp_none

but this doesn't work stating that the property is read only. Does anyone know of a way to perform this in VBA?????

Thanks.....

Activesheet.Unprotect Causes Runtime 1004 Error!?
I recorded several macros in Excel97 to sort a range of cells in various ways. The code didn't look too tricky so I have managed to add some to unprotect the worksheet before sorting and re-protect it afterwards. I created an extra drop-down menu on one of the toolbars and assigned the macros to each of the menuitems. Upon realising that I couldn't save the menu in the file (as the macros were) I thought I would put some buttons on the sheet.
After locating the Control Toolbox I put the buttons in place and realised there was no assign macro feature so I would have to do it in the code.
All I did was put the Sub name of the macro into the Sub for clicking the button. It came up with a Run-time 1004 error when it tried to ActiveSheet.Unprotect. Commenting out the Unprotect and the Protect lines made it work but I need to have this feature as the users of the spreadsheet are even less capable than me and will end up deleting the formulas I spent so long creating.

Can anyone help please.
Yours hopefully,
Larry.

--------------------------------------------------
"incognuity" should be a word!

Delete Worksheet, Then Create New Blank Worksheet With Same Name
Hi

I have a worksheet named "Harmonics", where I present to the user the results from my VB program.

But when the user re-runs the macro, I want to ensure that ALL the data on the worksheet is overwritten and no 'old' data is still there.

I thought the best way to do this would be to delete the entire worksheet at the start of the program then re-create the worksheet as a blank document ready for writing to. Is this a bit extreme? Would I be better to just delete all cells within the worksheet as opposed to the sheet itself?

Your help would be most appreciated.

And of course, some code to do what I want would be great!

Thanks,
Daryll

Separate Data In Worksheet Cells Then Add To New Worksheet
I have a spreadsheet with 9 columns and a variable number of rows. Each of the cells contains multiple values which include a date, time and location. I want to populate a second spreadsheet the the data from each of these cells. I have attached an example workbook which has the original data in SheetA, and the results I am trying to achieve in SheetB. The spreadsheed will be easier to understand than my explanation I think.

The data in the cells needs to be separated into individual cells in the new sheet, for each set of cell data, the associated data in columns 1 and 9 need to be added/repeated.

Is this possible?if yes, how do I achieve this?

How To Check If A Worksheet Exists In Worksheet Collection
Hi All,
   I wanted to know if there is any method to check if a worksheet exists in worksheets collection.I want to check this by worksheet name.

Thanks in advance

Regards
Raghu

Changing Worksheet Name When Copying Worksheet
Hi guys I have an Excel problem. I am having a worksheet and i duplicate it when i need to have it more times. Therefore I use the following code:


Code:
ActiveWorkbook.Sheets("Template_Sheet").Copy After:=ActiveWorkbook.Sheets(1)
When I copy the "Template_Sheet" Worksheet I get one with the name "Template_Sheet (2)"
How can i make the new copied Worksheet have the name I choose for it?

Thanx for any advice

To Determine The Rows To Paste A Worksheet(blank Cells To Paste Next Worksheet)
hi all experts out there....

would llike u guys to help out with a problem i am facing....


the code below is to select each of the worksheet,copy it and paste it in worksheet (printer").....i got 3 worksheets to paste into worksheet ("printer")....worksheet("11 aug"),("12 aug") and ("18 aug")....i got no problem pasting the first two worksheets("11 aug") and ("12 aug")...but i got a problem pasting the last worksheet("18 aug").....i want to paste each worksheet with a offset of (3,0) but the last worksheet paste at another cell instead......i attach the excel doc name (total)(workbook) and the macro.....the ("sub regroup")....need to be improve..pls help ......thank a lot..."P

Code:
Sub regroup()
'
' regroup Macro
' Macro recorded 9/3/2003 by SGTANCKE
'

'
Sheets("11 Aug").Select
Rows("1:" & Sheet1.UsedRange.Rows.Count).Select
Selection.Copy
Sheets("Printer").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("12 AUG").Select
Rows("1:" & Sheet2.UsedRange.Rows.Count).Select
Selection.Copy

Worksheets("Printer").Activate
ActiveCell.Offset(rowOffset:=3 + Sheets("Printer").UsedRange.Rows.Count, columnOffset:=0).Activate
ActiveSheet.Paste

Sheets("18 AUG").Select
Rows("1:" & Sheet3.UsedRange.Rows.Count).Select
Selection.Copy

Worksheets("Printer").Activate
ActiveCell.Offset(rowOffset:=3 + Sheets("Printer").UsedRange.Rows.Count, columnOffset:=0).Activate
ActiveSheet.Paste


Columns("E:E").EntireColumn.AutoFit




End Sub

Print A File In Edit.com N Close Edit.com??
i've to send information to edit.com to print a file!!!thx to someone i can open edit.com and browse the file!!i use sendkeys to print the file but it doesn't work can anyone pls help me!!how i can send the information to activate print and then close the editor!!!

to open the edit.com and browse the file it's like that

r = shell("edit.com the path of my file)
appactivate r

i've used sendkeys but it doesn't work!!!can anyone help me how can i print the file and close the edit.com!!pls
thx in advance

Copy From Worksheet To Worksheet??
I need to copy info from one cell in worksheet1 to another cell in worksheet2. The problem is I have a formulated cell in worksheet1 and when I copy from worksheet1 to worksheet2, it copies the formula. I need to copy the value only. Here's the code I'm using.

Worksheets("BT WORKSHEET").Range("A60").Copy
Worksheets("REQ.").Select
Worksheets("REQ.").Range("B11").Select
Worksheets("REQ.").Paste

Thanks for any help.

To Copy Worksheet At The End Of Other Worksheet
I WANT TO KNOW HOW TO COPY THE EXCEL WORKSHEET AT THE END OF OTHER WORKBOOK SHEET.
I HAVE WRITTEN CODE IN VB6.0 BUT IT OVERWRITE THE WORKSHEET WHILE COPYING.

Private Sub File1_Click()
Dim FileToCopy As String: FileToCopy = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLSMASTER_FILECAP 8 MASTER FOR RS232 V.95.xls"
Dim NewFile As String: NewFile = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005" + File1.FileName
FileCopy FileToCopy, NewFile
Exit Sub
End Sub

Private Sub Form_Load()
Me.Show
Me.Caption = "Copying..."
File1.Path = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005"

End Sub

I Cant Edit A Form But I Can Edit The Source!
Hey.

I have a form which I cant move, resize etc the controls on it but i can edit the code!When I choose a control on it the resize dots become white...
And the form is not read only(i right clicked in windows explorer at the form and then went to propeties)

Why is this happening?

Thanks

Protect One Row
I have a worksheet with some command buttons in row one. I want to make the sheet function mostly normally, except that users cannot change that row, only push the buttons. I have the buttons set not to move or resize and I have row 1 locked. The worksheet is protected and I am allowing users to change everything except locked cells. Everything is unlocked except row 1. Everything seems to be fine, except that the users can still resize row 1. Is there any way to make row 1 just a static, untouchable row?

Thanks

How Do I Protect My Dll ?
Hi there.

I have never compiled a Dll for redistribution in VB where the dll required a license key.
I have created many ActiveX controls this way -- and now, I need to protect my dll as it needs to be distributed to end user machines.

Can anyone tell me how it is done, as the "Require License Key" option is disabled when I try to change it in the project Properties screen.

The project consists of only one class

Thanks
wbeetge

Protect App
As you may know, some of us use something like **** scramble for security reason then we are in trouble.


I hope that it's clear enought , thnaks guys

How To Protect Exe?
hello, how to protect a exe file?

let say the exe contains

Text1.Text = "YOUR NAME"

you compiled it in .exe

and then open the .exe into Hex Workshop

u will see the YOUR NAME in the Hex workshop

how to prevent this? i don't want to show the YOUR NAME in hex

thanks..

Protect Exe?
Hello
Can anybody tell me if it is possible to prevent a vb
executable file from being moved or deleted or otherwise interfered with.(access denied kind of thing!)
Thanks!!

VB6 .exe To Protect....
 



I have an aplication developed in VB6, I want to protect the Distribution CD against copy or some way to avoid unwanted distribution. Does anybody help me? .
Thanks in advance
Fernando Oviedo

VB6 Exe To Protect
I have an aplication developed in VB6, I want to protect the Distribution CD against copy or some way to avoid unwanted distribution. Does anybody help me? .
Thanks in advance
Fernando Oviedo

How Do I Protect My Dll ?
Hi there.

I have never compiled a Dll for redistribution in VB where the dll required a license key.
I have created many ActiveX controls this way -- and now, I need to protect my dll as it needs to be distributed to end user machines.

Can anyone tell me how it is done, as the "Require License Key" option is disabled when I try to change it in the project Properties screen.

The project consists of only one class

Thanks
wbeetge

How Do You Protect Files
i have been coding a RPG and when they save the game it copys the xp to a text document , you see people can edit that ....so how do i stop them?

Protect VBA Project
I am creating a new workbook and adding forms and modules dynamically through VB. How can I lock the VBA Project at run time with VB?

Copyright © 2005-08 www.BigResource.com, All rights reserved