Consulting

Results 1 to 9 of 9

Thread: Impossible to insert

  1. #1

    Question Impossible to insert

    NewTimesheet - For posting.xlsm
    Hello,

    I have an Excel with macros. Everything was working fine for several months and I changed nothing to the code or parameters.
    Today I am suddenly unable to insert new rows or columns. I am sure it worked a few days ago, because I used a macro that used the following line and it worked:
    Worksheets(ActiveSheet.Name).Cells(5 + CurrentRowsUpdated, 1).EntireRow.Insert

    Up to Worksheets(ActiveSheet.Name).Cells(5 + CurrentRowsUpdated, 1).EntireRow, everything works fine (I tested it in the immediate window and with message boxes). But when we get to the "Insert" part, I get the following error:
    Run-time error '1004':
    Insert method of Range class failed


    Following that, I tried to just insert a row or column in the regular way. It doesn't work, but it unhides the last row.
    I also tried it on a new, empty tab, it still doesn't work.
    If I open another excel however, it works just fine.

    I'm at my wits' end, can't figure out why it suddenly acts like that. Can anyone help me?

    For those who want to open the Excel, here's how to use it:

    1. Open file and accept macros
    2. Go to the « Projects » tab et note down your projects
    3. Go to the « General » tab
    4. Click on « Show days in Timesheet »
    5. Click on « Quick Access To Month »
    6. Select a month and a year and press « Create Worksheet »
    7. Fill in the timesheet


    What to do if a new project is used or an old one isn't used anymore?

    1. Go to the « Projects » tab
    2. Add projects :

    To remove a project: Delete line

    1. SORT if required
    2. Go to tab YYYY_MMMM (for example 2022_October) that you want to update
    3. Click on "Update"
    4. New projects should automatically appear at the end
    5. Deleted projects will be removed if 0 hours are chosen for that month


    When you close the excel and open it again, you will be redirected to the "General" tab and can click on the « Go To Today’s tab » button to go to today's date.
    Last edited by totonicknick; 12-19-2022 at 12:59 PM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can you post the workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    I just added it to my original post, I forgot to add it originally.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You have accumulated vast numbers of conditional formatting (your macros add conditional formatting but never delete any).
    Try manually clearing the conditional formatting, then letting the code put conditional formatting back in.
    All worked normally here after that.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Quote Originally Posted by p45cal View Post
    You have accumulated vast numbers of conditional formatting (your macros add conditional formatting but never delete any).
    Try manually clearing the conditional formatting, then letting the code put conditional formatting back in.
    All worked normally here after that.

    Thank you for your help.
    Just to be clear, you mean removing the conditional formatting that is not in the code but leaving the one in the code as is or do you mean that when I put in a conditional formatting in the code I should first remove the conditional formatting that was already there, also in the code?

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    If you have conditional formatting (CF) which isn't duplicated/created by the code, leave it; there shouldn't be much.
    Take a copy of your workbook, manually clear all the CFs from all the sheets; there's loads of it (it would take you ages to examine and delete CFs one by one).Then you can run the Workbook Open event manually to reinstate CF, then look at the new CFs to see if they meet your approval, and there are no CFs missing.
    At this point you can also test that you can insert rows etc. and that this has solved the problem you were having.
    Then you need to adjust the code which adds the CF so that it first deletes (some?) CF before adding new CF. Record a macro of your clearing (some) CFs and see what the code looks like before you add it to your workbook open event. If there remain sheets which should have CF but don't, you know to be a bit more selective when deleting CFs from your actual workbook.
    I haven't examined your code closely, be aware that copying cells which have CF also carries over their CF, so if that's happening as well you may want to copy values only.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Looking at your code, test if this solves your problems:
    Add a first line of the Sub ConditionalFormating(Target As Range, j As Integer) sub:
    Target.Parent.Cells.FormatConditions.Delete
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Quote Originally Posted by p45cal View Post
    Looking at your code, test if this solves your problems:
    Add a first line of the Sub ConditionalFormating(Target As Range, j As Integer) sub:
    Target.Parent.Cells.FormatConditions.Delete
    Only doing that doesn't work.
    However, removing all previous conditional formatting before updating worked perfectly, thank you!
    For now, I have deleted all previous conditional formatting AND added the line you recommended.
    Hopefully this will prevent it from happening again, but if it still happens at least I know how to solve it

    I'll put this thread as "solved".

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by totonicknick View Post
    Only doing that doesn't work.
    It should!
    Remember that that sub only runs on the workbook opening, so making that change and not saving then reopening the workbook will not make a difference.
    You can, however, run that sub 'manually' by putting the cursor somewhere in that sub in the vba editor, then pressing F5 on the keyboard.

    Quote Originally Posted by totonicknick View Post
    but if it still happens at least I know how to solve it
    Excellent!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

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
  •