Limiting view area to user

  • Thread starter Thread starter jose luis
  • Start date Start date
J

jose luis

Hello,

I would like to restrict the user of an app to view just som
designated area,Let's say from A1:Z200. It's that possible with VBA?

Thank you all,

Best regards

jose lui
 
You could set the row height and column width to 0 for ranges outside your
desired view.
 
jose

You can hide the rows and columns you don't want to see.
Select them and Format>Hide. Then protect the worksheet.

Alternative.........You may want to try setting the Scroll Area.

This setting will not remain after closing the file.
Place a code line in the Workbook open event to set it
each time when you open the workbook.

Right-click on the Excel Logo left of your "File" on menu. Select "View Code"
and paste the following in there. Save the workbook and close/re-open to see
the scrollarea fixed to the range A1:H40

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:Z200"
End Sub

Adjust to your sheetname and range.

Note: if users diable macros, the second method will be rendered useless.

Gord Dibben Excel MVP
 
I would like to restrict the user of an app to view just ...

Just a thought. Even if you also included a restriction to the ScroolArea
with...

ActiveSheet.ScrollArea = "A1:Z200"

One could type a cell address (outside this range) in the "Name Box" to view
that cell's contents in the formula bar. You may want to consider
Protecting the sheet as well.
 
I've tried to dynamically set = Worksheets(1).ScrollArea = "A11:EU65536", but
was unable. In case you don't know the limits of your window you want to show
to the user.

I went this way:

ThirdString = ":"
FourthString = """"
NewString = FourthString & Assumptions.Range("Msg1").Text & ThirdString
& _
Assumptions.Range("Msg2").Text & FourthString
Assumptions.Range("Msg2").Offset(2, 0) = NewString
Worksheets(1).ScrollArea = FourthString & Assumptions.Range("Msg1").Text
& ThirdString & _
Assumptions.Range("Msg2").Text & FourthString

I always get an "Application defined or object-defined error" prompt.

The above "Worksheets" area definition should suffice if you know beforehand
the limits of the area you want to show.
 
Back
Top