[LØST] Excel (2007) Macro, lese av CheckBoxer

[Finholt]

Mitt mål er å åpne en serie excelfiler, lese av en serie celler og checkboxer og dumpe dette inn i ett excelark. Jeg er ganske ny innen macro, så det er litt prøv og feil og se hva som skjer metodikk. Jeg har fått til å loope gjennom filer og kopiere innhold i bestemte celler inn i den excelfila jeg jobber med, altså, samle inn data fra flere hundre excel-filer (likt oppsett) inn til ei excel-fil.

 

Men når det kommer til checkbokser sliter jeg litt, så jeg har foreløpig bare drevet lokalt på ett regneark. Her er hva jeg har prøvd, både eksempler fra nettsider og noen selvmodifiserte, med påfølgende feilmeldinger.

 

Jeg fant følgende eksempelkode herfra herfra for hvordan det kunne løses, men jeg får det ikke til å fungere. (pil indikerer linje der feil oppstår, og kommentarer er hva jeg vil/mener/tolker koden skal gjøre):

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
--> If CheckBox1.Value = True Then                          ' Hvis den er huket av
       Sheets("Sheet2").Visible = True                     ' vis Sheet2
   Else                                                    ' Hvis ikke
       Sheets("Sheet2").Visible = False                    ' gjem Sheet2
   End If
End Sub

Feilmelding: runtime error '424': Object required

 

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
--> If Sheets("Sheet1").CheckBox1.Value = True Then         ' Hvis den er huket av
       Sheets("Sheet2").Visible = True                     ' vis Sheet2
   Else                                                    ' Hvis ikke
       Sheets("Sheet2").Visible = False                    ' gjem Sheet2
   End If
End Sub

Feilmelding: runtime error '438': Object doesn't support this property or method

 

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
--> If Sheets("Sheet1").CheckBox1 = True Then               ' Hvis den er huket av
       Sheets("Sheet2").Visible = True                     ' vis Sheet2
   Else                                                    ' Hvis ikke
       Sheets("Sheet2").Visible = False                    ' gjem Sheet2
   End If
End Sub

Feilmelding: runtime error '438': Object doesn't support this property or method

 

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
   If CheckBox1 = True Then                                ' Hvis den er huket av
       Sheets("Sheet2").Visible = True                     ' vis Sheet2
   Else                                                    ' Hvis ikke
       Sheets("Sheet2").Visible = False                    ' gjem Sheet2
   End If
End Sub

Feilmelding: Ingen feilmelding, Sheet2 gjemmes, men det forblir gjemt selv etter at CheckBox1 er huket av igjen.

 

 

Det er tydelig at statussjekken feiler, den gir tolker alltid checkboxen som fasle, selv om den er huket av. Det er også vært av interesse å mellomlagre statusen i en boolsk variabel, men det feiler også:

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
   Dim bStatus As Boolean                                  ' boolsk mellomvariabel
--> bStatus = Sheets("Sheet1").CheckBox1                    ' Status fra checkbox mellomlagres
   Sheets("Sheet1").Range("A2").Value = bStatus            ' Verdi legges i cellen A2
End Sub

Feilmelding: runtime error '438': Object doesn't support this property or method

 

 

Private Sub CheckBox1_Click()               ' Når Check Box 1 endrer tilstand
   Dim bStatus As Boolean                                  ' boolsk mellomvariabel
   bStatus = CheckBox1                                     ' Status fra checkbox mellomlagres
   Sheets("Sheet1").Range("A2").Value = bStatus            ' Verdi legges i cellen A2
End Sub

Feilmelding: Ingen feilmelding, A2 settes til FALSE, og intet mer skjer.

 

 

Også denne (som er tilsvarende det jeg vil bruke for å lese av i de andre filene) feiler:

Sub sjekkboks()
   Sheets("Sheet1").Range("C10").Value = Sheets("Sheet1").Shapes("Check Box 1").Value
End Sub

Feilmelding: runtime error '438': Object doesn't support this property or method

 

 

Noen idé om hva jeg gjør feil her? Bruker excel 2007. Jeg så at den jobbet i kompatibilitetsmodus, men selv da jeg prøvde i 2007 format fikk jeg de samme feilmeldingene.

Jeg har prøvd å søke på google, men finner ikke svar på akkurat det jeg lurer på. Det som ofte er foreslått, fungerer heller ikke hos meg (som første eksempel). Jeg vet man kan linke avkryssningsbokser til celler. Det kan ikke jeg benytte meg av, da jeg skal lese av andre filer uten å endre de. Der er de ikke knyttet til noen celle, alle står fritt plassert på et av regnearkene i fila.

 

Forslag?

Endret 12. januar 2011 av Finholt

[Harald Staff]

Hei

 

Koden

Private Sub CheckBox1_Click()

ligger i en regnearkmodul eller en userformmodul og kjører idet dennes egen ActiveX-checkbox ved navn CheckBox1 klikkes på. Og bare da. Den kjører ikke hvis du åpner en fil og prøver å se status til boksen der.

 

Så spørsmål 1 er hva slags checkbox det er, er det fra skjemaverktøyene eller fra ActiveX-kontrollverktøykassen?

[Finholt]

Hei, takk for svar :-)

 

Jeg vet at "Private Sub CheckBox1_Click()" bare blir kjørt når checkboxen endrer tilstand. Jeg har ikke til hensikt å bruke en sånn funksjon, det var bare noe jeg kom borti da jeg prøvde meg fram.

 

Det er noe alá den siste kodesnuttetn jeg vil få til, og der også har jeg prøvd med forskjellig syntaks, tilsvarende de øvrige kodeeksemplene, men ender opp med samme feilmeldinger.

 

Bare så vi er enige om forskjellen, den activeX kontrollen blir markert omtrent som et bilde, og er den markert står det "CheckBox1" (uten mellomrom) i cellereferansen, mens den andre ser mer ut som en tekstboks, og merket står det "Check Box 1" (med mellomrom) i cellereferansen.

 

Så da er det denne typen, under "form controls", som jeg har satt inn, ikke fra activeX. Se bildet. Det er for øvrig også denne typen checkbox som er brukt i filene jeg skal hente data fra, de merkes i hvertfall som tekstbokser og i cellereferansen står de med mellomrom.

 

 

Edit: Jeg tok en spansk en og gjorde et opptak av merking og fjerning av checkboxen.

Denne koden kan brukes til å aktivere/deaktivere en checkbox:

Sub Macro1Off()
   ActiveSheet.Shapes("Check Box 1").Select
   With Selection
       .Value = xlOff
   End With
End Sub

Sub Macro1On()
   ActiveSheet.Shapes("Check Box 1").Select
   With Selection
       .Value = xlOn
   End With
End Sub

Skulle jo tro det gikk an å lese av en checkbox like enkelt?

 

Jeg tok en råsjans, denne koden fungerte:

Sub GetMacro1()
   ActiveSheet.Shapes("Check Box 1").Select
   With Selection
       Range("B3").Value = .Value
   End With
End Sub

Om boksen er krysset av returneres verdien "1", er den ikke krysset av returneres verdien "-4146" (av alle ting..). Med en IF-setning får jeg ut TRUE / FALSE:

Sub GetMacro1()
   ActiveSheet.Shapes("Check Box 1").Select
   With Selection
       If .Value = 1 Then
           Range("B3").Value = True
       Else
           Range("B3").Value = False
       End If
   End With
End Sub

Men jeg syns dette var noe hårete, må da vel være en penere måte å gjøre det på?

 

Edit2: det fungerte ikke å bruke en slik kode for å hente ut verdi fra checkbox i de andre excel arkene. Fikk da følgende feil:

Runtime error '-2147024809 (80070057)': The item with the specified name wasn't found.

Enda jeg har dobbeltsjekket at en boks med det gitt nummere finnes, så litt tilbake til start nå.

Endret 6. januar 2011 av Finholt

[Harald Staff]

Sånt må adresseres ganske nøye til hvor de bor, hvilken fil, hvilket regneark. Men den gode nyheten er at Forms-kontroller er en collection, du kan altså lese dem av via numre. (Som med ark, tell arkfaner fra venstre -og ta høyde for evt skjulte ark)

 

Så se om dette bringer deg noe sted (endre arknavn og filnavn ved testing):

 

Msgbox Activeworkbook.Sheets("Ark1").Checkboxes(1).Value

 

Msgbox Workbooks("Sammendrag.xlsx").Sheets("Ark1").Checkboxes(1).Value

 

Msgbox Activeworkbook.Sheets(1).Checkboxes(1).Value

 

Og, med forbehold om dårlig husk, må løpe om et minutt rekker ikke teste, loope alle boksene:

 

Dim i as Long

For i = 1 to Activeworkbook.Sheets(1).Checkboxes.Count

Msgbox Activeworkbook.Sheets(1).Checkboxes(i).Value,,"Boks " & i

Next

 

HTH. Beste hilsen Harald

Endret 6. januar 2011 av Harald Staff

[Finholt]

Jeg fikk denne metoden til å fungere med automatisk åpnedde excelark likevel (jeg hadde skrevet feil navn på fanearket):

Sub GetMacro1()
 ActiveWorkbook.Sheets("arkNavn").Shapes("Check Box 1").Select
 With Selection
   If .Value = 1 Then
     Range("B3").Value = True
   Else
     Range("B3").Value = False
   End If
 End With
End Sub

Jeg prøvde kodesetningene dine også, men de vil ikke igjennom. og det er innmari dumt, hadde vært knall å få til en avlesning på ei linje.

 

Denne gikk ikke:

Msgbox Activeworkbook.Sheets("arkNavn").Checkboxes(1).Value

Runtime error '1004': Unable to get the Checkboxes property of the Worksheet class

 

Men denne gikk:

ActiveWorkbook.Sheets("arkNavn").Shapes("Check Box 1").Select
 With Selection
   Msgbox .Value
 End With

EDIT: Aha! Dette gikk:

Msgbox Activeworkbook.Sheets("arkNavn").Checkboxes("1").Value

Og da gikk dette også:

ThisWorkbook.Sheets("målark").Range("$AZ11").Offset(iRowN - 1, 0).Value = Workbooks(excelFile).Sheets("arkNavn").CheckBoxes("1").Value

Men verdien som hentes er fortsatt enten 1 eller -4146, så med mindre det er noen måte å få ut TRUE/FALSE direkte må man vel til med IF-setning?

Endret 7. januar 2011 av Finholt

[Harald Staff]

Du må bruke IF ja.

Jeg aner ikke hvorfor Checkboxes(1) ikke funker hos deg, det går som en drøm her og har alltid gjort det. Trippelsjekk at du er i riktig fil (activeworkbook er ikke alltid det vi tror), at arknavn stemmer, og at det i akkurat det arket i akkurat den filen finnes minst en checkbox fra forms-kontrollene.

 

Du er uansett på sporet av noe som virker ser det ut til, spørsmålet er vel mer om hastighet og eleganse herfra. Lykke til!

 

Beste hilsen Harald

[Finholt]

Jeg vet heller ikke hvorfor jeg måtte ha " rundt innholdet i parentesen for checkbos. Men det skal åkke som være variabelt, og med string-variabler går det fint. Også henter jeg kun verdi dersom den er huket av, ikke dersom den er tom. Da forblir ruten i målfila også tom, like greit.

 

For iBa = iBST To iBSL
sIB = iBa
If Workbooks(excelFile).Sheets(sSubSite).CheckBoxes(sIB).Value = 1 Then
 ThisWorkbook.Sheets(sTSEN).Range("$AW11").Offset(iRowN - 1, iBa - iBST).Value = True
End If
Next iBa

Elegant, vel, elegant får det uansett ikke blitt med mindre jeg kan hente ut slike data uten å måtte åpne fila først, lite elegant å se skjermen blafre gjennom 500 filer på 15 min. Men mer effektivt enn å gjøre det manuelt!

 

Takk for hjelp, settes som [LØST]

Endret 12. januar 2011 av Finholt

[Harald Staff]

Blafringen skal vi nok bli kvitt:

 

Application.Screenupdating = False

'prosedyren din her. Gjerne idet du åpner en og en fil:

Application.Statusbar = "Bearbeider " & excelFile

'så helt til slutt:

Application.Statusbar = False

Application.Screenupdating = True

 

Beste hilsen Harald

[Finholt]

Blafringen skal vi nok bli kvitt:

 

Application.Screenupdating = False

'prosedyren din her. Gjerne idet du åpner en og en fil:

Application.Statusbar = "Bearbeider " & excelFile

'så helt til slutt:

Application.Statusbar = False

Application.Screenupdating = True

 

Beste hilsen Harald

Ah, helt knall, da ble det litt mer behagelig å følge med!

Tusen takk for hjelpen, og god helg