Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
bek
Select all columns right of F and FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#3
|
|||
|
|||
thanx gordy, mwaahhhh!!
"Gord Dibben" wrote: bek Select all columns right of F and FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#4
|
|||
|
|||
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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#5
|
|||
|
|||
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" wrote in message ... 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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#6
|
|||
|
|||
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" 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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#7
|
|||
|
|||
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" 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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#8
|
|||
|
|||
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" 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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#9
|
|||
|
|||
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" 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" 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 FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
crop spreadsheet
"Gord Dibben" wrote: bek Select all columns right of F and FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
crop spreadsheet
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" wrote: bek Select all columns right of F and FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
crop spreadsheet
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 On Sun, 17 Sep 2006 12:36:02 -0700, Adelaide Ruble wrote: 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" wrote: bek Select all columns right of F and FormatColumnHide. Same for rows below your data. OR FormatCellsPatterns. 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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) | |||
paste contents from other spreadsheet | Excel Worksheet Functions | |||
Applying Existing Password to New Spreadsheet | Excel Discussion (Misc queries) | |||
How do I convert exel spreadsheet to works spreadsheet? | Excel Discussion (Misc queries) | |||
How do I cancel sending a spreadsheet by email? | Excel Discussion (Misc queries) |