none
For Each inside another loop works only once after excel startup RRS feed

  • Question

  • Hello guys,

    I have an macro I am fighting last 5 days with no luck.

    I am doing FOR loop every row filled and hidding shapes (pictures) based on value in Column C in every row.
    So far I am only in stage that I wish to FOR loop of all shapes and hide or show (just to test that everything works fine so far).

    The issue is, that the macro works fine the first time I run it, and if I run it again, I got an error... When I close excel and open again, then I can run the macro once only :/.

    Code:
    Sub ds()
    
        Dim lastrow As Long
        lastrow = Worksheets("Task_List").Cells(Rows.Count, 1).End(xlUp).Row
    
    
        Dim ShapeAction As Shape
    
    
        Dim i As Integer
        For i = 6 To lastrow
    
    
            For Each ShapeAction In Worksheets("Task_List").Shapes
                If ShapeAction.TopLeftCell.Row = i And ShapeAction.TopLeftCell.Column = 14 Then
                    MsgBox ShapeAction.TopLeftCell.Row & "-" & ShapeAction.TopLeftCell.Column
                End If
                If ShapeAction.TopLeftCell.Row = i And ShapeAction.TopLeftCell.Column = 15 Then
                    MsgBox ShapeAction.TopLeftCell.Row & "-" & ShapeAction.TopLeftCell.Column
                End If
    
    
            Next ShapeAction
        Next i
    
    
    End Sub

    In these past 5 days I have tried everything I could posibly find out on the internet, the different kind of IF conditions, such for example If Not Intersect(Range(Cells(i, 14), ActionShape.TopLeftCell) Is Nothing Then (and so many other ways to find shape in a cell.

    Getting always same error on IF statement.

    EDIT:

    I am lot of frustrated with this... I dont understand the behaviour.


    Sometimes I isert new row and it works, another time it doesnt work.
    Then Sometimes helps if I save the excel first before run the macro.

    It is like something is keeping in cache, I tried to:
    Set ShapeAction = Notning before Next ShapeAction but it didnt help...

    Sometimes when the sheet changed (random cell which was before empty is filled now) works, sometimes not :/

    Sunday, December 3, 2017 7:59 PM

All replies

  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 3, 2017 8:29 PM
  • Hello,

    Dropbox link:

    https://www.dropbox.com/s/ak7z1vmcgwsb4yq/Tasks.xlsm?dl=0

    To make it work perfectly, please add a row inthe the tab HIEARCHY where first column is your office username (usualy same as windows login name) and then type ur account as Admin.

    Then few time add a new row by button "Assign new row" and then try runing macro "ds", sometimes it works once or twice, then i got error.

    Monday, December 4, 2017 8:48 AM
  • Thanks, that is helpful. The problem is caused by the data validation dropdown arrows in column E. They are included in the list of shapes as shapes of type msoFormControl, but they don't fully act like shapes - you cannot get at their TopLeftCell. Hence the error.

    You can get around this by testing for this in the loops through the shapes:

            For Each ShapeAction In Worksheets("Task_List").Shapes
                If ShapeAction.Type <> msoFormControl Then
                    If ShapeAction.TopLeftCell.Address = Cells(i, 14).Address Then
                        ShapeAction.Visible = False
                    End If
                    If ShapeAction.TopLeftCell.Address = Cells(i, 15).Address Then
                        ShapeAction.Visible = False
                    End If
                End If
            Next ShapeAction

    Alternatively, you could use an error handler.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, December 4, 2017 1:53 PM