I bet this is tough one< EXCEL PROTECTION...

[darno]

HI!

I am facing a problem with Excel Protection Feature, as i have a macr
in excel sheet that has to do something with range and copy paste fe
columns and format them accordingly, The macro is working fine, but th
problem starts when i protect the sheet for hididng my formulas fro
users. as i execute the macro on a protected sheet, an error popsu
which reads that i can not use range command while the sheet i
protected, But its a must for me to stick to my code, but at the sam
time i need the solution to this problem.

Secondly can any body tell excel to start taking input by enterin
column range via input box.? for example i want a macro which could as
me to enter columns that you want to hide. or could ask me to selec
only the columns tat i wish to print. Can any body help me on these?


Darn

 

[Frank Kabel]

Hi Darno
1. Protection
add the following to your macro
sub foo()
activesheet.unprotect password:="your password"
'your code
activesheet.protect password:="your password"
end sub

2. Asking for a range: use the application.inputbox together with
type:= 8:
Sub foo()
....
Dim aralik As Range
On Error Resume Next
Set aralik = Application.InputBox(prompt:="Select range:", Type:=8)
If Err <> 0 Then
Exit Sub
End If
On Error GoTo 0
' your code
....
End Sub

 

[Tom Ogilvy]

Look in help at the protect method. See the userinterfaceonly setting.
You can set this in the workbook_open event for the sheet and then your
macro should work.

use
application.InputBox( type:=8)

to allow selection of a range with the mouse.

 

[Gord Dibben]

Darno

To overcome first problem you could add Unprotect and Protect lines
to your code.

Sub dostuff()
ActiveSheet.UnProtect Password:="justme"
'your current code goes here
ActiveSheet.Protect Password:="justme"
End Sub

Or use this in a general module

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

For second problem use Application.InputBox Type:=8

Sub clearout()
Dim cel As Range
Set myRange = Application.InputBox(prompt:="Select a Range", _
Type:=8)
For Each cel In myRange
cel.ClearContents
Next cel
End Sub

Gord Dibben Excel MVP

 

[Gord Dibben]

Darno

If you select entire columns with the Application.InputBox code, the
code will run on the entire columns and would be slow.

To just select the used range in columns you don't need the InputBox

Select the top cells in several contiguous columns and run this
code. Then act upon the selected range with your code.

Sub select_alldown()
''from selected cells in selected columns to bottom
''of used range in columns including blanks
Selection.Select
Range(Selection, Cells(Rows.Count, _
Selection.Column).End(xlUp)).Select
End Sub

Gord

 

[Gord Dibben]

Darno

If you select entire columns with the Application.InputBox code, the
code will run on the entire columns and would be slow.

To just select the used range in columns you don't need the InputBox

Select the top cells in several contiguous columns and run this
code. Then act upon the selected range with your code.

Sub select_alldown()
''from selected cells in selected columns to bottom
''of used range in columns including blanks
Selection.Select
Range(Selection, Cells(Rows.Count, _
Selection.Column).End(xlUp)).Select
End Sub

Gord