A small adjustment needed to an existing code - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: A small adjustment needed to an existing code

  1. #11
    Board Regular
    Join Date
    May 2017
    Posts
    204
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A small adjustment needed to an existing code

     
    Hi Peter,

    I've had so many threads, I lost track of this one, my apologies.

    Yes, I have a trillion codes in this workbook, but none of them are worksheet_Change events.

    When I get the pop-up message appearing, it's not saying there's an error, it's simply saying ... "The cell or chart you're trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password)."

    So, there's no debug that's highlighted. I simply click away from the pop-up message, and it disappears, but a new user might not know to do that, so I'm trying to avoid that potential confusion.

    If there's no code to prevent that pop-up from appearing, that's not a problem.

    Very kind regards,

    Chris

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A small adjustment needed to an existing code

    Quote Originally Posted by palaeontology View Post
    When I get the pop-up message appearing, it's not saying there's an error, it's simply saying ... "The cell or chart you're trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password)."

    So, there's no debug that's highlighted. I simply click away from the pop-up message, and it disappears, but a new user might not know to do that, so I'm trying to avoid that potential confusion.
    Ahhh, my mistake.
    I have a different setting to you in my Excel Options so I wasn't getting that pop-up - but I should have known to include an extra line of code. Add this blue one in where shown.

    Code:
    If Not Intersect(Target, Range("A8:A400")) Is Nothing Then
      Cancel = True
      ActiveSheet.Unprotect Password:="Malibu00"
    Last edited by Peter_SSs; Aug 14th, 2017 at 09:25 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  3. #13
    Board Regular
    Join Date
    May 2017
    Posts
    204
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A small adjustment needed to an existing code

    as always, that worked a treat.

    Thankyou, so much, Peter.

    Kind regards,

    Chris

  4. #14
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A small adjustment needed to an existing code

    Quote Originally Posted by palaeontology View Post
    as always, that worked a treat.

    Thankyou, so much, Peter.

    Kind regards,

    Chris
    No problem. Sorry it took me so long to cotton on to the issue.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  5. #15
    Board Regular
    Join Date
    May 2017
    Posts
    204
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A small adjustment needed to an existing code

    Not at all. I needed to improve my explanation.

    Kindest regards from Brisbane to Macksville,

    Chris

  6. #16
    Board Regular
    Join Date
    May 2017
    Posts
    204
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A small adjustment needed to an existing code

    OK, I'm having a new issue with the code.

    Here's the current code ...

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim wsLS As Worksheet
      Dim NextCell As Range, Found As Range
      
      If Not Intersect(Target, Range("A8:A400")) Is Nothing Then
      Cancel = True
        ActiveSheet.Unprotect Password:="Malibu00"
        Set wsLS = Sheets("LeftSubject")
        wsLS.Unprotect Password:="Malibu00"
        If Target.Interior.ColorIndex = 3 Then
          Target.Interior.ColorIndex = xlNone
          Set Found = wsLS.Columns("A").Find(What:=Target.Value, LookAt:=xlWhole)
          If Not Found Is Nothing Then Found.Resize(, 5).Delete Shift:=xlUp
        Else
          Target.Interior.ColorIndex = 3
          Set NextCell = wsLS.Cells(wsLS.Rows.Count, "A").End(xlUp).Offset(1)
          NextCell.Value = Target.Value
          NextCell.Offset(0, 4) = Now
        End If
        ActiveSheet.Protect Password:="Malibu00"
        wsLS.Protect Password:="Malibu00"
      End If
    End Sub
    It's doing everything brilliantly, however, when I double click a cell that is currently already highlighted red (because that student's data has previously been moved over to the sheet 'LeftSubject') .... ie: I'm trying to remove that value from it's location in 'LeftSubject' ... all the formulae I have in LeftSubject!F8:NZ400, that feed from the corresponding cell value in LeftSubject!A8:A400, seem to get affected.

    For example, if I double click an existing red cell somewhere in Entry!A8:A400, that corresponding entry is removed from it's location somewhere in LeftSubject!A8:A400 ... but .... the formula in every cell in the range LeftSubject!F8:NZ8 changes from this ...

    =IFERROR(IF(INDEX(Entry!$A$8:$NY$400,MATCH($A8,Entry!$A$8:$A$400,0),COLUMN()-1)="","",INDEX(Entry!$A$8:$NY$400,MATCH($A8,Entry!$A$8:$A$400,0),COLUMN()-1)),"")

    to

    =IFERROR(IF(INDEX(Entry!$A$8:$NY$400,MATCH(#REF ,Entry!$A$8:$A$400,0),COLUMN()-1)="","",INDEX(Entry!$A$8:$NY$400,MATCH(#REF ,Entry!$A$8:$A$400,0),COLUMN()-1)),"")

    the next row down (row 9) then takes on the formula that used to be in row 8 (the one shown above).

    If I double click a second red cell from Entry!A8:A400, the formula in row 9 then changes to a formula with #REF (like the one above), and row 10 then takes on the formula that used to be originally in row 8.

    Is there something in the current code that's causing the formula in LeftSubject!F8:NZ400 to lose reference like that ?

    It's not happening to the formulae I have in LeftSubject!B8:E400. They seem to be unaffected.

    Kind regards,

    Chris

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A small adjustment needed to an existing code

    I think this is the first we have heard of formulas (or anything) to the right of column E in LeftSubject.

    Try making this cahnge and see how it goes.

    Code:
    If Not Found Is Nothing Then Found.Resize(, 5).Delete Shift:=xlUp
    If Not Found Is Nothing Then Found.Offset(1).Resize(400, 5).Copy Destination:=Found
    Last edited by Peter_SSs; Aug 20th, 2017 at 01:24 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  8. #18
    Board Regular
    Join Date
    May 2017
    Posts
    204
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A small adjustment needed to an existing code

    That did the trick ... thankyou, again, so much Peter.

    Very kindest regards,

    Chris

  9. #19
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A small adjustment needed to an existing code

      
    Quote Originally Posted by palaeontology View Post
    That did the trick ...
    Good news!
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com