For Each inside another loop works only once after excel startup

[shokarta]

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 :/.

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.

See images as errors


EDIT:
Hello Guys,

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 :/

[Kenneth Hobs]

cross-posted https://www.ozgrid.com/forum/forum/h...irst-time-only

Welcome to the forum!

I don't see why that would be a problem unless i was not an Integer. Just to be safe, row variables should be dimmed as Long.

e.g.

Sub ds()  
  Dim lastrow As Long, i As Long, s As Shape, ws As Worksheet
  Set ws = Worksheets("Task_List")
  lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
  For i = 6 To lastrow
    For Each s In .Shapes
      Select Case True
        Case s.TopLeftCell.Row = i And s.TopLeftCell.Column = 14
          MsgBox s.TopLeftCell.Address, , 14 & s.Name
        Case s.TopLeftCell.Row = i And s.TopLeftCell.Column = 15
          MsgBox s.TopLeftCell.Address, , 15 & s.Name
        Case Else
      End Select
    Next s
  Next i
End Sub

[p45cal]

cross-posted https://www.ozgrid.com/forum/forum/h...irst-time-only

…and:
https://social.msdn.microsoft.com/Fo...p?forum=isvvba
and
https://www.excelforum.com/excel-pro...l-startup.html

[shokarta]

Hello, sorry for the crosspost,
I am in delay with this topic and stackover and mrexcel site did not get anywhere, thefore I decided to place it to couple more forums and I didnt know crossposting is an issue than.

But as far as I know it know, it will not happen again.

Kenneth,
your code gives me same error 1004 on line Case s.TopLeftCell.Row = i And s.TopLeftCell.Column = 14.

Dropbox link:
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.

[p45cal]

…and
https://stackoverflow.com/questions/...irst-time-only
and
https://www.mrexcel.com/forum/excel-...l-startup.html

I have come across this sort of behaviour several times while developing code, a loop control variable takes on the wrong value for no apparent reason. What I've done to get around it is to change the name of the loop control variable to something longer; in your case try changing all instances of the i to say, idx. If it works, you can probably change it back later!.
Also, you have many empty modules from which I infer you have a lot more code; check that you haven't declared i as global variable somewhere, and check that no event handler is being triggered that might also use i. It shouldn't matter if i is a local variable each time but…

Either way, changing the name of the loop control variable could solve it.

[p45cal]

Something else which might be far more relevant:
1. ShapeAction might clash; when I ask for Help on it I get Error in downloading DLL instead of the usual Keyword not found.
2. Do you have a function/sub called ShapeAction?
3. Long shot: Do you have SpreadSheetGear installed? (It has a ShapeAction event but I know nothing about SpreadSheetGear.)

First thing to do is to change the name of your ShapeAction object variable.

[shokarta]

Hello,

someone from crosspost:
https: // social.msdn.microsoft.com/Forums/en-US/56adfc5d-b075-4887-8673-d89788e53ed1/for-each-inside-another-loop-works-only-once-after-excel-startup?forum=isvvba

just explained the error, that the dropdown lists also behaves as shapes but they I can not get their position, therefore I am getting error.
I will try it tomorrows at work.

Regarding your ideas:
I will definitely change i to idx based on your idea and will remember this for the future not to use short variable names.
ShapeAction is not an sub name, but just a name of variable, I have also tried different names in order to try your idea too, but didnt help.
I do not use SpreadSheetGear.

BUT!!!!!!
From this morning only I am having the error "Error in loading DLL" which I have never had before !!!!!
But from works PC it seems to work OK without the error, so I do not know what happened, I havent changed this code at all so how the hell this error can be happening now when nothing at all changed?
O.o

I will tomorrow post if that helped

[Kenneth Hobs]

That is an odd deal on Drop Down as it should error every time. You can skip that error like this:

Sub ShapeNames()  
  Dim s As Shape
  For Each s In Worksheets("Task_List").Shapes
    If VBA.Left(s.Name, 4) <> "Drop" Then Debug.Print s.Name, s.TopLeftCell.Address
  Next s
End Sub

You can also use .Type or .AutoShapeType. e.g.

Debug.Print s.Type, s.AutoShapeType '8, -2 for Drop Downs - Ones you want: 13 ,1

I would recommend modifying your code to Lock and Unlock cells in mass rather than one at a time.

I like to Protect my sheets in ThisWorkbook Open event so I don't have to UnProtect/Protect in code. e.g.

Private Sub Workbook_Open()  
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "ken", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

When working with shapes I like to poke then into an array. I can then use Match to find the one I want or use the row and column of the array using the TopLeftCell. I think you have duplicate Shape names so that might not be best for you.