[SOLVED] crop spreadsheet

i have created a spreadsheet which only uses columns a-f. i would like to
crop this area or remove g onwards so g onwards is completely blank or a grey
area. any ideas? i can't find anything in the search for help database b ut i
know it can be done as i have seen other workbooks the same. thanx for your
time if you reply i appreciate it :O)

bek

Select all columns right of F and Format>Column>Hide.

Same for rows below your data.

OR Format>Cells>Patterns. Pick a nice gray color.

Alternative to the above........

Set the Scroll Area so's no one can move out of the area you designate.

Note: Setting ScrollArea is good for that session only and only the
activesheet. Has to be reset next time workbook is opened.

Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
specify which worksheet.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:F50"
End Sub


Gord Dibben Excel MVP

On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
wrote:

>i have created a spreadsheet which only uses columns a-f. i would like to
>crop this area or remove g onwards so g onwards is completely blank or a grey
>area. any ideas? i can't find anything in the search for help database b ut i
>know it can be done as i have seen other workbooks the same. thanx for your
>time if you reply i appreciate it :O)

thanx gordy, mwaahhhh!!

"Gord Dibben" wrote:

> bek
>
> Select all columns right of F and Format>Column>Hide.
>
> Same for rows below your data.
>
> OR Format>Cells>Patterns. Pick a nice gray color.
>
> Alternative to the above........
>
> Set the Scroll Area so's no one can move out of the area you designate.
>
> Note: Setting ScrollArea is good for that session only and only the
> activesheet. Has to be reset next time workbook is opened.
>
> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
> specify which worksheet.
>
> Private Sub WorkBook_Open()
> Sheets("YourSheet").ScrollArea = "A1:F50"
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
> wrote:
>
> >i have created a spreadsheet which only uses columns a-f. i would like to
> >crop this area or remove g onwards so g onwards is completely blank or a grey
> >area. any ideas? i can't find anything in the search for help database b ut i
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)
>
>

I have gone to VB and copied your code below but to no avail. Could you
explain again in perhpas simpler terms in case I've missed something ? thanks

"Gord Dibben" wrote:

> bek
>
> Select all columns right of F and Format>Column>Hide.
>
> Same for rows below your data.
>
> OR Format>Cells>Patterns. Pick a nice gray color.
>
> Alternative to the above........
>
> Set the Scroll Area so's no one can move out of the area you designate.
>
> Note: Setting ScrollArea is good for that session only and only the
> activesheet. Has to be reset next time workbook is opened.
>
> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
> specify which worksheet.
>
> Private Sub WorkBook_Open()
> Sheets("YourSheet").ScrollArea = "A1:F50"
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
> wrote:
>
> >i have created a spreadsheet which only uses columns a-f. i would like to
> >crop this area or remove g onwards so g onwards is completely blank or a grey
> >area. any ideas? i can't find anything in the search for help database b ut i
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)
>
>

Hi Mike

to use Gord's code, you need to right mouse click on a sheet tab and choose
view code, now down the left of the VBE window you'll see your workbook's
name in bold & brackets and under that all the sheets listed and something
called "ThisWorkbook" (if you can't see this choose view / project explorer
from the menu)

double click on this workbook & then copy & paste Gord's code in the right
hand side of the screen. Then change
YourSheet
to the name of the sheet where you want to limit the number of columns &
rows that can be viewed.

once you've done that use ALT & F11 to switch back to your workbook ...
choose tools / macro / security and ensure your security settings are set to
medium - now save & close the workbook.

Now reopen it, say yes to enabling macros and then the code should run and
you should not be able to scroll outside of the specified range.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have gone to VB and copied your code below but to no avail. Could you
> explain again in perhpas simpler terms in case I've missed something ?
> thanks
>
> "Gord Dibben" wrote:
>
>> bek
>>
>> Select all columns right of F and Format>Column>Hide.
>>
>> Same for rows below your data.
>>
>> OR Format>Cells>Patterns. Pick a nice gray color.
>>
>> Alternative to the above........
>>
>> Set the Scroll Area so's no one can move out of the area you designate.
>>
>> Note: Setting ScrollArea is good for that session only and only the
>> activesheet. Has to be reset next time workbook is opened.
>>
>> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module
>> and
>> specify which worksheet.
>>
>> Private Sub WorkBook_Open()
>> Sheets("YourSheet").ScrollArea = "A1:F50"
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud
>> <[email protected]>
>> wrote:
>>
>> >i have created a spreadsheet which only uses columns a-f. i would like
>> >to
>> >crop this area or remove g onwards so g onwards is completely blank or a
>> >grey
>> >area. any ideas? i can't find anything in the search for help database b
>> >ut i
>> >know it can be done as i have seen other workbooks the same. thanx for
>> >your
>> >time if you reply i appreciate it :O)
>>
>>

Mike

The code must be pasted in the Thisworkbook module, not a general module.

ALT + F11 to open VBE.

CTRL + r to open Project Explorer.

Select and expand your workbook/project.

Click on Microsoft Excel Objects.

Double-click on Thisworkbook to open.

Paste the code in there. "YourSheet" must be changed to whatever sheetname is
appropriate.

Save and close the file.

Re-open and scrollarea should be set.


Gord

On Wed, 20 Apr 2005 06:15:02 -0700, "Mike" <[email protected]>
wrote:

>I have gone to VB and copied your code below but to no avail. Could you
>explain again in perhpas simpler terms in case I've missed something ? thanks
>
>"Gord Dibben" wrote:
>
>> bek
>>
>> Select all columns right of F and Format>Column>Hide.
>>
>> Same for rows below your data.
>>
>> OR Format>Cells>Patterns. Pick a nice gray color.
>>
>> Alternative to the above........
>>
>> Set the Scroll Area so's no one can move out of the area you designate.
>>
>> Note: Setting ScrollArea is good for that session only and only the
>> activesheet. Has to be reset next time workbook is opened.
>>
>> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
>> specify which worksheet.
>>
>> Private Sub WorkBook_Open()
>> Sheets("YourSheet").ScrollArea = "A1:F50"
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
>> wrote:
>>
>> >i have created a spreadsheet which only uses columns a-f. i would like to
>> >crop this area or remove g onwards so g onwards is completely blank or a grey
>> >area. any ideas? i can't find anything in the search for help database b ut i
>> >know it can be done as i have seen other workbooks the same. thanx for your
>> >time if you reply i appreciate it :O)
>>
>>

Mike

Alternative.......

Right-click on Excel logo left of "File" on menu bar.

Select "View Code" which opens Thisworkbook Module directly.

Paste code in there.


Gord Dibben Excel MVP

On Wed, 20 Apr 2005 09:26:13 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>Mike
>
>The code must be pasted in the Thisworkbook module, not a general module.
>
>ALT + F11 to open VBE.
>
>CTRL + r to open Project Explorer.
>
>Select and expand your workbook/project.
>
>Click on Microsoft Excel Objects.
>
>Double-click on Thisworkbook to open.
>
>Paste the code in there. "YourSheet" must be changed to whatever sheetname is
>appropriate.
>
>Save and close the file.
>
>Re-open and scrollarea should be set.
>
>
>Gord
>
>On Wed, 20 Apr 2005 06:15:02 -0700, "Mike" <[email protected]>
>wrote:
>
>>I have gone to VB and copied your code below but to no avail. Could you
>>explain again in perhpas simpler terms in case I've missed something ? thanks
>>
>>"Gord Dibben" wrote:
>>
>>> bek
>>>
>>> Select all columns right of F and Format>Column>Hide.
>>>
>>> Same for rows below your data.
>>>
>>> OR Format>Cells>Patterns. Pick a nice gray color.
>>>
>>> Alternative to the above........
>>>
>>> Set the Scroll Area so's no one can move out of the area you designate.
>>>
>>> Note: Setting ScrollArea is good for that session only and only the
>>> activesheet. Has to be reset next time workbook is opened.
>>>
>>> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
>>> specify which worksheet.
>>>
>>> Private Sub WorkBook_Open()
>>> Sheets("YourSheet").ScrollArea = "A1:F50"
>>> End Sub
>>>
>>>
>>> Gord Dibben Excel MVP
>>>
>>> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
>>> wrote:
>>>
>>> >i have created a spreadsheet which only uses columns a-f. i would like to
>>> >crop this area or remove g onwards so g onwards is completely blank or a grey
>>> >area. any ideas? i can't find anything in the search for help database b ut i
>>> >know it can be done as i have seen other workbooks the same. thanx for your
>>> >time if you reply i appreciate it :O)
>>>
>>>

Thank you JulieD and Gord - works now !
If I have several worksheets that I want to restrict the scrolling area
do I just repeat the process using different "sheet name" and areas ?
Ie: press return to new line and repeat or under end sub ?
Mike

"Gord Dibben" wrote:

> Mike
>
> Alternative.......
>
> Right-click on Excel logo left of "File" on menu bar.
>
> Select "View Code" which opens Thisworkbook Module directly.
>
> Paste code in there.
>
>
> Gord Dibben Excel MVP
>
> On Wed, 20 Apr 2005 09:26:13 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> >Mike
> >
> >The code must be pasted in the Thisworkbook module, not a general module.
> >
> >ALT + F11 to open VBE.
> >
> >CTRL + r to open Project Explorer.
> >
> >Select and expand your workbook/project.
> >
> >Click on Microsoft Excel Objects.
> >
> >Double-click on Thisworkbook to open.
> >
> >Paste the code in there. "YourSheet" must be changed to whatever sheetname is
> >appropriate.
> >
> >Save and close the file.
> >
> >Re-open and scrollarea should be set.
> >
> >
> >Gord
> >
> >On Wed, 20 Apr 2005 06:15:02 -0700, "Mike" <[email protected]>
> >wrote:
> >
> >>I have gone to VB and copied your code below but to no avail. Could you
> >>explain again in perhpas simpler terms in case I've missed something ? thanks
> >>
> >>"Gord Dibben" wrote:
> >>
> >>> bek
> >>>
> >>> Select all columns right of F and Format>Column>Hide.
> >>>
> >>> Same for rows below your data.
> >>>
> >>> OR Format>Cells>Patterns. Pick a nice gray color.
> >>>
> >>> Alternative to the above........
> >>>
> >>> Set the Scroll Area so's no one can move out of the area you designate.
> >>>
> >>> Note: Setting ScrollArea is good for that session only and only the
> >>> activesheet. Has to be reset next time workbook is opened.
> >>>
> >>> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
> >>> specify which worksheet.
> >>>
> >>> Private Sub WorkBook_Open()
> >>> Sheets("YourSheet").ScrollArea = "A1:F50"
> >>> End Sub
> >>>
> >>>
> >>> Gord Dibben Excel MVP
> >>>
> >>> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
> >>> wrote:
> >>>
> >>> >i have created a spreadsheet which only uses columns a-f. i would like to
> >>> >crop this area or remove g onwards so g onwards is completely blank or a grey
> >>> >area. any ideas? i can't find anything in the search for help database b ut i
> >>> >know it can be done as i have seen other workbooks the same. thanx for your
> >>> >time if you reply i appreciate it :O)
> >>>
> >>>
>
>

Mike

At end of ScrollArea code line, hit ENTER and repeat with new sheet name and
scroll area range.

Alternative is to place the code line in event code in each sheet.

Right-click on a sheet tab and "View Code"

Paste this into the module that opens.

Private Sub Worksheet_Activate()
ScrollArea = "A1:F50"
End Sub

Repeat for each sheet.

If you go this route, delete the Thisworkbook code.

Gord

On Thu, 21 Apr 2005 03:27:01 -0700, "Mike" <[email protected]>
wrote:

>Thank you JulieD and Gord - works now !
>If I have several worksheets that I want to restrict the scrolling area
>do I just repeat the process using different "sheet name" and areas ?
>Ie: press return to new line and repeat or under end sub ?
>Mike
>
>"Gord Dibben" wrote:
>
>> Mike
>>
>> Alternative.......
>>
>> Right-click on Excel logo left of "File" on menu bar.
>>
>> Select "View Code" which opens Thisworkbook Module directly.
>>
>> Paste code in there.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 20 Apr 2005 09:26:13 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:
>>
>> >Mike
>> >
>> >The code must be pasted in the Thisworkbook module, not a general module.
>> >
>> >ALT + F11 to open VBE.
>> >
>> >CTRL + r to open Project Explorer.
>> >
>> >Select and expand your workbook/project.
>> >
>> >Click on Microsoft Excel Objects.
>> >
>> >Double-click on Thisworkbook to open.
>> >
>> >Paste the code in there. "YourSheet" must be changed to whatever sheetname is
>> >appropriate.
>> >
>> >Save and close the file.
>> >
>> >Re-open and scrollarea should be set.
>> >
>> >
>> >Gord
>> >
>> >On Wed, 20 Apr 2005 06:15:02 -0700, "Mike" <[email protected]>
>> >wrote:
>> >
>> >>I have gone to VB and copied your code below but to no avail. Could you
>> >>explain again in perhpas simpler terms in case I've missed something ? thanks
>> >>
>> >>"Gord Dibben" wrote:
>> >>
>> >>> bek
>> >>>
>> >>> Select all columns right of F and Format>Column>Hide.
>> >>>
>> >>> Same for rows below your data.
>> >>>
>> >>> OR Format>Cells>Patterns. Pick a nice gray color.
>> >>>
>> >>> Alternative to the above........
>> >>>
>> >>> Set the Scroll Area so's no one can move out of the area you designate.
>> >>>
>> >>> Note: Setting ScrollArea is good for that session only and only the
>> >>> activesheet. Has to be reset next time workbook is opened.
>> >>>
>> >>> Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
>> >>> specify which worksheet.
>> >>>
>> >>> Private Sub WorkBook_Open()
>> >>> Sheets("YourSheet").ScrollArea = "A1:F50"
>> >>> End Sub
>> >>>
>> >>>
>> >>> Gord Dibben Excel MVP
>> >>>
>> >>> On Wed, 2 Mar 2005 13:29:09 -0800, bekwud <[email protected]>
>> >>> wrote:
>> >>>
>> >>> >i have created a spreadsheet which only uses columns a-f. i would like to
>> >>> >crop this area or remove g onwards so g onwards is completely blank or a grey
>> >>> >area. any ideas? i can't find anything in the search for help database b ut i
>> >>> >know it can be done as i have seen other workbooks the same. thanx for your
>> >>> >time if you reply i appreciate it :O)
>> >>>
>> >>>
>>
>>