Microsoft Office Tutorials and References
In Depth Information
Restricting the User to a Portion of the Worksheet
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub RestrictUser(WhichRange As Range)¶
'WhichRange is the range that the user is allowed to use¶
With WhichRange.Worksheet¶
'Set the ScrollArea property, which is a string, not a Range¶
'object equal to the address of the given range¶
'Only one Area can be used, so, use the first one in case¶
'more than 1 area was received.¶
.ScrollArea = WhichRange.Areas(1).Address¶
End With¶
End Sub¶
' * * * * *¶
Sub Auto_Open()¶
'Call the RestrictUser macro when the workbook is opened¶
RestrictUser Range("C6:I18")¶
End Sub¶
Exl
For added security, the macro could be called from the Worksheet_Activate,
SelectionChange, or Change events by copying and pasting the code below into
the sheet module of the sheet on which you want to limit the scroll area, and
then removing the apostrophes from the beginning of each line.
View the Appendix to learn how to store any of these
procedures in a Worksheet module.
'Private Sub Worksheet_Activate()¶
' RestrictUser Range("C6:I18")¶
'End Sub¶
' * * * * *¶
'Private Sub Worksheet_Change(ByVal Target As Range)¶
' RestrictUser Range("C6:I18")¶
'End Sub¶
' * * * * *¶
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)¶
' RestrictUser Range("C6:I18")¶
'End Sub¶
Notes: The range address in which the user is allowed to select should be changed as
needed for each worksheet.
Search JabSto ::




Custom Search