Hello.
Iv made this little code, its in difficulty a bit higher than my skill (copy pasted most of it, but i know what is what)
Sub UpdateList()
Dim oCheck As OLEObject
Dim rCell As Range
maxdni = 40
maxwpisow = ActiveSheet.Cells(55, 1) + 5
breake = ActiveSheet.Cells(58, 1)
i = ActiveSheet.Cells(56, 1)
j = ActiveSheet.Cells(57, 1)
koniec = False
nextstep = True
Do
Set rCell = ActiveSheet.Cells(i, j)
With ActiveSheet.OLEObjects.Add(classtype:="Forms.Image.1", _
Top:=rCell.Top, Left:=rCell.Offset(0, 0).Left, _
Height:=rCell.Height, Width:=rCell.Offset(0, 0).Width)
If rCell.Value = 1 Then
.Object.Picture = LoadPicture("W:\WCM\FILARY\WO\NOWE\MD1\KALENDARZ i standardy\pełny.bmp")
Else
.Object.Picture = LoadPicture("W:\WCM\FILARY\WO\NOWE\MD1\KALENDARZ i standardy\pusty.bmp")
End If
End With
'Check if its final cell
If i = maxwpisow And j = maxdni Then
koniec = True
nextstep = False
End If
'Move to next row
If j = maxdni Then
j = 10
i = i + 1
nextstep = False
Else
'Move to next cell in same row
j = j + 1
End If
Loop While nextstep = True
If koniec = False Then
'make a short breake (experimental)
If breake = 0 Then
alertTime = Now + TimeValue("00:00:10")
Application.OnTime alertTime, "updatelist"
Else
ActiveSheet.Cells(58, 1) = 0
End If
ActiveSheet.Cells(56, 1) = i
ActiveSheet.Cells(57, 1) = j
Else
ActiveSheet.Cells(56, 1) = 6
ActiveSheet.Cells(57, 1) = 10
End If
End Sub
It makes first row in around 5 secs, second row in 10-20 secs, third takes long, never allowed it make 4th run.
Allready tested option to stop macro and let excel rest for couple secs, same delay.
Comments changed to english, fixed minor bug
Made some more experiments - starting from different row returns same incrasing delay (doesnt matter where is first one)
While "on breake" excel is running smoothly.
Some explanation to macro:
Its some sort of calendar, days in columns, actions to be taken in rows. Cells inside filled with "1" (first picture should be inserted) or "0" (second one).
Bookmarks