VBA Macro - Do While trouble

[BestiaFPV]

Hey guys, I have a macro that I want to keep doing over and over until I change a the value in A7

I have worked out the action of the Macro (rotating pages)

But I am stuck with the Do While

Here is my code, thanks in advance for the help

Sub Rotating()Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
End Sub


Sub ShowNextSheet()
    Dim lastIndex As Integer, nextShtIndex As Integer
   
   Do While Range(A7) = "Yes"
    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1


    If nextShtIndex <= lastShtIndex Then
        Worksheets(nextShtIndex).Select
        Rotating
    Else
        
    Sheets("Running Sheet").Activate
        
    End If
    Loop
End Sub

 

[JackDanIce]

It's not clear what you mean by "stuck", what is wrong with the DO WHILE LOOP? At a guess, it maybe be which A7 cell it refers to is not fixed to a specific worksheet; (untested) try:

Sub Rotating()

    Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
    
End Sub

Sub ShowNextSheet()
    
    Dim x   As Long
    
    With Sheets("Running Sheet")
        Do While .Range("A7").Value = "Yes"
            x = ActiveSheet.Index + 1
            If x <= Worksheets.Count Then
                Worksheets(x).Select
                Rotating
            Else
                .Select
            End If
        Loop
    End With
    
End Sub

 

[BestiaFPV]

My Issue is when I run my Macro I get

Run-time Error '1004'
Method 'Range' of object '_Global' failed


@JackDanIce - Thanks - but when I use yours, it doesnt use the 5 seconds

This is a working verison (with out the Checking of A7)

[/I][/COLOR]Sub Rotating()Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
End Sub


Sub ShowNextSheet()
    Dim lastIndex As Integer, nextShtIndex As Integer
   
    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1




    If nextShtIndex <= lastShtIndex Then
        Worksheets(nextShtIndex).Select
        Rotating
    Else
        
    Sheets("Running Sheet").Activate
        
    End If
   
End Sub


[COLOR=#574123][I]

 

[MARK858]

What happens when you put quotes around the cell reference in your original code?

 Do While Range([COLOR="#FF0000"]"[/COLOR]A7[COLOR="#FF0000"]"[/COLOR]) = "Yes"

 

[JackDanIce]

There seems to be no difference between your reply in #3 and what I posted in #2 other than Application.Ontime is on the same line as Sub Rotating() (which it shouldn't be I believe).

Have you tried Mark's suggestion (I'd auto included those in the suggested code as well)? I just ran the suggested code and I do not get the error you mention and does use the 5 seconds.

 

[BestiaFPV]

@JacKDanIce - Well there is a big difference in the result

@Mark8585 - It doesnt give me the error so I must be on the right track.

I think either my placement with the loop is off, or I am not telling it to check the right cell (which I think it is)

I think instead of having a range of A7 - I need it to have a range Running Sheet - A7

So I will be looking up how to do that

 

[BestiaFPV]

O.k so I found a solution

While kind of - it rotates just like I want, it stops when I want. I just need to work out how stop it from rotating the second workbook I have open when I click on that. ( A way to confine the Macro just to the work book called "Display Page")

Here is my current code

Sub Rotating()
Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
End Sub




Sub ShowNextSheet()
    Dim lastIndex As Integer, nextShtIndex As Integer
   
 
    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1




    If nextShtIndex <= lastShtIndex Then
        Worksheets(nextShtIndex).Select
        Rotating
    Else
        
    If Sheets("Running Sheet").Range("A7") = "Yes" Then
       Sheets("Running Sheet").Activate
        Rotating
        
        Else
       Sheets("Running Sheet").Activate
        
       End If
       
    End If
  
End Sub

 

[BestiaFPV]

Here is what I am currently trying (and it is not working

Sub Rotating()Application.OnTime Now + TimeValue("00:00:30"), "ShowNextSheet"
End Sub




Sub ShowNextSheet()
    Dim lastIndex As Integer, nextShtIndex As Integer
   
    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1


    If nextShtIndex <= lastShtIndex Then
        Workbooks("Display page.xlsm").Activate
        Workbooks("Display page.xlsm").Worksheets(nextShtIndex).Select
        Rotating
    Else
        
    If Sheets("Running Sheet").Range("A7") = "Yes" Then
       Workbooks("Display page.xlsm").Sheets("Running Sheet").Activate
        Rotating
        
        Else
        Workbooks("Display page.xlsm").Sheets("Running Sheet").Activate
        
       End If
       
    End If
  
End Sub

It currently gets stuck on - Workbooks("Display page.xlsm").Worksheets(nextShtIndex).Select - I get the error " Run-time error '9': Subscript out of range

And clues on where to look to fix that?