How to render a formula inactive

[James T777]

Hi Everyone. I hope someone can help - I'm relatively new to Excel VBA macros. So here is my dilemma. I created a spreadsheet with certain conditions ( see screenshot -image attached). I created a formula ( networkdays) under Days Pending that compares the date entered to the current date. So it will increment 1 day, each day. What I want to do is to keep that from incrementing and just freeze the current value when the status changes to "Closed" under Status Column because when it is closed, it is no longer pending although I still want to show how many days it took to complete.

Hope that made sense and thanks in advance to anyone who can help!

Freeze formula.jpg

[6StringJazzer]

Welcome to the Forum James T777!

One way to do this is with a macro that runs when you change the status to Closed, and it will change the formula for Days Pending to the actual value, so it won't change.

The way that I do this is to have another column next to status where you record the date that the status changed. Then you can do everything with formulas and don't need VBA.

I can help with either solution but you'll need to attach your file. I can't update a picture. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[James T777]

Thanks 6String. Attached is the spreadsheet. I had to delete a few things since there is sensitive material pertaining to my workplace but its not need for you to evaluate what I was asking.

BTW: I would prefer if you could show me a solution via VBA code since I'm trying to limit the number of columns on the spreadsheet.

Thanks Again!

[6StringJazzer]

I can't find anything in this file that looks remotely like the image in your first post.

[6StringJazzer]

Put this code in the module for the worksheet having your table. Update red references to match your actual sheet, where 3 is the column with CLOSED and A is the column with the date.

Please Login or Register  to view this content.

[James T777]

Thanks, I'll give your code a try below. Hmm..The file I uploaded is the exact one that I uploaded a screenshot from yesterday.

[6StringJazzer]

I see the problem. On sheet KANBAN DATA you have frozen the panes at row 19. However, given the format of your file, my laptop screen is not big enough to see row 19 so all I can see is 1-16, and it won't scroll down.

So for 3 use 11 and for A use J.

[James T777]

Thanks! All good.