how do you get the prof look in excel

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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



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

(tis a pain trying to show this without real formatting 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

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.

cheers

martin
any help usefull

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.

 

Hi Martin,

Glad you got the first problem sorted. Did a quick google and came up with this

http://www.excel-vba.com/v-input-box.htm

any use?

Phil

 

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

 

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

 

What are your macro security settings? If they are too high the scripts and macros wont work.

 

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