Macro/VBA for auto hide unhide rows

This is the second thread I make over here, but it concerns the same question from my first thread (but a little more difficult, since I had to change my sheet)

Attached, you can see my file where the code has to it's work

I'll try to explain it simple, but if I wasn't clear on something, please ask me.

ROW 12 = always visible
ROW 13= hiden if C12 & D12 & E12 & F12 & G12 are empty. From the moment the user makes an input in one of those 5 cells, ROW 13 should unhide
ROW 14= hiden if C13 & D13 & E13 & F13 & G13 are empty. From the moment the user makes an input in one of those 5 cells, ROW 14 should unhide
...
ROW 21 = hiden if C20 & D20 & E20 & F20 & G20 are empty. From the moment the user makes an input in one of those 5 cells, ROW 21 should unhide

Same store on ROW 41 till 50; 65 - 74; 80 - 89; 92 - 94.
Always one row that is always visible by default (the first one), and the ones behind become visible from the moment user makes an input in the row before.

It's important that the code has no restrictions on the amount of rows, because rows will be inserted or deleted in time (concerning rows 4 - 11; 22 - 40; ...)
Next thing very important to me, is that the excisting macro's (color cells with comment & make user input auto uppercase) keep working.
And last but not least: if C12 & C13 & C14 has an value, and the user deletes C13, C14 should keep being visible (in my thread before it was hiding rows with values in).

Hope I made it clear enough
Thanks for any help in advance !

Hi xladept,

The code does auto hide and unhide, but not the rows that I need to be hiden or unhiden.
Entering a value in C12 (eg) doesn't unhide row 13.
Row 22 is a fixed value and should always be visible. But it hides from the moment Row 21 is blank.
And another problem: if C12, C13 and C14 are not blank, and I delete the value in C13, row 14 hides while it is not empty.

Grtz
Kristof

Ok thanks man !

I am trying to create a macro in cell B35. Cell C35 captures a value from other worksheets. There is a circular reference. I want B35 to have a macro that will continue with the circular reference until the value in B35 is equal to or close to the value in C35. the circular reference should stop when the values are close.
Right now I have to copy paste the value from C35 to B35 several times until the values are equal.
Can someone help? I dont know how to write macros.

Originally Posted by xladept

Hi Louvaek,

Try this:

Please Login or Register  to view this content.

Hi xladept,

That code works fine for the hiding part.
Only the unhide doesn't work. If I enter a value in C12, row 13 should unhide, so the user always has one empty line to make a new entry.

Originally Posted by jaslake

Hi Kristof

I've modified the Code from your previous Thread...see if it does as required

Please Login or Register  to view this content.

Hello Jaslake,

Same issue with your code, the hide row thing works fine, but it doesn't unhide when a value is entered.
E.g. : c12 ==> user inputs a name, C13 should unhide so user has a new blank line for a new name.

Grt
Kristof

That's strange. Your file that you attached does not work to for me
I've put the letter F in C12, C13 doesn't unhide

Don't understand it ...

Originally Posted by jaslake

Hi Kristof

I repeated this on your upload and it worked as expected...what Version of Excel are you running...you have, I assume, Enabled Macros.

Excel 2007 ... not enabling macro's would be really stupid and the hiding part works fine
What version are you working with?

I found a very strange thing Jaslake

If C12 is blank, and I press DELETE, C13 unhides. If I'm on C13 and push DEL, C14 unhids, and so on and so on.

If found the problem, but now I have another one :p

In my file, I had a module too, wit ha vba to trigger cells with comments (so I could add conditional format).

From the moment I cleared that module, your code did work.
But now my 'comments' code doesn't fit in

Function HasCmt(Rng As Range)
Application.Volatile
HasCmt = Not Rng.Comment Is Nothing
End Function

That's the comment triggering. Can I fit in this code in my worksheet code?

Well, I need the cells with a comment made by user, to be different from the rest.
So I was looking to do something with conditional formatting. But you can't use cond. format if the cell has a comment, so I used that code (I found it on this forum) to help out the standard Cond. F in Excel.

Everything in the sheets in my workbook should be highlighted after inserting a comment. And that worked perfectly, but now I had to delete it to make your code work

Originally Posted by jaslake

Hi Kristof

Are there any Comments in your most recent Upload? Point us to an example we can work with.

In my upload now, I have 3 cells in blue with white font. That's the Cond Format rule '=hascmt()'.

Right click, insert comment, and that code did the rest.

There is not really a step by step problem.
The book you sent me in you're last reply, works fine except the auto unhide.
If I fill in C12, row 13 doesn't unhide.

BUT:
From the moment I remove the 'Function HasCmt' in Module 1, the auto unhide starts working.
But then the auto format cells for cells with comments doesn't work anymore, because I had to remove the code to make your hide/unhide code work.
I see you do know what the auto format cells means, because you inserted comment in the sheet to test it.

Originally Posted by xladept

Hi Louvaek,

Does that mean that my code also works now?

Xladept,

No I wish it would ... Tried it out again and removed the 'hascmt' code too, but the unhide still doesn't work with your code.

Could somebody on this forum test out the latest attachment from Jaslake too, and reply if it works?
It doesn't when I try, except when I remove the Module 1 ...

Please do tell me if ROW13 unhides when I fill in C12
Becoming really stressed about this

Originally Posted by jaslake

Hi Kristof


I don't know...I can't duplicate the issue. Hopefully another will test to see what's happening for them.

For me, C12 is blank and Row 13 is hidden. I enter F into C12 and Row 13 becomes visible. I enter G into C13 and Row 14 becomes visible.

Insert a Comment on any cell in Columns C through G and CF works.

I've ask the Cavalry to look at and test it.

Comments indeed do work, for me too.
Just the unhide doesn't. I attached a little screenshot just to show I'm not messing with you

Originally Posted by xladept

Hi Louvaek,

It works like this:Attachment 359478

Yes the unhide en hide things works in jour book, but the comments don't.
I see you took them out by making them text instead of code.
From the moment I take away your ' s, the unhiding stops

And should you know what's the reason the simple formula's in I4:M11 stopped working?
It's a simple VLookup that worked before editing with hide-unhide-code.

Originally Posted by jaslake

Hi Kristof

Unfortunately, I can't view this image either. I've had this issue for several weeks and it's been reported.

Language can sometimes cause issues regarding understanding. I think your English is fine as I know what you wish the end result to be.

One thing for which you may have not gotten my meaning...when I said Step by Step. I was looking for the actual Keystrokes you make. I wanted to duplicate your Keystrokes to see if I could duplicate the problem you're having.

We'll get it sorted...hang in there.

Well by actual keystrokes you mean type out every move or push on the keyboard?
I open the .xlsm that is working just fine for jou. I go to C12 and type a name in, random.
Row 13 doesn't unhide ...

There's no much more to say 'bout that. We have the same worbook, sheet, the same Excel version, and try to do the same thing.
Maybe it's an excel option?

I really have no clue. I hope that this person you call Cavalry, can help me out
I'll see it tomorow, it's 00u48 AM over here

For me, the unhiding/hiding is way more important than the comment highlights.
It would be nice to have them both, but If I had to choose, I would go for unhiding every time.

But stil, my hopes are on for someone to find a solution

I have to start working on the rest of the workbook now, so I'll leave out the CF thing ...
I want to say, I really like the help I've got here, especially from xladept and jaslake!

The reason why this thread has been opened, was the hide-unhide problem, and that's solved so I am a happy man
Admin's can close it now.

Thx again, you 2 rock

Originally Posted by TMS

OK, I have tested the HasCmt UDF and it is definitely a problem

Regardless of whether or not the Application.Volatile is present in the function, the function prevents the insertion of rows IF the Target cell is in the area covered by the Conditional Formatting (which uses the function). If the Target cell is in, say, column A or B, the Worksheet Change event works effectively.

See the simple workbook which demonstrates the problem.

Regards, TMS

Hey TMS,

Thx for investigating this, but I'm a big VBA noob and I have still lot to learn about this.
I do not understand half the things you are saying That code you gave, is that a code that I can paste in my sheet, or is it written for a example sheet like in your last post?

grt
Kristof

TMS,

What attatchment or sheet did you use? Because when I copy your code into my sheet, it doesn't work (except for the UCASE).
Can you take the attachement where you tried it on, and attach it to your response?
If you didn't try it on an attachment, could you just copy it in one where it should work?


grt
Kristof

Originally Posted by xladept

Hi Louvaek,

Thanks for the rep! - If you run this again after the dust settles, it will unhide your rows

Please Login or Register  to view this content.

It indeed does the job Xladept ! Thanks
I'm afraid the dust will never go away, I do not know enough VBA to understand some poeple over here and I don't have the knowledge to put their codes into my worksheet

Xladept,


I used your code, because it was the only one working after I edited the file and changed the last details (mostly formatting).
Now my file is ready. I'll attach it, in case you are interested in helping me once again.

So your code works fine, but the only thing is: my sheet is so slow each time a make a change in the worksheet.
My laptop at home is one of the newest, and here it works between fast and slow. But at work, we have old computers and each time I input a value, it takes half a minute before I can work again.

Is that something that can be changed? Or what can I do to make it work faster?
The sheet attached has the name '01'. That's a week number, so 51 more sheets like these will be added to the excel file
I guess that won't help the speed

Hope you, or anybody else that's willing to be so friendly, can help me out once again, it would be great !!

Thx!
Kristof