Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Target.Address wont triger AutoFit code in Excel 07

    Hi, Im trying to get rows to autofit every time cell b5 changes.

    In Cell b5, there is a drop-down list via data validation. The data in the range of B10:E61 will automatically change referencing to the same location of another worksheet specified in the drop-down list of b5.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$b$5" Then
    Rows("10:61").Select
    Rows("10:61").EntireRow.AutoFit
    End If
    End Sub


    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this in the Excel forum....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Thanks.

    Im new to this, and didn't look far down enough to See MS Excel before Visual Basics.

    Anyways when I went to look at MS Excel Forum, this appears there. Does that mean it is under that forum now? Ot do i need to create a new thread directly in that forum?

    Thanks and appreciate the tip.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Welcome to the forum.

    I suspect that the event handler is being called but that your logical check has a flaw: the address should have an uppercase "B". Also, there's no need to select the rows to autofit them using code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$5" Then
            Me.Rows("10:61").EntireRow.AutoFit
        End If
    End Sub
    An easy way to tell if your event handler is being called is to add a breakpoint. If you find it is not being called then it probably means that events have been disabled. To re-enable them type this into the immediate window:
    Code:
    Application.EnableEvents=True
    However, I think it is most likely that the problem was the lower case "b" in the code you posted.

    Hope that helps...

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Thanks Colin!

    I've got codes to work with lowercases before so not sure if that had to do with what was wrong. Anyhow, I tested the code you provided and it works perfectly.

    Appreciate it.

    Cheek

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •