Work book crash when deleting a value

    Hi folks

    I have a workbook in which I can add initials or delete them ( in the 'Teachers' column - column C). It actually works fine.

    The initials are numbered, in the next column (D). No header.

    I can delete initials in column C. The numbers will adjust accordingly in Colum D.

    The limit on the initials entered is 20 (in column C).


    My problem:

    I can delete in Column C, ANYWHERE, just by clicking on it, any initial.

    HOWEVER, If I delete the FIRST initial in C3 (number 1 in column D), vba acts up!


    Please help me to fix it.

    Thanks

    Eric

    The dynamic range called Teachers becomes invalid when you delete cell C3 since it is tied to that cell.


    Maybe change the Worksheet_SelectionChange code as follows to reset that range when you delete in that column:

    Code
     'Move up teachers
  Application.EnableEvents = False
  Rng.Delete Shift:=xlUp
 'Reset the dynamic range
  ActiveWorkbook.Names("Teachers").RefersTo = "=OFFSET(Teachers!$C$3, 0, 0, COUNTA(Teachers!$C:$C))"
  Application.EnableEvents = True


    Also best to add the EnableEvents False/True code otherwise you'll trigger the Worksheet_Change event whenever you delete a cell.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login