Free Microsoft Excel 2013 Quick Reference

.Protection UserInterfaceOnly:=True

I've come across something perplexing. I would attach a workbook but it pulls data from 2 large text files and 1 Access database.

Briefly what is happening is this. When the workbook opens every worksheet page is protected with the following routine:


	VB:
	
. 
. 
. 
If Selection  "" And InStr("VISP,H,VH", Selection.Offset(0, 1)) > 0 And Selection.Offset(0, 1)  "" Then 
    If intUserChoice = vbYes Then Call Worksheet_SetProtection(ThisWorkbook.Name, CStr(Selection), True) 
    If intUserChoice = vbNo Then Call Worksheet_SetProtection(ThisWorkbook.Name, CStr(Selection), False) 
End If 
. 
. 
. 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The called routine follows


	VB:
	
) 
     
     '   Local Variables
    Dim strPassword         As String 
     
     '   Step 1 : Retreive current worksheet password
    strPassword = Workbooks(strWorkbook).Worksheets("Application Data").Range("ad_WksPassword").Offset(0, 1) 
    DecryptPassword (strPassword) 
     
     '   Step 2 : Set the sheet protection
    Application.ScreenUpdating = False 
    If blnProtect Then 
        Workbooks(strWorkbook).Worksheets(strSheet).Protect Password:=strPassword, UserInterFaceOnly:=True 
        If Workbooks(strWorkbook).Worksheets(strSheet).AutoFilterMode Then Workbooks(strWorkbook).Worksheets(strSheet).EnableAutoFilter = True 
    End If 
    If Not blnProtect Then Workbooks(strWorkbook).Worksheets(strSheet).Unprotect Password:=strPassword 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

So essentially once everything is said and done. The user is able to manipulate the filters on a worksheet but unable to do anything else. I love my users can't you tell.

Now I've been using the above code for at least a year without issue, until today that is.

Now all my workbooks have a worksheet call "User Application Interface" which contains the main controls for getting / manipulating various production data. In this workbook I have a button ( I use the Control Toolbox controls ) called "wkscmd_UpdateGoals"

There comes 2 points where I have to sort a worksheet. This is routine that I use for sorting.


	VB:
	
, _ 
    strSortKey1 As String, varOrder1 As Variant, _ 
    strSortKey2 As String, varOrder2 As Variant, _ 
    strSortKey3 As String, varOrder3 As Variant, _ 
    varThisOrientation As Variant) 
     
     '   Local Variables
    Dim rngSortThis As Range 
    Dim rngSortKey1 As Range, rngSortKey2 As Range, rngSortKey3 As Range 
     
     '   Step 1 : At least one key must be sent
    If strSortKey1 = "" Then Exit Sub 
     
     '   Step 2 : Prepare Sort Range
    Set rngSortThis = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortThisRange) 
     
     '   Step 3 : Sort according to the number of keys sent
    If strSortKey3  "" Then 
        Set rngSortKey1 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey1) 
        Set rngSortKey2 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey2) 
        Set rngSortKey3 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey3) 
        rngSortThis.Sort Key1:=rngSortKey1, Order1:=varOrder1, _ 
        Key2:=rngSortKey2, order2:=varOrder2, _ 
        key3:=rngSortKey3, order3:=varOrder3, _ 
        Orientation:=varThisOrientation 
        Exit Sub 
    End If 
    If strSortKey2  "" Then 
        Set rngSortKey2 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey2) 
        Set rngSortKey1 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey1) 
        rngSortThis.Sort Key1:=rngSortKey1, Order1:=varOrder1, _ 
        Key2:=rngSortKey2, order2:=varOrder2, _ 
        Orientation:=varThisOrientation 
        Exit Sub 
    End If 
    If strSortKey1  "" Then 
        Set rngSortKey1 = Workbooks(strSortThisBook).Worksheets(strSortThisPage).Range(strSortKey1) 
        rngSortThis.Sort Key1:=rngSortKey1, Order1:=varOrder1, _ 
        Orientation:=varThisOrientation 
        Exit Sub 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and this is the how I call the sorting routine


	VB:
	
 SortWorksheet_Range(ThisWorkbook.Name, Me.Name, Range(Range("wksdet_TableTop"), Range("wksdet_TableTop").End(xlDown).Offset(0, 15)).Address, _ 
Range("wksdet_TableTop").Address, xlAscending, _ 
"", "", _ 
"", "", _ 
xlTopToBottom) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Now the problem that I'm having is that if the active page is the page with the command button I get an error on the sorting. That if when I debug I make any other page the active page it doesn't have an error.

I've started the command buttons code from the VBE with various pages active and the only page that error occurs on is the page which the actual button resides.

Any brilliant ideas out there in Oz land?


Post your answer or comment

comments powered by Disqus
Hi there,

Please excuse mistakes with my submission, am relatively new to posting.

I have develeped a simple UI for an Access database with Excel, it is a workbook that contains 1 sheet. It builds an SQL query on a few selectable cell criteria and retrieves information from a central MSAccess Database.

The user of this tool can then update this information and return it to the database. However, I have unlocked only certain range of columns and permitted editing. The remainder is for information only.

With the Workbook Open event I have used the following to protect the sheet.
This does indeed protect the sheet but it has an undesirable effect in that the keyboard cursor movemens are restriced on the
unlocked cells. Left & Right is fine. Up and Down keystrokes do not work. Very wierd.

To resolve this I have set the worksheet property EnableSelection = 0 xlNoRestrictions.

This will now allow full keyboard cursor use, but now has an undesired effect in allowing the non editible cells to be selected resulting in the familiar dialog box "The cell or chart you are trying to change is protected and therefore read only... etc.. etc..."

I'd like to protect the sheet, and not allow the cells to be selected. I would have thought my original method would have accomplished this but for the undesired keyboard anomoly.

Has anyone had similar experiences or can shed any light on the subject?

Thank your for your time.

i have several macros in my workbook and a worksheet that needs to be protected. i added:

Private Sub
Workbook_Open()
With shMAIN
    '.ScrollArea = "A1:AL50"
    .Protect userinterfaceonly:=True
    .EnableSelection = xlUnlockedCells
    .EnableAutoFilter = True
End With
End Sub
to the workbook, but i still get the following error:

The cell or chart you are trying to change is protected and therefore read-only.

To modify a protected cell or chart, first remove protection using the Unprotect Sheet command what am i missing?

thanks.

edit: figured it out. a cell link caused the problem.

Any help is greatly appreciated.

I am using the following snippet in code to protect locked cells and allow
the user to only select unlocked cells.

.Protect userinterfaceonly:=True
.EnableSelection = xlUnlockedCells

This previously worked great in Excel 2000. Now I have both 2000 and 2003
loaded on the same computer. The Tab or Enter key used to advance to the
next unprotected cell. Now most of the time it won't properly tab. And
when selecting a cell I get the "I" bar but no selection around the cell.
(in some rare instances it works correctly - but I have no idea of why).

Again - any and all help is appreciated!

--
steveB

Remove "AYN" from email to respond

I'm trying to protect sheets using the UserInterfaceOnly:=True option, so that I can run macros without having to Protect/Unprotect within every procedure. Unfortunately, for some VBA methods, it's not working.

As an example, I have the following macro which runs automatically when opening the workbook (this is necessary when using the UserInterfaceOnly:=True option).

Code:
 
Private Sub Workbook_Open()
Sheet1.Protect UserInterfaceOnly:=True
End Sub
When I try to run the following macro,

Code:
Sub Macro1()
    Sheet1.[C13:C15].Replace What:="_find", Replacement:="_replace", LookAt:=xlPart
    Sheet1.AutoFilterMode = False 'A range has been filtered in the sheet
End Sub
I find that
1. The .Replace method doesn't work, but no error message appears
2. The .AutoFilterMode = False method generates the error message

"Run-time error '1004':
Method 'AutoFilterMode' of object '_Worksheet' failed."

By the way, I'm using Excel 2003.

Any ideas would be greatly appreciated.

Cheers all!

I got two little problems and i kindly request your assistance.

1. Password protect all sheets without getting any prompts when the user clicks on buttons

In the code window for This Workbook (Workbook_Open event), i protect all sheets using the UserInterfaceOnly argument of the Protect method:

Private Sub Workbook_Open
For each ws in ActiveWorkbook.Worksheets
ws.protect ,,,,UserInterfaceOnly:=True
Next
End sub

The problem:
Suppose i want to enter a password for all sheets in the previous loop.
If i do that, when i click on a Button to activate some procedures i am asked for the password (note: the specific control, i.e. button, is not locked).
Is there any way around this (i.e. password-protect the sheets, but not get the password prompt when the user clicks on unlocked buttons)?

2. Control deletion of Excel file in the same folder

I wish to check a specific folder (Path: ThisWorkbook.Path) for the existence of a specific Excel file with a known file name
(File name to check for = a known string, say "Test").

If the file exists in the folder, i want to delete it.
If not, no action should be taken.

How do i do that?

Your help is very much appreciated.

Thank you for your time,
Alex

Hi,

I have a workbook that needs to protect & unprotect all sheets with the same pass. Running OK.
I put some data in locked cells with a macro, ok when userinterfaceonly := true.
I need to share the workbook... thats a problem.
I found that userinterfaceonly only runs when i protect now, but if i close and reopen, userinterface turns to false. Well, the users that will use this workbook dont have to protect & unprotect, & dont know the password. To make the userinterfaceonly be true always, i can put in openworkbook event that it turns to true, but i need the pass to protect & unprotect is always the same... Not at all my best solution i think... Ok, I accept this way of protecting, but... when the file is shared... I cant protect or unprotect the file, so i cant turn to true userinterface!
Any solution?

I'm familar UserInterfaceOnly:= True

I'm on excel 2002 on XP and I can't get it to work. e.g when I run a macro to copy some data I get an error saying it's protected. I've had a hunt around on Microsoft but I'm still at a loss.


	VB:
	
 Workbook_Open() 
     
    Call CreateMenu 
     
    Dim wSht As Worksheet 
    Dim pWord As String 
     
    pWord = "****" 
     
    For Each wSht In ActiveWorkbook.Sheets 
        wSht.Protect _ 
        Password:=pWord, _ 
        DrawingObjects:=True, _ 
        Contents:=True, _ 
        Scenarios:=True, _ 
        UserInterfaceOnly:=True 
    Next wSht 
     
    Worksheets("MenuSheet").Visible = xlVeryHidden 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas?

I am working on a templete where I want to protect sheets from users but still allow them to group/ungroup rows, I can only get the VBA code to work on 1 sheet, how do I make this work for all sheets in my workbook?

Here's my code:


	VB:
	
 
Private Sub Workbook_Open() 
    With Sheet5 
        .Protect Password:="Secret", UserInterFaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Workbook is protected, sheet is protected userinterfaceonly, have checked and the correct range is selected.

Following code fails on the .Add method with an automation error - 2147417848 (80010108) - The object invoked has disconnected from it's clients. If the sheet is fully unprotected, there is no error - problem is, I need the sheet and the workbook structure protected - ...

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Replacing the .Delete and .Add with a single .Modify results in another error...1004 Application-defined or object-defined error.

I'd like the current code to apply the validation shown on the protected workbook/ sheet...can you help?

Oh, I have searched (here, vbaexpress, cpearson, microsoft support forums, google, expertexchange) and I can find the error but not as a result of my particular circumstances.

Thanks

Hi,

I have run into a problem that makes no sense. I have protected my
sheets using the UserInterfaceOnly=True parameter for some time, and it
has worked fine until now. I'll illustrate the problem with a few
examples.

When the document is opened, in the workbook_open() function, I can do
the following without problem:

Sheet1.Unprotect
Sheet1.Protect UserInterfaceOnly:=True
Sheet1.Range("A1").EntireRow.Hidden = True

So here the UserInterfaceOnly protection seems to work OK. However,
when I later execute the following code in Sheet1:

Sheet1.Range("A1").Value = 123
Sheet1.Range("A1").EntireRow.Hidden = True

.... Setting the value of the locked cell A1 works fine, but trying to
hide the row, I get the "Cannot set hidden property of range object"
error.

I also tested doing a unprotect+protect right before the calls, but it
didn't change anything. Unprotecting the sheet without reprotecting it,
did however make the problem disappear, so it is somehow related to the
protection.

Another odd thing is that the VB Editor does not automatically fix the
case of the userinterfaceonly-parameter. Case is corrected for other
parameter names. It does show it in the parameter list popup, though.
I'm using Excel 2002.

Anyone encountered anything like this? Ideas for debugging? Is there a
way to query the status of the userinterfaceonly-bit for a worksheet?

I manage about 1,600 shared Excel workbooks each day. In each workbook I use a VBA script with Excel 2003 to access a protected worksheet and add/delete/alter data in unprotected (unlocked) cells. I now find a need to write data into protected cells in each workbook.

I'm trying to figure out how to use the UserInterFaceOnly:=True parameter of the Sheets Protect method so I don't need to sandwich my VBA code between a Sheets.Unprotect and a Sheets.Protect method.

So I took one of my workbooks and striped its worksheet free of protection and then reprotected with the following code.

ActiveWorkbook.Sheets(1).Protect Password:="pwd1", UserInterFaceOnly:=True
ActiveWorkbook.SaveAs FileName:=filespec, WriteResPassword:="pwd2", AccessMode:=xlShared
This generates no errors.

So I reopen the workbook with another VBA script and attempt to write to a protected cell.

Set oWBb = oXL.Workbooks.Open(FileName:=filespec, WriteResPassword:="pwd2")
With ActiveWorkbook.Sheets(1)
	.Protect Password:="pwd1", UserInterFaceOnly:=True
	.Range("A1") = "new data"
End With
I first tried the write without the ".Protect Password:="pwd1", UserInterfaceOnly:=True" statement. And it failed with the error that the cell to which I'm writing is protected. I read somewhere that the UserInterfaceOnly parameter is lost when the workbook is saved and has to be reapplied when the workbook is next opened. So I added the statement in. It still failed for the same reason.

I'd appreciate any suggestions on how I can get the UserInterFaceOnly parameter to make protected cells available to VBA writing. Thanks in advance.

I've just now used this method of protecting a worksheet, and it seems at first glance to be working properly. However, I need to know if anybody has come across this issue.

I have a master workbook in which I protect 2 sheets, 2 of many, one of the functions is to save a copy of the workbook to a secondary file name. Now in the main workbook the protection works as required. It prevents the user from entering data but programaticaly I don't have to unprotect/protect the sheets during code execution.

However, when the workbook is saved to the alternate file name it seems to change the level of protection. The protection is still there, the password is still the same, but now the protection is complete it won't execute the code which changes infomation on the protected sheet.

Has anybody else come across this issue and do you know why it does this?

Regards,
Barry Pettis

I have a small piece of code which runs without protection but won't create the data validation list if protection is enabled:

	VB:
	
 WorkSheet_Activate() 
    ActiveSheet.Protect UserInterfaceOnly:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code is:


	VB:
	
 EqOther() 
     
    Cells(6, 9) = "" 
    Cells(6, 10) = "" 
     
    Range("i6").Select 
    With Selection.Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:="=Bother" 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .InputTitle = "" 
        .ErrorTitle = "" 
        .InputMessage = "Please select Buyer" 
        .ErrorMessage = "" 
        .ShowInput = True 
        .ShowError = True 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
called from:

	VB:
	
 Range) 
     'toggles eventshandler
    Const WS_RANGE As String = "A1:M40" 
     
On Error Goto ws_exit: 
    Application.EnableEvents = False 
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then 
        With Target 
            Dim Equip1 As String 
            If Intersect(Target, Range("H6")) Is Nothing Then 
            Else 
                Equip1 = Target.Value 
                If Equip1 = "Other" Then 
                    Call EqOther 
                End If 
            End If 
             
        End With 
    End If 
     
ws_exit: 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"Bother" is a named range. Any idea why setting proctection would cause this. Thank you.

OzGrid / Et Al,

I am currently working on a project which at various times does 2 different things. These things are to be done on a "PROTECTED" ( UserInterfaceOnly:=True ) worksheet.

The first is a Range.Replace command.
The other is a Hyperlinks.Add command.

Now when I'm testing ( typically the worksheet is not protected ) everything works like a champ. But as soon as I protect the sheet the following scenario happens.

1. The replace DOES NOT work.
2. The hypelink.add DOES NOT work.

First of does anybody know why or do they not have this issue?

Second, when the sheet is protected it ends up throwing a runtime error ( 1004 ) "Protected Sheet error" the real odd thing about this is that it doesn't throw the error on the command itself. On the hyperlink the error happens about 3 command further along in the flow.

I'd love to attach a sample but alas it's not possible. I'd just like to know if anybody has come accross this error. Or has heard of an explanation of why some commands won't be performed on a protected sheet. Even though it should only be protected for user interface only.

Using Xl 2003

Gday,

I need some help in modifying / correcting a code I am using. What I am trying to do is have my sheet proteted, but allow a user to use the established group / ungroup selections already created within the worksheet. What happens at the moment is when protected and you select the + button from the group outline, an error message appears not letting you use this function.

I have a macro already created that allows me to use the autofilter functionailty when a sheet is protected as shown below

Sub Autofilter
'
Worksheets("Sheet1").Activate
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveSheet.EnableAutoFilter = True

'
End Sub

I am sure a similar code will enable the use of grouping, but can not establish what it is. I have tried using record new macro, but when you select to + button, it does not show any code.

Any help would be appreiciated

Bevan

Hello all,
I am trying to protect a set of sheets, but want some to remain unprotected, but my code doesn't work and I do not want to resort to putting repeated lines of code in. Any ideas?

Here's the code:

	VB:
	
 Sheets(Array("Logo", "PopInp", "HealthInp1", "HealthInp2", "Spatial1", "Spatial2", _ 
    "Cost", "SummaryOutputs")) 
    mySheet.Protect Userinterfaceonly:=True 
Next mySheet 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Regards,
B

Hi,

I use the following syntax for protecting my worksheet:
Activesheet. protect userinterfaceonly:=true
But how can I prevent that a user uses tools/protection/unprotect worksheet so that my protection is gone?

thanks,
Fluppe

Hi All,

I am in a quandary over how to implement cell protection. It is common knowledge that for protection to work the cells must be locked and the worksheet protected. Well, when I try to implement cell protection using the following code:

Sub Worksheet_Activate
ActiveSheet.Protect UserInterfaceOnly:=True, password:="password"
End Sub

Either the target cells remain locked and a "Run-time error 1004' occurs and the macro stops, or the macro executes and leaves the target cells unlocked (and I'd like them to be locked when the macro has executed).

Can anyone shed light on this quandary? How can I acheive my objective of protecting the cell contents? It has been suggested that I set the sheets scroll area, however, this solution does not work for my situation.

Also, is there a way to lock cell after it has been unprotected?

Any help that I could get on this will be greatly appreciated!

Thanks,
Chris

Hi

I am using this code;

Sub InsertRow()

ActiveSheet.Protect UserInterfaceOnly:=True

Dim rown As Range
Dim rname As String

Set rown = Application.InputBox _
(Prompt:="Select a cell in column A", Title:="Add a row", Type:=8)
rown.Select

Selection.EntireRow.Insert
ActiveCell.Offset(0, 38).FormulaR1C1 = "=SUM(RC[-31]:RC[-1])"
ActiveCell.Offset(0, 39).FormulaR1C1 = "=RC[-36]*RC[-1]"
ActiveCell.Offset(0, 50).FormulaR1C1 = "=RC[-41]-SUM(RC[-3]:RC[-1])"

End Sub

I use the "ActiveSheet.Protect UserInterfaceOnly:=True" bit so that the protection remains on but it allows the macro to insert a row.

However, whenever I open the spreadsheet for the first time and run the macro, it is asking for a password which defeats the object! Once I have entered the password it will run repeatedly without asking for the password again.

How can I resolve this? The user will not know the password but needs to run the macro!

Thanks in advance

I found a great piece of coding in the archives for taking care of filters
in protected workbooks. My difficulty lies in that the sheets all have
different names and I don't know how to code for all sheets in a workbook.

Here's the code to put in the workbook module:

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

I'm guessing that it's the "Sheet1" that is stopping this from working.
I've tried removing the "Sheet1", etc., but all I get are errors. Is there
a way to modify the above so that it works on any sheet?: Users will be
adding new ones in the future and they'll call them all sorts of things that
would be impossible to determine in advance so a generic bit of code would
work best.

Thank you! D

I have a protected worksheet with some macros I'm creating. The macros
call the
Protect Userinterfaceonly:=True to be able to modify the sheet, but
there are still some problems:

- I can't read/duplicate Hidden formulas (I had to disable the Hidden
attribute) (something like Range1.Formula = Range2.Formula doesn't
work if Range2 is hidden)

- Much more important: macros can't manipulate Shapes. I can't
duplicate and delete shapes (I use many formcontrols, and I use macros
to add and delete them)

What can I do? I don't want to put the password of the sheet in the
macro. (like Unprotect password:="topsecret")

--- Bye

I have searched the archives extensively....
the nearest post I can find on sorting protected sheets is http://www.ozgrid.com/forum/showthread.php?t=28699

sheet layout:
database-like list; hidden formulas in cols A..H, left of the list range; top 10 rows for parameters entry and forumlas; the List range has an AutoFilter.

I would like to be able to protect (& hide) the "surrounding" (left and above) cells & formulae WHILE still enabling the User to manually exercise the AutoFilter and Sorting.
I want the List range data to be Unprotected.

From previous posts by Dave Hawley, I see what how to do this for AUTOFILTER


	VB:
	
 Workbook_Open() 
    With Worksheets("Sheet1") 
        .Enable.Sort 
        .EnableAutoFilter = True 
        .Protect UserInterfaceOnly:=True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which I have tested and seems to enable the mix of protection

--HOWEVER, how to enable SORT on a protected sheet WITHOUT resorting to VBA intervention, turning protection on and off.

The VBA protection on and off method would inhibit the ad hoc editing, sorting and filtering needed by user.

Dave

PS. Fantastic website and forum archives!
I have learned so much in the past months!!

Hi all,

I have two problems that I need help resolving.

First, the splashscreen that I have incorporated in my workbook application does not initialize until after at least one of the worksheets opens. How can I make the splashscreen the first thing that my end-user sees. Here is the code that I used:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

(the above code placed in "This Workbook")

Private Sub UserForm_Initialize()
Application.OnTime Now + TimeValue("00:00:05"), "KillTheForm"
With Application
Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width
End With
UserForm1.Hide
End Sub

(the above code placed in "UserForm1")

Private Sub KillTheForm()
Unload UserForm1
End Sub

(the above code placed in "Module1")

Any and all suggestion on this are welcome.

Second, I attempting to limit access to my worksheets with VBA code. What I have done is locked all cells that I don't want the end-users to tamper with, in conjunction with this I implement the subroutine that allows your macro to update the locked cells while preventing the end-user's access. Here is the code that I used:

Sub WorkSheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

The outcome of using this code is that the some of my macros are allowed to update the locked cells, however, some other macro issue errors that indicate the cells are "protected". In particular, it seems that the macros which are simply copying a range form one worksheet to another are the one that do not work with the protection on.

If anyone could help me out with this I would greatly appreciated!

Thanks,
ChrisBEE1

I have recently distributed 300 files which had protected cells on them.
I am now on route to receive these 300 updated files and I need to develop a macro to extract all data from these files to a datadump.
So far I dont ssem to be able to enter the password I set to remove the protection of certian cells on these files automatically.
Can anybody see where I am going wrong?
Many thanks
Tony

    ActiveSheet.Unprotect
    ActiveSheet.Unprotect Password:="protect", UserInterFaceOnly:=True
    ActiveSheet.EnableSelection = xlUnlockedCells



No luck finding an answer? You could always try Google.