crop spreadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bekwud - 02 Mar 2005 21:29 GMT

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)

Reply to this Message

Gord Dibben - 02 Mar 2005 22:29 GMT

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

>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)

Reply to this Message

bekwud - 02 Mar 2005 23:21 GMT

thanx gordy, mwaahhhh!!

> bek
>
[quoted text clipped - 25 lines]
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)

Reply to this Message

Mike - 20 Apr 2005 14:15 GMT

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

> bek
>
[quoted text clipped - 25 lines]
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)

Reply to this Message

JulieD - 20 Apr 2005 15:54 GMT

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.

Signature

Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway

>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 ?
[quoted text clipped - 38 lines]
>> >your
>> >time if you reply i appreciate it :O)

Reply to this Message

Gord Dibben - 20 Apr 2005 17:26 GMT

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

>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
[quoted text clipped - 28 lines]
>> >know it can be done as i have seen other workbooks the same. thanx for your
>> >time if you reply i appreciate it :O)

Reply to this Message

Gord Dibben - 20 Apr 2005 18:45 GMT

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

>Mike
>
[quoted text clipped - 51 lines]
>>> >know it can be done as i have seen other workbooks the same. thanx for your
>>> >time if you reply i appreciate it :O)

Reply to this Message

Mike - 21 Apr 2005 11:27 GMT

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

> Mike
>
[quoted text clipped - 63 lines]
> >>> >know it can be done as i have seen other workbooks the same. thanx for your
> >>> >time if you reply i appreciate it :O)

Reply to this Message

Gord Dibben - 21 Apr 2005 16:45 GMT

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

>Thank you JulieD and Gord - works now !
>If I have several worksheets that I want to restrict the scrolling area
[quoted text clipped - 69 lines]
>> >>> >know it can be done as i have seen other workbooks the same. thanx for your
>> >>> >time if you reply i appreciate it :O)

Reply to this Message

Adelaide Ruble - 17 Sep 2006 20:28 GMT

> bek
>
[quoted text clipped - 25 lines]
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)

Reply to this Message

Adelaide Ruble - 17 Sep 2006 20:36 GMT

Gord:
Over the last couple of weeks, your answer is the closest that I have comne
to resolving this similar issue. But I have some questions.

Where do you insert code?

*When you click on a row for all figures in that row to be added, the sum is
posted in the last cell of that row, regardless of where I want it be. I only
want to go as far as AF. I want the sum in AG. So far, I only get the sum
posted in IV. How can I define the area that I want to use and get excel to
only use that area, as well.

Again, I want the sum in AG not in IV

> bek
>
[quoted text clipped - 25 lines]
> >know it can be done as i have seen other workbooks the same. thanx for your
> >time if you reply i appreciate it :O)

Reply to this Message

Gord Dibben - 17 Sep 2006 22:35 GMT

Two question here.

1. This particular code is workbook event code.

Right-click on the Excel icon left of "File" on main menu and select "View Code"

Copy/paste the scrollarea code in there.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:AG50"
'note the change in range to accomodate your range of A1:AG50
End Sub

NOTE also......if macros are disabled on opening, this code is useless.

2. To SUM a range select the range first or write a formula in the cell you
want the SUM to appear.

In your case in AG1 enter =SUM(A1:AF1)

You can drag/copy that down Column AG as far as you want.

It will SUM each row.

Gord

>Gord:
>Over the last couple of weeks, your answer is the closest that I have comne
[quoted text clipped - 39 lines]
>> >know it can be done as i have seen other workbooks the same. thanx for your
>> >time if you reply i appreciate it :O)

Reply to this Message

Rate this thread: