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

  • 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?

  • 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

  • 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

    [h4]Cheers
    Andy
    [/h4]

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!