Calling worksheet format function 1

[daveonion]

Hi I've created a function (by recording a macro) which will format a nummber of different worksheets,
the function is set up as below
Function formatfile(sheet_name As Worksheet)
Range("E13:AD17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous

blah blah blah


i am calling it by

For Each work_sheet In w.Worksheets
formatfile(work_sheet)
blah blah

however it fails any ideas would be appreciated

 

[xlbo]

try:
For Each work_sheet In w.Worksheets
formatfile(work_sheet.name)
blah blah


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[daveonion]

Hi Ive tried worksheet.name that didnt work either, i didnt think it would to be honest because in the function i am specifying sheet_name as a work_sheet

thanks

 

[PHV]

And what about this ?
Function formatfile(sheet_name As Worksheet)
[!]sheet_name.[/!]Range("E13:AD17").Select
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GlennUK]

Change it to a Sub! Honest.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[daveonion]

Nope neither works, when i step through the code it errors on the call so it doesnt actually make it to the formatting function any ideas

honestly : )

 

[PHV]

And, in addition to my previuos suggestion, replacing this:
formatfile(work_sheet)
with thiss ?
formatfile work_sheet

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GlennUK]

Do you have this in your calling sub:
Dim work_sheet As Worksheet
?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[daveonion]

thanks Phv, works now, i dont understand though why you dont use the brackets but ah well