Worksheet Change help

[chookers]

Hello -

I'm using the following code to change the rowheight when info is entered. I'd like to get it to also add a top and bottom line to the row but I can't work out how to do it. Is it possible? I've put the usual Borders code in but I can't get it right.

Do I do something like EntireRow.Borders etc?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range([A5], [A65536].End(xlUp)).EntireRow.RowHeight = 9

End Sub

I would also like it to merge columns C:L within each row but don't know if I'm pushng my luck.

Thanks for suggestions!

Chookers

[Colo]

Hi chookers, please try this

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngLs As Long



    '/// Delete all borders of Sheet

    '/// If you do not need this, just delete following code line...

    For lngLs = 5 To 12

        Cells.Borders(lngLs).LineStyle = xlNone

    Next



    With Range([A5], [A65536].End(xlUp))

        .EntireRow.RowHeight = 9

        '/// [A5] add Borders

        .Item(1).EntireRow.Borders(xlEdgeTop).LineStyle = xlContinuous

        '/// [A???] add Borders

        .Item(.Rows.Count).EntireRow.Borders(xlEdgeBottom).LineStyle = xlContinuous

        '/// Merge Cells

        Cells(Target.EntireRow.Row, 3).Resize(0, 8).Merge

    End With

End Sub

HTH

[chookers]

Hi Colo - thank you - it almost works!

I can guarantee there will be at least one lame *** question from me in this but bear with me, I'm only beginning at this VBA thing - One of those dreaded scenarios where I'm great with Access so the boss thinks he'll give me an impossible (for me!) Excel task as surely they're all the same!!.

what has happend is it's changed the rowheight then I get an error message ' application or object defined error'. It hasn't put borders or merged cells yet. When I debug it goes to the last line - the Entire row.Cells...Merge line

To make matters worse I forgot that i can't put borders in column A so I'll change the code to accomodate that. What it will need to do is

Column B:L - underline top and bottom
Column D:L - merge cells within each row.

How can I tell it to only merge those columns of each row, and do you have any idea what it's not liking in that last line of code?

Thank you Colo!

[Colo]

On 2002-05-20 23:00, chookers wrote:
Hi Colo - thank you - it almost works!

I can guarantee there will be at least one lame *** question from me in this but bear with me, I'm only beginning at this VBA thing - One of those dreaded scenarios where I'm great with Access so the boss thinks he'll give me an impossible (for me!) Excel task as surely they're all the same!!.

what has happend is it's changed the rowheight then I get an error message ' application or object defined error'. It hasn't put borders or merged cells yet. When I debug it goes to the last line - the Entire row.Cells...Merge line

To make matters worse I forgot that i can't put borders in column A so I'll change the code to accomodate that. What it will need to do is

Column B:L - underline top and bottom
Column D:L - merge cells within each row.

How can I tell it to only merge those columns of each row, and do you have any idea what it's not liking in that last line of code?

Thank you Colo!

Hi chookers, me again.

So, try this code.

First, try remove "0" of resize, so it will work.
Sorry I can not tell you why in English.
But on my system, it was work fine.(XL97-sr2,Win2K)

Private Sub Worksheet_Change(ByVal Target As Range)

    '/// Delete all borders of Sheet

    '/// If you do not need this, just delete following code line...

    For lngLs = 5 To 12

        Cells.Borders(lngLs).LineStyle = xlNone

    Next



    With Range([A5], [A65536].End(xlUp))

    '/// Change RowHeight

        .EntireRow.RowHeight = 9



    '/// Column B:L - underline top and bottom

        With Cells(.Item(1).EntireRow.Row, 2).Resize(, 11)

            .Borders(xlEdgeTop).LineStyle = xlContinuous

        End With

        With Cells(.Item(.Rows.Count).EntireRow.Row, 2).Resize(, 11)

            .Borders(xlEdgeBottom).LineStyle = xlContinuous

        End With

    End With

    '/// Column D:L - merge cells within each row.

    Cells(Target.EntireRow.Row, 4).Resize(, 9).Merge

End Sub

_________________
With regards,
Colo

[ This Message was edited by: Colo on 2002-05-21 00:24 ]

[chookers]

Oh Colo Thank you so much for that, it worked really well. I was wondering if I could ask your advice on something.

What I'm using this for is a few people at work that would like to cut/paste some target outcomes from one worksheet to another (so that they can just have the target outcomes and not all the other stuff of the original worksheet) so they can write some notes in next to the outcomes on how they want to reach the goals (hence the merged cells). Now what will most likely happen is they will just copy all their target outcomes from one sheet and paste to the 'notes' sheet. Usually though this will involve pasting several rows, rather than just one.

I wanted to make it a bit easier for them so it would be easily formatted as you've done above, but even if they've pasted 4 rows at once.

Would I be better advised to do a macro that I could attach to a button, so they could use that and it would format all the rows (with the merged cells and lines etc)? From what I can gather the Worksheet change is mostly used for entering info a row at a time rather than if you have to paste several rows?

I know this is a long missive but I AM learning more about all code from this, not just this one, so thanks.

Kind regards,

Chookers



At the moment the worksheet has a header in A1:L4, these rows will never change, only ALL the ones below it. Basically the rows that would need to be changed are A5 through what ever the last row is. I am winding up using your code in another worksheet as well - as is.