Foutmelding: objecten die van het blad schuiven

[gGerretje]

Ik krijg in sommige bestanden vaker een foutmelding als ik regels wil invoegen.
Ik kan dan geen regels invoegen omdat er dan objecten van het werkblad geschoven zouden worden.
Waarschijnlijk heb ik bij het kopiëren van een aantal opdrachtknoppen ergens een object (of meerdere) over het hoofd gezien.
Die gaat dan op een of andere manier een eigen leven leiden en komt ergens terecht waar je hem niet meer ziet.
Met de bewuste foutmelding als gevolg.

De aangedragen oplossing (in het foutmeldingsscherm) biedt weliswaar even oplossing, maar niet veel later krijg je de melding weer.
Waarschijnlijk nog ergens een object rondslingerend.

Zo blijf je bezig.
Wat ik nu graag zou willen is die bewuste objecten opsporen (macrootje?)
Een voorgestelde oplossing zou bijvoorbeeld zijn door alle objecten één voor één te selecteren en als de object.top of object.left groter is dan bijvoorbeeld 2000 dan object.top=0.
Enfin, volgens mij is het dan al zo goed als klaar.
Nu nog de code om die objecten te selecteren.

Wie helpt me met dat laatste stukje?

Groetjes,
Ger

 

[HSV]

Test het eens Ger.

Sub hsv()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
 If sh.Top > 2000 Then
   sh.Top = Cells(1).Top
   sh.Left = Cells(1).Left
 End If
Next sh
End Sub

 

[gGerretje]

Hallo Harry,

Bedankt voor je reactie.
Jouw oplossing is de perfecte aanvulling van wat ik bedoelde.
Alleen .... het probleem is er helaas niet mee opgelost.

Ik dacht dat er nog ergens wat van die losgeslagen besturingselementen zouden rondslingeren.
Niet dus.
Om jouw oplossing te testen heb ik maar ergens een knop neergezet, en die wordt netjes naar de cel A verplaatst.

Maar ik moet de vraag dus laten staan.
Hoe kom ik van het probleem van die van het werkblad afschuivende objecten af.

Groetjes,
Ger

 

[gGerretje]

O, ja.
Voor de duidelijkheid.
De voorgestelde oplossing (bij de foutmelding) om naar de laatste niet-lege cel te gaan met CTRL+END levert een sprong naar cel IV65505, volgens mij de laatste in Excel.
En die is in mijn bestand leeg.

 

[HSV]

Het laatste rijnummer in Excel 2003 is 65536.

Misschien een optie om het bestand hier te plaatsen?

 

[gGerretje]

Hallo Harry,

Je hebt gelijk. Stom van me. Ik wist het aantal rijen niet meer precies maar 65505 was het natuurlijk niet. Had ik zelf kunnen weten. 2¹⁶ h�.
Er bleken nog een paar verborgen rijen te zitten.
Ik heb het bestand van zijn gegevens ontdaan (en uiteindelijk zo goed als alles moeten weghalen).
Gezipt is het nu nog steeds te groot. Waarom weet ik ook niet, want er staat zo goed als niets meer in.

Ik wil je het bestand wel apart even toesturen.
Het is een bestand met normaal gesproken alle adressen van familie, vrienden en bekenden.
Op het blad Vakantie staan dan de adressen van reisgenoten.
Ik heb maar van ��n groep het kader laten staan.

Ga ik onder die groep staan en selecteer dan zo'n 100 rijen dan kan ik meestal een keer 100 rijen invoegen.
Daarna levert een of twee maal op F4 drukken de genoemde foutmelding.

De voorgestelde oplossing heb ik al een aantal keren uitgeprobeerd, vandaar mijn vermoeden in de oorspronkelijke vraagstelling.
Ik ben intussen wel nieuwsgierig geworden naar de oorzaak van het probleem.

Groetjes,
Ger

 

[Cobbe]

Zitten er opmaken voor hele kolommen en/of rijen in?
Zitten er formules in die verwijzen naar volledige kolommen/rijen?

 

[gGerretje]

Hallo Cobbe,

Ja inderdaad.
Ik heb van complete rijen of kolommen de opmaak naar andere rijen of kolommen gekopieerd.
Geen verwijzingen naar hele rijen.
Heb nu alle opmaak verwijderd (alle cellen van het blad hebben nu 'geen opvulling') en nu krijg ik de foutmelding niet meer.
Er blijken dus geen niet-lege cellen te zijn die van het blad worden verschoven.
Mag ik daaruit afleiden dat Excel die opgemaakte cellen als niet-leeg beschouwt?
Overigens zouden dan toch de laatste rijen worden weggeschoven? Die hadden namelijk geen opmaak.

Vreemd bijverschijnsel: de bestandsgrootte gaat (na het weghalen van die opmaak) van 1,74MB naar 2,09MB.

Hoe kan ik trouwens die bestanden kleiner krijgen zodat ik ze wel als bijlage kan toevoegen?
M.a.w. waar zit 'm de bestandsgrootte precies in?

Beiden bedankt voor de hulp.

Groetjes,
Ger

 

[Cobbe]

Copiëer het hele gebruikte bereik en plak in een nieuw blad.
Verwijder dan het eerste blad en sla uw bestand op.
Als je nu terug opent zou alles naar normale proportie teruggebracht moeten zijn.

 

[gGerretje]

Hallo Cobbe,

Dat is inderdaad een optie.
Zeker het overwegen waard.
Maar alleen als je blad uit louter celinhoud en opmaak bestaat.
Als je twintig opdrachtknoppen hebt moet je die weer allemaal opnieuw een naam geven.

Groet,
Ger

 

[VenA]

Een mogelijke oplossing is alle lege rijen selecteren en deze verwijderden. Hetzelfde voor lege kolommen. Vervolgens je bestand opslaan en dan weer openen.

 

[gGerretje]

Hallo VenA,

Ik heb hem even geprobeerd.
Dat werkt ook.
Ik neem aan dat de gedachte erachter is, dat je daarmee dus de opmaak van die verwijderde cellen ook meteen mee verwijdert.
Met andere woorden: door de onderste 65000 rijen te verwijderen zijn dus al die kolommen met opmaak (zoals Cobbe zegt) niet meer als hele kolom opgemaakt.
Is dat inderdaad wat je bedoelt?

 

[HSV]

Hier heb ik een testbestandje voor je gemaakt Ger.

Bij het openen van het bestand geeft het de posities aan van elke shape.
Bij het invoegen van extra rijen gaat elke shape terug naar de oorspronkelijke positie.
De posities worden genoteerd in Blad2.
De codes staan in ThisWorkbook.

Misschien dat je hier iets aan hebt.

Private Sub Workbook_Open()
Dim sq, sq1, Sh As Shape
Application.EnableEvents = False
 With Sheets(1)
  For Each Sh In .Shapes
    sq = sq & Sh.Top & "|"
    sq1 = sq1 & Sh.Left & "|"
      Next Sh
    End With
  With Sheets(2)
   .Range("A1") = sq
   .Range("A2") = sq1
  End With
  Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
 With Sheets(1)
  For i = 0 To .Shapes.Count - 1
   .Shapes(i + 1).Top = Split(Sheets(2).Range("A1"), "|")(i)
   .Shapes(i + 1).Left = Split(Sheets(2).Range("A2"), "|")(i)
     Next i
   End With
End Sub

 

[gGerretje]

Hallo Harry,

Dat laatste bestandje doet niets.
Ik kan de knoppen zelfs niet eens activeren.
Gaf overigens wel een foutmelding bij de eerst opstart (direct na het kiezen voor Macro's Inschakelen, bij For Each Sh In .Shapes
Maar ik was al geholpen met je oorspronkelijke code. Al was dat uiteindelijk niet de oplossing van het probleem.

Ik ga het wel nog uitproberen door de code onder een nieuw aan te maken knop te zetten.

Bedankt ,
En alvast de allerbeste wensen voor het nieuwe jaar. Ook aan alle andere helpers van deze site.

Groetjes,
Ger

 

[HSV]

Dag Ger,

Het bestandje doet heus wel iets, anders had ik het niet geplaatst natuurlijk.
Je hoeft alleen maar nieuwe rijen in te voegen, en te constateren dat de shapes op hun plaats blijven.
Om het te verduidelijken heb ik een regel geel gemaakt.
Voeg bij de geselecteerde rijen nieuwe rijen in, dan zie je het wel.
Aan de knoppen hangt geen code zoals je meende.

 

[gGerretje]

Hallo Harry,

Ik had inderdaad gezien dat er geen code achter de knoppen zat.
Maar het bestandje moet eerst geconverteerd worden voor Excel 2003.
Dan moet ik kiezen voor Macro's inschakelen en krijg ik een foutmelding op die eerder genoemde regel.
Ik dacht dat het daaraan lag dat er geen code werd meegenomen.
De shapes gaan ook gewoon mee naar beneden als ik een regel invoeg.

Maar het probleem was niet dat er objecten verdwenen.
Het probleem was dat ik regels invoegde en er kennelijk gegevens van het werkblad geschoven dreigden te worden (volgens die foutmelding).
Ik was dus in de veronderstelling dat er losgeslagen knoppen waren die ik niet kon zien.
Dat was dus niet het geval.
Het probleem is nu opgelost door die verborgen kolommen en rijen weer zichtbaar te maken.

Op dit moment kan ik dus niet veel meer met je code.
Neemt niet weg dat ik die nog wel hoop te kunnen gebruiken.
Bedankt in ieder geval voor je hulp.

Groetjes,
Ger

 

[HSV]

Hallo Ger,

Ik wist niet dat je met Excel 2003 werkte.
Mooi dat het is opgelost.
Voor de geïntresseerden dan maar in die versie.
Nu zou je geen foutmelding moeten krijgen.

Succes ermee.

 

[gGerretje]

Hallo Harry,

Ik wil je toch nog even laten weten dat deze laatste versie wel precies doet wat je zegt.
Zonder die foutmelding vooraf.
Bedankt.

Groetjes,
Ger