[Solved] VBA: Question about For Statements and Hiding Excel

Damo · Nov 21st 2003

These are basically two seperate questions.

I have a For Each loop, with a nested if statement inside, is it possible to have a Next inside the if statement, so that if the IF is true it goes to the next loop. Basic layout shown below, if this wont work can someone show me a Goto statement.

For Each Cell in Range
If cell.value = empty then
Next
End If
Next

My other question is I have a userform creating a number of reports and I want to hide excel in the background rather than having my spreadsheet available. Any way to do this and keep my user form visible?

dkabambe · Nov 21st 2003

Instead of an extra next, put a label just before the next and a goto where you want to skip to the next in the for loop.

For Each ... in ...
If testCondition Then GoTo nextOne:

...code...

nextOne:
Next

HTH, Dzinja

**Andy Pope** · Nov 21st 2003

Hi Damo,

Rather than increment the NEXT manually within the FOR..NEXT structure could you not change the structure of the IF THEN statement.

So instead your structure would be;
For Each Cell in Range
If cell.value <> empty then
..do something
End If
Next

If you do move the cell on surely you will have to repeat the code used to check if the cell is empty. I am a bit unclear as to why you need to NEXT your self rather than let the FOR..NEXT do it.

As for your second question, you can make Excel invisible by using Application.Visible = False
But I would not advise it.
Not sure what would happen if your macro broke down whilst Excel was invisible. How would the user get it back?

Having a modal userform, this is the default action, will stop the user being able to get at the worksheet whilst your userform is open.

Maybe its the screen falshing and jumping around during execution you want to get rid of. In which case use Application.screenupdating = False

Or how about displaying a blank worksheet whilst your userform is open.

Cheers
Andy

Damo · Nov 21st 2003

I have over simplified the problem concerning the if statement, it basically is a very big struture so rather than wasting time running throught the same code repeatedly, the if statement would move to the next cell and proceed skipping the other statements. I will use the goto statement.

Its for presentation purposes, only in that, I have a large database and a userform and I dont want my users to be able to see the database but they do have to be able to see the userform. I will test your solution before implementation.

[Solved] VBA: Question about For Statements and Hiding Excel

Participate now!

Share