Makro kürzen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
UserForm TextBox
Bild

Betrifft: Makro kürzen
von: Daniel
Geschrieben am: 08.10.2003 17:52:54

Hallo Freunde, noch 'ne Frage:
wie kann ich diese Makro kürzen?


Private Sub Worksheet_Calculate()
UserForm6.TextBox1 = Range("d53")
 UserForm6.TextBox2 = Range("E53")
 UserForm6.TextBox3 = Range("F53")
 UserForm6.TextBox4 = Range("G53")
 UserForm6.TextBox5 = Range("h53")
 UserForm6.TextBox6 = Range("i53")
 UserForm6.TextBox7 = Range("j53")
 UserForm6.TextBox8 = Range("k53")
 UserForm6.TextBox9 = Range("l53")
 UserForm6.TextBox10 = Range("m53")
 UserForm6.TextBox11 = Range("n53")
 UserForm6.TextBox12 = Range("o53")
 UserForm6.TextBox13 = Range("p53")
 UserForm6.TextBox14 = Range("q53")
 UserForm6.TextBox15 = Range("r53")
 UserForm6.TextBox16 = Range("s53")
UserForm6.TextBox17 = Range("t53")
 UserForm6.TextBox18 = Range("u53")
 UserForm6.TextBox19 = Range("v53")
 UserForm6.TextBox20 = Range("w53")
 UserForm6.TextBox21 = Range("x53")
 UserForm6.TextBox22 = Range("y53")
 UserForm6.TextBox23 = Range("z53")
 UserForm6.TextBox24 = Range("aa53")
 UserForm6.TextBox25 = Range("ab53")
 UserForm6.TextBox26 = Range("ac53")
 UserForm6.TextBox27 = Range("ad53")
 UserForm6.TextBox28 = Range("ae53")
 UserForm6.TextBox29 = Range("af53")
 UserForm6.TextBox30 = Range("ag53")
 UserForm6.TextBox31 = Range("ah53")
 
 UserForm6.TextBox32 = Range("d54")
 UserForm6.TextBox33 = Range("E54")
 UserForm6.TextBox34 = Range("F54")
 UserForm6.TextBox35 = Range("G54")
 UserForm6.TextBox36 = Range("h54")
 UserForm6.TextBox37 = Range("i54")
 UserForm6.TextBox38 = Range("j54")
 UserForm6.TextBox39 = Range("k54")
 UserForm6.TextBox40 = Range("l54")
 UserForm6.TextBox41 = Range("m54")
 UserForm6.TextBox42 = Range("n54")
 UserForm6.TextBox43 = Range("o54")
 UserForm6.TextBox44 = Range("p54")
 UserForm6.TextBox45 = Range("q54")
 UserForm6.TextBox46 = Range("r54")
 UserForm6.TextBox47 = Range("s54")
UserForm6.TextBox48 = Range("t54")
 UserForm6.TextBox49 = Range("u54")
 UserForm6.TextBox50 = Range("v54")
 UserForm6.TextBox51 = Range("w54")
 UserForm6.TextBox52 = Range("x54")
 UserForm6.TextBox53 = Range("y54")
 UserForm6.TextBox54 = Range("z54")
 UserForm6.TextBox55 = Range("aa54")
 UserForm6.TextBox56 = Range("ab54")
 UserForm6.TextBox57 = Range("ac54")
 UserForm6.TextBox58 = Range("ad54")
 UserForm6.TextBox59 = Range("ae54")
 UserForm6.TextBox60 = Range("af54")
 UserForm6.TextBox61 = Range("ag54")
 UserForm6.TextBox62 = Range("ah54")
  End Sub


Danke
Daniel
Bild


Betrifft: AW: Makro kürzen
von: Andi_H
Geschrieben am: 08.10.2003 18:02:51

Hi Daniel,

konnte es leider nicht testen, müßte aber laufen


Private Sub Worksheet_Calculate()
UserForm6.Controls("Textbox" & i) = Cells(53, i + 3)
Next
For i = 32 To 62
UserForm6.Controls("Textbox" & i) = Cells(54, i - 28)
Next
End Sub


Gruß

Andi


Bild


Betrifft: AW: Makro kürzen
von: Nepumuk
Geschrieben am: 08.10.2003 18:05:04

Hallo Daniel,
so geht's:

Option Explicit
Private Sub Worksheet_Calculate()
    Dim index1 As Integer
    For index1 = 1 To 31
        UserForm6.Controls("TextBox" & CStr(index1)) = Cells(53, index1 + 3)
        UserForm6.Controls("TextBox" & CStr(index1 + 31)) = Cells(54, index1 + 3)
    Next
End Sub


Code eingefügt mit: Excel Code Jeanie

Gruß
Nepumuk


Bild


Betrifft: Danke, es funkt!!! Danke Nepumuk&Andi
von: Daniel
Geschrieben am: 08.10.2003 18:21:23

.


 Bild

Beiträge aus den Excel-Beispielen zum Thema " Excel als Datenquelle für Serienbrief Word"