crop spreadsheet

[Guest]

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)

 

[Gord Dibben]

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

 

[Guest]

thanx gordy, mwaahhhh!!

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

 

[Guest]

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

 

[JulieD]

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.

 

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

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

 

[Gord Dibben]

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