1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

how do you get the prof look in excel

Discussion in 'Microsoft Office Specialist (MOS)' started by sirdragon, Jan 10, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    hi guys

    been looking at the templates in excel, and just wanted to know if any one knows how to get the neat borders around the print area.

    also how do you limit the scroll of the spreadsheet past the blacked out area surrounding the print area, to get the idea look at the template for balance sheets and try and scroll the page.

    the idea is that i am trying to set up a 'front page' with macros to link all the various sheets within a 'program'.

    ie. frontsheet-

    level 1

    balance sheet..monthly balance..forecast..invoices ..weekly recipts..profit and loss..staff rota

    ...............monthly report(merge)......................... expenditures.....department breakdown
    ........................................................................................staff expenses breakdown

    (tis a pain trying to show this without real formatting [​IMG] looks sort of right with my resolution. hope u get the idea

    etc. then of course I link them all together with formulas to minimise repeating the same work.

    looks like fun eh [​IMG]

    most of it I have already sorted out but i just need to make it look the part so it as idiot proof as possible.


    any help usefull
  2. sirdragon

    sirdragon Nibble Poster

    dont worry guys got it sorted, the scrolling limiter is in VB editor in excel set the limits with absolute ref and this seems to work ok.

    now I am trying to sort out a popup sytem where users can enter data directly into a prompt that will copy and paste into a sheet using a macro. got a feeling it has to do with VB again.....

    ideas would be welcome.
  3. Phil
    Honorary Member

    Phil Gigabyte Poster

    Certifications: MCSE:M & S MCSA:M CCNA CNA
    WIP: 2003 Upgrade, CCNA Upgrade
  4. sirdragon

    sirdragon Nibble Poster

    will have a gander cheers

    oh btw the first problem..........didnt work for some reason did not save, for next time the application ran.....

    oh well back to the drawing board..
  5. sirdragon

    sirdragon Nibble Poster

    well so far managed to get scroll to freeze, using this code

    Public Sub Workbook_Open()
    Worksheets(1).ScrollArea = "a1:m34"
    End Sub

    put in the workbook section of VBE

    but if I need to alter anything in the hidden area need a way of getting past my own code.

    would like it to work like this.

    worksheet is protected by password.

    1 remove password
    2 click on cell A1 (previously protected)

    the code should recognise that range A1 is selected and activate code to set the ScrollArea back to normal.

    with help from the guys VBA.com got this far

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Intersect(Target, Range("a1")) Is Nothing Then
    MsgBox ("nothing")
    Exit Sub

    Worksheets(1).ScrollArea = ""
    MsgBox ("scroll")
    End If
    End Sub

    the msgbox is supposed to help me locate how far the script has run but they dont work either, or the script doesnt even start.....

    help appreciated....
  6. nugget
    Honorary Member

    nugget Junior toady

    What are your macro security settings? If they are too high the scripts and macros wont work.
    Certifications: A+ | Network+ | Security+ | MCP (270,271,272,290,620) | MCDST | MCTS:Vista
    WIP: MCSA, 70-622,680,685
  7. sirdragon

    sirdragon Nibble Poster

    they are set to enable quotes, so I do not think it is that.

Share This Page