Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Prevent autofill outside scroll area! (Excel 2000 >)

    See the attachment.
    The blue area is controlled by a macro to prevent scrolling out of the blue. I need to prevent users from being able to autofill into the no-go area!
    PS: I do not want to use cell protection in this example. Is there a way to prevent interaction in the no go area using code!??
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent autofill outside scroll area! (Excel 2000 >)

    Rudi

    Does this work

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Created: May 2003
    'Creator: Jezza
    If Not Intersect(Target, ActiveSheet.Range("A3:A202,E1:E202,F3:F202")) Is Nothing Then


    Application.EnableEvents = True
    ActiveSheet.Range("A1").Select
    End If

    End Sub

    This sub has protected ranges A3 to A202 , E1 to E202 and F3 to F202. You can put individual cell refs in there or say A1 to C7. If the user bounces into one of the cells in the prohibited range if then makes A1 the focus. You could do this for quite a large selection of the worksheet

    I hope this is a starter for you?
    Jerry

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent autofill outside scroll area! (Excel 2000 >)

    Unfortunately not Jezza.
    I tested it and am still able to autofill outside the blue area!
    Thanks 4 the input though!
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent autofill outside scroll area! (Excel 2000 >)

    <img src=/w3timages/censored.gif alt=censored border=0> so it does. I hadn't even tested that for an application I have just written using it, back to the drawing board
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent autofill outside scroll area! (Excel 2000 >)

    Try this. It is slow, though.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("11:65536")) Is Nothing Then
    Intersect(Target, Range("11:65536")).Clear
    End If
    If Not Intersect(Target, Range("G:IV")) Is Nothing Then
    Intersect(Target, Range("G:IV")).Clear
    End If
    End Sub

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent autofill outside scroll area! (Excel 2000 >)

    Thanks Hans. I saw that it was quite slow. However, I took the body of your code and pasted it into the SelectionChange event and it works like a dream now. It is fast and it prevents data from being filled outside the blue range...

    Thanx a stack!

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets(1).ScrollArea = "A1:F10"
    If Not Intersect(Target, Range("11:65536")) Is Nothing Then
    Intersect(Target, Range("11:65536")).Clear
    End If
    If Not Intersect(Target, Range("G:IV")) Is Nothing Then
    Intersect(Target, Range("G:IV")).Clear
    End If
    End Sub</pre>

    Regards,
    Rudi

Posting Permissions

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