Checkbox op tabblad met VBA

[Mistic NV]

Ik heb een klein programmatje geschreven met een "loop" voor hety overbrengen van data van sheet1 naar sheet 2 of 3 naargelang de selectie in sheet 1 (taal Nederlands of Frans)

In sheet 2 (en 3) staan checkboxes.

De bedoeling is als de gebruiker de Nederlandse taal selecteerd, dat er een marco wordt opgestart die de sheet1 leest, en deze vertaalt (tekst en cijfers) omzet naar sheet 2 (dito voor sheet 3 als de selectie op sheet 1 Frans is).

De volledige cyclus werkt als men de macro opstart in VBA en deze laat uitvoeren stap voor stap.

maar als ik de macro automatisch wil laten starten, worden de checkboxes niet gereset.
Dus, blijft de selectie altijd op de eerste lijn uit sheet1 staan.

Voorbeeld :

Als er in rij 12, kolom 10 een "S" staat vermeld, moet deze op sheet2 de chexbox 10 activeren, enz... voor de rest van de kolommen, maar dan andere checkboxes.

Als deze naar rij 13 gaat, zouden alles checkboxes FALSE moet zijn en zou d emacro opnieuw moeten kijken naar de waardes vermeld in elke kolom?

Weet iemand hoe ik deze moet resetten?

 

[Hoornvan]

Mistic NV,

Het is een duidelijk voorbeeld wat je heb bijgevoegd.

 

[Roncancio]

Ik begrijp er ook niet veel van maar je kan checkboxen, die je met Formulieren hebt aangemaakt, uitvinken via:

    ActiveSheet.CheckBoxes.Value = False

Met vriendelijke groet,


Roncancio

 

[Mistic NV]

Hieronder het programma

Sub CommandButton1_Click()
'Application.ScreenUpdating = False
Sheets("Montagerapport").Activate
Cells(12, 1).Activate
Do While ActiveCell.Value > " "
'reset van de rapporten (NL)
Sheets("Nederlands").Activate
'ActiveSheet.OLEObjects("CheckBox1").Object.Value = False
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox4.Value = False
Sheet1.CheckBox5.Value = False
Sheet1.CheckBox6.Value = False
Sheet1.CheckBox7.Value = False
Sheet1.CheckBox8.Value = False
Sheet1.CheckBox9.Value = False
Sheet1.CheckBox10.Value = False
Sheet1.CheckBox11.Value = False
Sheet1.CheckBox12.Value = False
Sheet1.CheckBox13.Value = False
Sheet1.CheckBox14.Value = False
Sheet1.CheckBox15.Value = False
Sheet1.CheckBox16.Value = False
Sheet1.CheckBox17.Value = False
Sheet1.CheckBox18.Value = False
Sheet1.CheckBox19.Value = False
Sheet1.CheckBox20.Value = False
Sheet1.CheckBox21.Value = False
Sheet1.CheckBox22.Value = False
Sheet1.CheckBox23.Value = False
Sheet1.CheckBox24.Value = False
Sheet1.CheckBox25.Value = False
Sheet1.CheckBox26.Value = False
Sheet1.CheckBox31.Value = False
Sheet1.CheckBox32.Value = False
Sheet1.CheckBox33.Value = False
Sheet1.CheckBox34.Value = False
Sheet1.CheckBox35.Value = False
Sheet1.CheckBox36.Value = False
Sheet1.CheckBox37.Value = False
Sheet1.CheckBox38.Value = False
Sheet1.CheckBox39.Value = False
Sheet1.CheckBox40.Value = False
Sheet1.CheckBox53.Value = False
Sheet1.CheckBox54.Value = False
Sheet1.CheckBox55.Value = False
Sheet1.CheckBox56.Value = False
Sheet1.CheckBox57.Value = False
Sheet1.CheckBox58.Value = False
Sheet1.Range("C4:E4").ClearContents
Sheet1.Range("G4").ClearContents
Sheet1.Range("C5:J5").ClearContents
Sheet1.Range("C6:F6").ClearContents
Sheet1.Range("H6:K6").ClearContents
Sheet1.Range("B1213").ClearContents
Sheet1.Range("E12:G13").ClearContents
Sheet1.Range("B1415").ClearContents
Sheet1.Range("E14:G15").ClearContents
Sheet1.Range("E19:N19").ClearContents
Sheet1.Range("F24:N24").ClearContents
Sheet1.Range("C52:E52").ClearContents
Sheet1.Range("C53:E53").ClearContents
Sheet1.Range("J50:K50").ClearContents
Sheet1.Range("J51:K51").ClearContents
Sheet1.Range("L50:M50").ClearContents
Sheet1.Range("L51:M51").ClearContents
Sheet1.Range("C6161").ClearContents
Sheet1.Range("C6262").ClearContents
Sheet1.Range("E61:F61").ClearContents
Sheet1.Range("E62:F62").ClearContents
Sheet1.Range("J61:K61").ClearContents
Sheet1.Range("J62:K62").ClearContents
Sheet1.Range("L61:M61").ClearContents
Sheet1.Range("L62:M62").ClearContents
Sheet1.Range("H68:J68").ClearContents
Sheet1.Range("H69:J69").ClearContents
Sheets("Montagerapport").Activate
'baken 0 en 1 - Naam opsteller
Sheets("Nederlands").Range("C4:E4") = ActiveCell.Offset(0, 24).Value
'baken 0 en 1 - Graad opsteller
Sheets("Nederlands").Range("G4") = ActiveCell.Offset(0, 25).Value
'baken 0 en 1 - Datum Plaatsing
Sheets("Nederlands").Range("L4:N4") = ActiveCell.Offset(0, 3).Value
'baken 0 en 1 - Lijn
Sheets("Nederlands").Range("C5:J5") = ActiveCell.Offset(0, 26).Value
'baken 0 en 1 - Sein/KP
Sheets("Nederlands").Range("M5:N5") = ActiveCell.Offset(0, 21).Value
'baken 0 en 1 - Installatie
Sheets("Nederlands").Range("C6:F6") = ActiveCell.Offset(0, 27).Value
'baken 0 en 1 - Versie
Sheets("Nederlands").Range("H6:K6") = ActiveCell.Offset(0, 28).Value
'baken 0 en 1 - Datum controle
Sheets("Nederlands").Range("M6:N6") = ActiveCell.Offset(0, 29).Value
'baken 0 en 1 : Type bakengroep
If ActiveCell.Offset(0, 6).Value = "SEIN" Then Sheet1.CheckBox1.Value = True
If ActiveCell.Offset(0, 6).Value = "INFILL" Then Sheet1.CheckBox2.Value = True
If ActiveCell.Offset(0, 6).Value = "TECH" Then Sheet1.CheckBox3.Value = True
If ActiveCell.Offset(0, 6).Value = "IN" Then Sheet1.CheckBox4.Value = True
If ActiveCell.Offset(0, 6).Value = "OUT" Then Sheet1.CheckBox5.Value = True
If ActiveCell.Offset(0, 6).Value = "ON" Then Sheet1.CheckBox6.Value = True
If ActiveCell.Offset(0, 6).Value = "OFF" Then Sheet1.CheckBox7.Value = True
If ActiveCell.Offset(0, 6).Value = "KVW" Then Sheet1.CheckBox8.Value = True
'baken 0 - Switch / Fixe
If ActiveCell.Offset(0, 4).Value = "S" Then Sheet1.CheckBox9.Value = True
If ActiveCell.Offset(0, 4).Value = "F" Then Sheet1.CheckBox10.Value = True
If ActiveCell.Offset(0, 4).Value = "S" Then Sheet1.CheckBox13.Value = True
'baken 0 en 1 : BA-bord
If ActiveCell.Offset(0, 11).Value = "J" Then Sheet1.CheckBox53.Value = True
If ActiveCell.Offset(0, 11).Value = "N" Then Sheet1.CheckBox54.Value = True
'baken 0 - Identificatie
Sheets("Nederlands").Range("B1213") = ActiveCell.Offset(0, 23).Value
'baken 0 - Serienummer
Sheets("Nederlands").Range("E12:G13") = ActiveCell.Offset(0, 2).Value
'baken 0 : Met codering of Zonder codering
If ActiveCell.Offset(0, 5).Value = "M" Then Sheet1.CheckBox11.Value = True
If ActiveCell.Offset(0, 5).Value = "Z" Then Sheet1.CheckBox12.Value = True
'baken 0 : Vortok - type
If ActiveCell.Offset(0, 7).Value = "1" Then Sheet1.CheckBox17.Value = True
If ActiveCell.Offset(0, 7).Value = "1bis" Then Sheet1.CheckBox18.Value = True
If ActiveCell.Offset(0, 7).Value = "2" Then Sheet1.CheckBox19.Value = True
If ActiveCell.Offset(0, 7).Value = "3" Then Sheet1.CheckBox20.Value = True
If ActiveCell.Offset(0, 7).Value = "A" Then Sheet1.CheckBox23.Value = True
If ActiveCell.Offset(0, 7).Value = "A" Then Sheets("Nederlands").Range("F19:N19") = ActiveCell.Offset(0, 22).Value
'baken 0 : Vortok - met codering of zonder codering
If ActiveCell.Offset(0, 8).Value = "M" Then Sheet1.CheckBox21.Value = True
If ActiveCell.Offset(0, 8).Value = "Z" Then Sheet1.CheckBox22.Value = True
'baken 0 : Vortok - dwarsligger
If ActiveCell.Offset(0, 9).Value = "H" Then Sheet1.CheckBox24.Value = True
If ActiveCell.Offset(0, 9).Value = "B" Then Sheet1.CheckBox25.Value = True
If ActiveCell.Offset(0, 9).Value = "M" Then Sheet1.CheckBox26.Value = True
'baken 0 : Vortok - bedding : Vast of Ballast
If ActiveCell.Offset(0, 10).Value = "V" Then Sheet1.CheckBox37.Value = True
If ActiveCell.Offset(0, 10).Value = "B" Then Sheet1.CheckBox38.Value = True
'baken 0 : maten
Sheets("Nederlands").Range("C52:E52") = ActiveCell.Offset(0, 13).Value
Sheets("Nederlands").Range("J50:K50") = ActiveCell.Offset(0, 14).Value
Sheets("Nederlands").Range("L50:M50") = ActiveCell.Offset(0, 15).Value
Sheets("Nederlands").Range("C6161") = ActiveCell.Offset(0, 16).Value
Sheets("Nederlands").Range("E61:F61") = ActiveCell.Offset(0, 17).Value
Sheets("Nederlands").Range("J61:K61") = ActiveCell.Offset(0, 18).Value
Sheets("Nederlands").Range("L61:M61") = ActiveCell.Offset(0, 19).Value
Sheets("Nederlands").Range("H68:J68") = ActiveCell.Offset(0, 20).Value
'Eén cel naar beneden gaan.
ActiveCell.Offset(1, 0).Activate
'baken 1 - Identificatie
Sheets("Nederlands").Range("B1415") = ActiveCell.Offset(0, 23).Value
'baken 1 - Serienummer
Sheets("Nederlands").Range("E14:G15") = ActiveCell.Offset(0, 2).Value
'baken 1 : Met codering of Zonder codering
If ActiveCell.Offset(0, 5).Value = "M" Then Sheet1.CheckBox14.Value = True
If ActiveCell.Offset(0, 5).Value = "Z" Then Sheet1.CheckBox15.Value = True
'baken 1 - Switch / Fixe
If ActiveCell.Offset(0, 4).Value = "S" Then Sheet1.CheckBox16.Value = True
'baken 1 : Vortok - type
If ActiveCell.Offset(0, 7).Value = "1" Then Sheet1.CheckBox55.Value = True
If ActiveCell.Offset(0, 7).Value = "1bis" Then Sheet1.CheckBox56.Value = True
If ActiveCell.Offset(0, 7).Value = "2" Then Sheet1.CheckBox57.Value = True
If ActiveCell.Offset(0, 7).Value = "3" Then Sheet1.CheckBox58.Value = True
If ActiveCell.Offset(0, 7).Value = "A" Then Sheet1.CheckBox33.Value = True
If ActiveCell.Offset(0, 7).Value = "A" Then Sheets("Nederlands").Range("F24:N24") = ActiveCell.Offset(0, 22).Value
'baken 1 : Vortok - met codering of zonder codering
If ActiveCell.Offset(0, 8).Value = "M" Then Sheet1.CheckBox31.Value = True
If ActiveCell.Offset(0, 8).Value = "Z" Then Sheet1.CheckBox32.Value = True
'baken 1 : Vortok - dwarsligger
If ActiveCell.Offset(0, 9).Value = "H" Then Sheet1.CheckBox34.Value = True
If ActiveCell.Offset(0, 9).Value = "B" Then Sheet1.CheckBox35.Value = True
If ActiveCell.Offset(0, 9).Value = "M" Then Sheet1.CheckBox36.Value = True
'baken 1 : Vortok - bedding : Vast of Ballast
If ActiveCell.Offset(0, 10).Value = "V" Then Sheet1.CheckBox39.Value = True
If ActiveCell.Offset(0, 10).Value = "B" Then Sheet1.CheckBox40.Value = True
'baken 1 : maten
Sheets("Nederlands").Range("C53:E53") = ActiveCell.Offset(0, 13).Value
Sheets("Nederlands").Range("J51:K51") = ActiveCell.Offset(0, 14).Value
Sheets("Nederlands").Range("L51:M51") = ActiveCell.Offset(0, 15).Value
Sheets("Nederlands").Range("C6262") = ActiveCell.Offset(0, 16).Value
Sheets("Nederlands").Range("E62:F62") = ActiveCell.Offset(0, 17).Value
Sheets("Nederlands").Range("J62:K62") = ActiveCell.Offset(0, 18).Value
Sheets("Nederlands").Range("L62:M62") = ActiveCell.Offset(0, 19).Value
Sheets("Nederlands").Range("H69:J69") = ActiveCell.Offset(0, 20).Value
'montagerapport afdrukken in Nederlands
Sheets("Montagerapport").Activate
ActiveCell.Offset(0, 0).Activate
'afprinten van invulblad nederlands
Sheets("Nederlands").Activate
ActiveCell.Offset(0, 0).Activate
Dim filename As String
filename = ActiveCell.Offset(0, 0).Value & ".ps"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDFFILES", PrintToFile:=True, _
PrToFileName:=filename
'Wachttijden
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
'Eén cel naar beneden gaan.
Sheets("Montagerapport").Activate
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

 

[Roncancio]

De code kan veel korter.

In plaats van:

Sheets("Montagerapport").Activate
Cells(12, 1).Activate
Do While ActiveCell.Value > " "

volstaat

Do While Sheets("Montagerapport").Range("A12").Value > " "

in plaats van:
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox4.Value = False
Sheet1.CheckBox5.Value = False
Sheet1.CheckBox6.Value = False
Sheet1.CheckBox7.Value = False
Sheet1.CheckBox8.Value = False
Sheet1.CheckBox9.Value = False
Sheet1.CheckBox10.Value = False
Sheet1.CheckBox11.Value = False
Sheet1.CheckBox12.Value = False
Sheet1.CheckBox13.Value = False
Sheet1.CheckBox14.Value = False
Sheet1.CheckBox15.Value = False
Sheet1.CheckBox16.Value = False
Sheet1.CheckBox17.Value = False
Sheet1.CheckBox18.Value = False
Sheet1.CheckBox19.Value = False
Sheet1.CheckBox20.Value = False
Sheet1.CheckBox21.Value = False
Sheet1.CheckBox22.Value = False
Sheet1.CheckBox23.Value = False
Sheet1.CheckBox24.Value = False
Sheet1.CheckBox25.Value = False
Sheet1.CheckBox26.Value = False
Sheet1.CheckBox31.Value = False
Sheet1.CheckBox32.Value = False
Sheet1.CheckBox33.Value = False
Sheet1.CheckBox34.Value = False
Sheet1.CheckBox35.Value = False
Sheet1.CheckBox36.Value = False
Sheet1.CheckBox37.Value = False
Sheet1.CheckBox38.Value = False
Sheet1.CheckBox39.Value = False
Sheet1.CheckBox40.Value = False
Sheet1.CheckBox53.Value = False
Sheet1.CheckBox54.Value = False
Sheet1.CheckBox55.Value = False
Sheet1.CheckBox56.Value = False
Sheet1.CheckBox57.Value = False
Sheet1.CheckBox58.Value = False

Volstaat

Sheet1.Checkboxes.Value = False

In plaats van:

Sheet1.Range("J61:K61").ClearContents
Sheet1.Range("J62:K62").ClearContents
Sheet1.Range("L61:M61").ClearContents
Sheet1.Range("L62:M62").ClearContents

volstaat:

Sheet1.Range("J61:M62).ClearContents

Verder staan de volgende celverwijzingen in de code:
Sheet1.Range("B1213").ClearContents
Sheet1.Range("B1415").ClearContents
Sheet1.Range("C6262").ClearContents

Dat lijkt mij niet in orde.

Er is wel meer, maar dat was het voorlopig.

Met vriendelijke groet,


Roncancio