show hide shape to a cell problem with code

Hi,

Try the following code which is also in the attached workbook.

Please Login or Register  to view this content.

Lewis

their seems to have a problem, when i i reference the cell with other sheet it does auto update the show or hide shape

ExcelForumDisplayHideShape-2015-03-22-14-50.xlsm

The code requires a slightly different approach Sub Worksheet_Calculate() when a cell changes value from a formula. Try the following code which is included in the attached workbook.

Please Login or Register  to view this content.

Lewis

OK THAT is amazing thank you so much. one more thing please, is there a way after shape is auto SHOWN, the rows bellow then ARE HIDDEN? you see where i choose to hide or show rows? i want to eliminate that and to do the show or hide rows automatic when shape is visible or not.

Hi,

Thanks for the kind words. Try the following macros which are included in the attached file. Please let me know if the file does not behave EXACTLY per your specifications.

Please Login or Register  to view this content.

Lewis

first of all you are amazing, thank you. there are things i couldn't imagine they could happen in this project and when ever i do something plus to it it gives me new ideas on how to proceed.

1) i want to completely remove L1 and (no value in that cell at all) i think it works yes?
2) at the beginning you have Private Const sSheetNAME = "MAIN DB MULTILINE 1P"
i also have another sheet which is called "MAIN DB MULTILINE 3P" it has the same formatting with a slide little diference,
is there a way to add it at this code or do i have to copy paste the whole thing to the sheet code of "MAIN DB MULTILINE 3P"?


Lewis if you know is it possible to do the program web based? i did a search but it costs a lot and second they only convert simple excel programs without macro and vba code. i am trying to develop this for my students. i teach adults electrical courses in vocational night school and they are very interested in this software. it will help them a lot. by the way the software has many sheets and many calculations.

thank you very very much

Thank you for the kind words and the rep points.

i want to completely remove L1 and (no value in that cell at all) i think it works yes?

I don't think you can do that completely. You probably at the very least need 'UNHIDE', or you won't be able to add a value to a hidden row.

at the beginning you have Private Const sSheetNAME = "MAIN DB MULTILINE 1P"
i also have another sheet which is called "MAIN DB MULTILINE 3P" it has the same formatting with a slide little diference, is there a way to add it at this code or do i have to copy paste the whole thing to the sheet code of "MAIN DB MULTILINE 3P"?

You have to cut and paste the code to the new Sheet and change the name to '3P'.

is it possible to do the program web based? i did a search but it costs a lot and second they only convert simple excel programs without macro and vba code. i am trying to develop this for my students. i teach adults electrical courses in vocational night school and they are very interested in this software. it will help them a lot. by the way the software has many sheets and many calculations.

I don't know, since I don't use Web Based Excel. I did all the software using Excel 2003.

If you can buy on Ebay UK, you can get Office 2003 for $25 US Dollars (30 Euros) each including postage. Postage may be a lot less than the $14 each if you buy several copies: http://www.ebay.co.uk/itm/Microsoft-...item51c9a69de9

Lewis

thank you very much

now my file is running slower than before why is that? it has to do with calculations of macro?

Try adding the lines in red (very busy today, so I didn't have time to test the code):

Please Login or Register  to view this content.

Lewis

now its ok but there is to much screen flickering on another sheet. it affected my other sheet which it has this

Please Login or Register  to view this content.

I think it's time for you to upload a sample workbook. If the workbook is too big, put it in a zip file.

Lewis

I was unable to duplicate your problem on a Windows 8.1 computer using 32 bit Excel 2010. You can add the 2 items in red above (Application.EnableEvents), but when I added them, it did not make a difference. When I made a change on sheet 'Main DB' (1 cell or several cells), I had a delay of about one-half second, but I did not see any screen flicker.

Lewis

that delay cannot be fixed?

The problem is that accessing shapes takes a (relatively) long time, even if no changes are required to the shape. I tried to improve the time performance, but was not successful. Sorry, but I did the best I could.

It is possible that someone with more skill than I could make the macros run faster.

Lewis

its ok you already did too much thank you