excel - delete.entirerow breaks vba function

[luthepa1]

First off I am not expert in this area.

I am using excel 2000 on WinXP Pro SP2.

I have used a vba function for calculating like NetworkDays function except this custom function (NetworkDaysMisc) can calculate the number working week weekdays that you specify (e.g Mon to Sat). Code can be found here -> http://www.bettersolutions.com/excel...I327411311.htm

this works very well and does what I want. Then under the doubleclick subroutine of the worksheet I have code to add and remove rows depending on what the cells contents is. Here is my code;

Please Login or Register  to view this content.

Ok. Now on my spreadsheet I have a cell to calculate my number of working days of a date range using the vba function like I first stated. The cell formula looks like this

=module1.networkdaysmisc(K2,K3,I34:I48,J18:J24)

So now the problem I am experiencing that I hope someone can help me with please. When I double click my cell with contents of "Double click here to add SR" and new row is inserted above that cell. And the cell that uses the vba function to calculate the workdays displays correct value. But then if I double click the cell with contents "New SR Entry" that is in the newly created row, the row deletes as it should but now the cell calculating the networkdaysmisc becomes #VALID!. And I cant work out why? If I go to the cell using the networkdaysmisc function I find the reference cells and ranges are correct and so all I need to do on this cell is enter edit mode (F2 key) then press enter and is now display correctly.

I have tried moving/relocating the cell reporting the networkdays up above where the rows can be added and deleted but this make no difference. So it make me think that something like vba code needs to be restarted. I dont know really.

I figure I need to add some code, or the sheet is not refreshing/updating. Can anyone figure this out and help me?

I have though of vba code to move to this problem cell (ActiveCell.Offset(2,0).Select) and simulate the F2 and enter keys. But there must be a better way and a reason why this happen and how to avoid this problem.

Thanks in advance,

Paul.

[Leith Ross]

Hello luthepa1,

Welcome to the Forum!

You gave a very full description of the problem but you are missing one critical piece in this puzzle - the workbook. When working with VBA is critical that all the code be posted. The easiest way to cover all bases is to post the workbook. With the workbook, there is no misunderstanding about layout or code. This will reduce the posting of questions and answers and help you get a solution faster.

[luthepa1]

Sorry. Very understandable.

I have attached my workbook (having deleted some titles to protect sensitive data but does not affect sheet operations/functions at all).

i have highlighted the suspect cell in red highlight. On the A column is where you can add and remove rows with double clicking cells like stated in my first post where you add new service reports to the excel sheet (SR's).

I have also kept a password on it for support of troubleshooting if it were to make any difference at all. the password is "letmein".

Thanks.

P.S. - hmmm... I just noticed something. If I add a new row for new SR entry bu then double click the new row, A column cell to delete new row I get the same problem with the cell highlighted in red. But if I now change the start or end date in the date range section at top right, it will refresh my cell in red highlight. Maybe I am looking in wrong area for issue.

[Leith Ross]

Hello luthepa1,

I have reviewed your code and tested it several times. So far, everything works. I have not gotten the "#VALID!" error performing the steps you posted. Maybe I have missed a step. I will go to the web site and review the writeup. Maybe I can see what is happening from there.

[luthepa1]

Really! it works for you? I have noticed I made one mistake in what I reported. Its not that the cell becomes #VALID!. Thats wrong. It becomes #VALUE! But even then you say its working. Could it be a excel version issue? I being on excel 2000.

I have attached 3 pics to help demo my issue.

Pic1: Shows the default look and all is ok. I have select the cell that I am about to double click.

Pic2: I double click the selected cell of pic1 which inserted a new row with the contents of the first cell as "New SR Entry". Then double clicking this cell of the new row will remove this new row and give me pic3

Pic3: The red highlight cell now has #VALUE!. This will easily refresh to display the correct number of working days by just selecting the date in the top right with the drop down lists. Start or End date works fine.


I appreciate you helping me troubleshoot this.

[luthepa1]

I SOLVED IT! Yay....

In the NetworkdaysMisc function I use (custom function - aka UDF), the first line of code after declaring variables, you need to add the following line;

Application.Volatile

This tells excel/vba that the function needs to be executed everytime there is a calculation performed in the worksheet.

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.