Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
25.04.2024 21:06:05
Duplikate entfernen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1496to1500
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Code kürzen

Code kürzen
13.06.2016 18:14:25
Frank
Hallo zusammen
Ich habe eine Userform gebastelt, mit der ich gewisse Daten von einem Tabellenblatt abfrage. Die Daten sind alle untereinandergeschrieben. Jedoch soll nicht jeder Nutzer auf alle Daten Zugriff haben. Dazu habe ich vorab eine Abfrage drin die jedem Nutzer lediglich den vorgegebenen Bereich zuordnet.
Der Code ist ganz einfach zu verstehen aber recht lang. Ich denke, dass er stark zu kürzen ist.Nur weiß ich da im Moment nicht weiter.
Gruß Frank
Hier mein Code:
Private Sub UserForm_Activate()
Me.Caption = "Zugangsberechtigungen an " & Range("name3").Value
With Me
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = .InsideHeight * 2.6
.ScrollWidth = .InsideWidth * 9
End With
If Range("name3").Value = "FRW-01" Then
TextBox1.Text = Sheets("Daten").Cells(1, 5).Text
TextBox2.Text = Sheets("Daten").Cells(1, 4).Text
TextBox3.Text = Sheets("Daten").Cells(1, 7).Text
TextBox4.Text = Sheets("Daten").Cells(2, 5).Text
TextBox5.Text = Sheets("Daten").Cells(2, 4).Text
TextBox6.Text = Sheets("Daten").Cells(2, 7).Text
TextBox7.Text = Sheets("Daten").Cells(3, 5).Text
TextBox8.Text = Sheets("Daten").Cells(3, 4).Text
TextBox9.Text = Sheets("Daten").Cells(3, 7).Text
TextBox10.Text = Sheets("Daten").Cells(4, 5).Text
TextBox11.Text = Sheets("Daten").Cells(4, 4).Text
TextBox12.Text = Sheets("Daten").Cells(4, 7).Text
TextBox13.Text = Sheets("Daten").Cells(5, 5).Text
TextBox14.Text = Sheets("Daten").Cells(5, 4).Text
TextBox14.Text = Sheets("Daten").Cells(5, 7).Text
TextBox16.Text = Sheets("Daten").Cells(6, 5).Text
TextBox17.Text = Sheets("Daten").Cells(6, 4).Text
TextBox18.Text = Sheets("Daten").Cells(6, 7).Text
TextBox19.Text = Sheets("Daten").Cells(7, 5).Text
TextBox20.Text = Sheets("Daten").Cells(7, 4).Text
TextBox21.Text = Sheets("Daten").Cells(7, 7).Text
TextBox22.Text = Sheets("Daten").Cells(8, 5).Text
TextBox23.Text = Sheets("Daten").Cells(8, 4).Text
TextBox24.Text = Sheets("Daten").Cells(8, 7).Text
TextBox25.Text = Sheets("Daten").Cells(9, 5).Text
TextBox26.Text = Sheets("Daten").Cells(9, 4).Text
TextBox27.Text = Sheets("Daten").Cells(9, 7).Text
TextBox28.Text = Sheets("Daten").Cells(10, 5).Text
TextBox29.Text = Sheets("Daten").Cells(10, 4).Text
TextBox30.Text = Sheets("Daten").Cells(10, 7).Text
TextBox31.Text = Sheets("Daten").Cells(11, 5).Text
TextBox32.Text = Sheets("Daten").Cells(11, 4).Text
TextBox33.Text = Sheets("Daten").Cells(11, 7).Text
TextBox34.Text = Sheets("Daten").Cells(12, 5).Text
TextBox35.Text = Sheets("Daten").Cells(12, 4).Text
TextBox36.Text = Sheets("Daten").Cells(12, 7).Text
TextBox37.Text = Sheets("Daten").Cells(13, 5).Text
TextBox38.Text = Sheets("Daten").Cells(13, 4).Text
TextBox39.Text = Sheets("Daten").Cells(13, 7).Text
TextBox40.Text = Sheets("Daten").Cells(14, 5).Text
TextBox41.Text = Sheets("Daten").Cells(14, 4).Text
TextBox42.Text = Sheets("Daten").Cells(14, 7).Text
TextBox43.Text = Sheets("Daten").Cells(15, 5).Text
TextBox44.Text = Sheets("Daten").Cells(15, 4).Text
TextBox45.Text = Sheets("Daten").Cells(15, 7).Text
TextBox46.Text = Sheets("Daten").Cells(16, 5).Text
TextBox47.Text = Sheets("Daten").Cells(16, 4).Text
TextBox48.Text = Sheets("Daten").Cells(16, 7).Text
TextBox49.Text = Sheets("Daten").Cells(17, 5).Text
TextBox50.Text = Sheets("Daten").Cells(17, 4).Text
TextBox51.Text = Sheets("Daten").Cells(17, 7).Text
TextBox52.Text = Sheets("Daten").Cells(18, 5).Text
TextBox53.Text = Sheets("Daten").Cells(18, 4).Text
TextBox54.Text = Sheets("Daten").Cells(18, 7).Text
TextBox55.Text = Sheets("Daten").Cells(19, 5).Text
TextBox56.Text = Sheets("Daten").Cells(19, 4).Text
TextBox57.Text = Sheets("Daten").Cells(19, 7).Text
TextBox58.Text = Sheets("Daten").Cells(20, 5).Text
TextBox59.Text = Sheets("Daten").Cells(20, 4).Text
TextBox60.Text = Sheets("Daten").Cells(20, 7).Text
TextBox61.Text = Sheets("Daten").Cells(21, 5).Text
TextBox62.Text = Sheets("Daten").Cells(21, 4).Text
TextBox63.Text = Sheets("Daten").Cells(21, 7).Text
TextBox64.Text = Sheets("Daten").Cells(22, 5).Text
TextBox65.Text = Sheets("Daten").Cells(22, 4).Text
TextBox66.Text = Sheets("Daten").Cells(22, 7).Text
TextBox67.Text = Sheets("Daten").Cells(23, 5).Text
TextBox68.Text = Sheets("Daten").Cells(23, 4).Text
TextBox69.Text = Sheets("Daten").Cells(23, 7).Text
TextBox70.Text = Sheets("Daten").Cells(24, 5).Text
TextBox71.Text = Sheets("Daten").Cells(24, 4).Text
TextBox72.Text = Sheets("Daten").Cells(24, 7).Text
TextBox73.Text = Sheets("Daten").Cells(25, 5).Text
TextBox74.Text = Sheets("Daten").Cells(25, 4).Text
TextBox75.Text = Sheets("Daten").Cells(25, 7).Text
TextBox76.Text = Sheets("Daten").Cells(26, 5).Text
TextBox77.Text = Sheets("Daten").Cells(26, 4).Text
TextBox78.Text = Sheets("Daten").Cells(26, 7).Text
TextBox79.Text = Sheets("Daten").Cells(27, 5).Text
TextBox80.Text = Sheets("Daten").Cells(27, 4).Text
TextBox81.Text = Sheets("Daten").Cells(27, 7).Text
TextBox82.Text = Sheets("Daten").Cells(28, 5).Text
TextBox83.Text = Sheets("Daten").Cells(28, 4).Text
TextBox84.Text = Sheets("Daten").Cells(28, 7).Text
TextBox85.Text = Sheets("Daten").Cells(29, 5).Text
TextBox86.Text = Sheets("Daten").Cells(29, 4).Text
TextBox87.Text = Sheets("Daten").Cells(29, 7).Text
TextBox88.Text = Sheets("Daten").Cells(30, 5).Text
TextBox89.Text = Sheets("Daten").Cells(30, 4).Text
TextBox90.Text = Sheets("Daten").Cells(30, 7).Text
TextBox91.Text = Sheets("Daten").Cells(31, 5).Text
TextBox92.Text = Sheets("Daten").Cells(31, 4).Text
TextBox93.Text = Sheets("Daten").Cells(31, 7).Text
TextBox94.Text = Sheets("Daten").Cells(32, 5).Text
TextBox95.Text = Sheets("Daten").Cells(32, 4).Text
TextBox96.Text = Sheets("Daten").Cells(32, 7).Text
TextBox97.Text = Sheets("Daten").Cells(33, 5).Text
TextBox98.Text = Sheets("Daten").Cells(33, 4).Text
TextBox99.Text = Sheets("Daten").Cells(33, 7).Text
TextBox100.Text = Sheets("Daten").Cells(34, 5).Text
TextBox101.Text = Sheets("Daten").Cells(34, 4).Text
TextBox102.Text = Sheets("Daten").Cells(34, 7).Text
TextBox103.Text = Sheets("Daten").Cells(35, 5).Text
TextBox104.Text = Sheets("Daten").Cells(35, 4).Text
TextBox105.Text = Sheets("Daten").Cells(35, 7).Text
TextBox106.Text = Sheets("Daten").Cells(36, 5).Text
TextBox107.Text = Sheets("Daten").Cells(36, 4).Text
TextBox108.Text = Sheets("Daten").Cells(36, 7).Text
ElseIf Range("name3").Value = "FRW-02" Then
TextBox1.Text = Sheets("Daten").Cells(37, 5).Text
TextBox2.Text = Sheets("Daten").Cells(37, 4).Text
TextBox3.Text = Sheets("Daten").Cells(37, 7).Text
TextBox4.Text = Sheets("Daten").Cells(38, 5).Text
TextBox5.Text = Sheets("Daten").Cells(38, 4).Text
TextBox6.Text = Sheets("Daten").Cells(38, 7).Text
TextBox7.Text = Sheets("Daten").Cells(39, 5).Text
TextBox8.Text = Sheets("Daten").Cells(39, 4).Text
TextBox9.Text = Sheets("Daten").Cells(39, 7).Text
TextBox10.Text = Sheets("Daten").Cells(40, 5).Text
TextBox11.Text = Sheets("Daten").Cells(40, 4).Text
TextBox12.Text = Sheets("Daten").Cells(40, 7).Text
TextBox13.Text = Sheets("Daten").Cells(41, 5).Text
TextBox14.Text = Sheets("Daten").Cells(41, 4).Text
TextBox14.Text = Sheets("Daten").Cells(41, 7).Text
TextBox16.Text = Sheets("Daten").Cells(42, 5).Text
TextBox17.Text = Sheets("Daten").Cells(42, 4).Text
TextBox18.Text = Sheets("Daten").Cells(42, 7).Text
TextBox19.Text = Sheets("Daten").Cells(43, 5).Text
TextBox20.Text = Sheets("Daten").Cells(43, 4).Text
TextBox21.Text = Sheets("Daten").Cells(43, 7).Text
TextBox22.Text = Sheets("Daten").Cells(44, 5).Text
TextBox23.Text = Sheets("Daten").Cells(44, 4).Text
TextBox24.Text = Sheets("Daten").Cells(44, 7).Text
TextBox25.Text = Sheets("Daten").Cells(45, 5).Text
TextBox26.Text = Sheets("Daten").Cells(45, 4).Text
TextBox27.Text = Sheets("Daten").Cells(45, 7).Text
TextBox28.Text = Sheets("Daten").Cells(46, 5).Text
TextBox29.Text = Sheets("Daten").Cells(46, 4).Text
TextBox30.Text = Sheets("Daten").Cells(46, 7).Text
TextBox31.Text = Sheets("Daten").Cells(47, 5).Text
TextBox32.Text = Sheets("Daten").Cells(47, 4).Text
TextBox33.Text = Sheets("Daten").Cells(47, 7).Text
TextBox34.Text = Sheets("Daten").Cells(48, 5).Text
TextBox35.Text = Sheets("Daten").Cells(48, 4).Text
TextBox36.Text = Sheets("Daten").Cells(48, 7).Text
TextBox37.Text = Sheets("Daten").Cells(49, 5).Text
TextBox38.Text = Sheets("Daten").Cells(49, 4).Text
TextBox39.Text = Sheets("Daten").Cells(49, 7).Text
TextBox40.Text = Sheets("Daten").Cells(50, 5).Text
TextBox41.Text = Sheets("Daten").Cells(50, 4).Text
TextBox42.Text = Sheets("Daten").Cells(50, 7).Text
TextBox43.Text = Sheets("Daten").Cells(51, 5).Text
TextBox44.Text = Sheets("Daten").Cells(51, 4).Text
TextBox45.Text = Sheets("Daten").Cells(51, 7).Text
TextBox46.Text = Sheets("Daten").Cells(52, 5).Text
TextBox47.Text = Sheets("Daten").Cells(52, 4).Text
TextBox48.Text = Sheets("Daten").Cells(52, 7).Text
TextBox49.Text = Sheets("Daten").Cells(53, 5).Text
TextBox50.Text = Sheets("Daten").Cells(53, 4).Text
TextBox51.Text = Sheets("Daten").Cells(53, 7).Text
TextBox52.Text = Sheets("Daten").Cells(54, 5).Text
TextBox53.Text = Sheets("Daten").Cells(54, 4).Text
TextBox54.Text = Sheets("Daten").Cells(54, 7).Text
TextBox55.Text = Sheets("Daten").Cells(55, 5).Text
TextBox56.Text = Sheets("Daten").Cells(55, 4).Text
TextBox57.Text = Sheets("Daten").Cells(55, 7).Text
TextBox58.Text = Sheets("Daten").Cells(56, 5).Text
TextBox59.Text = Sheets("Daten").Cells(56, 4).Text
TextBox60.Text = Sheets("Daten").Cells(56, 7).Text
TextBox61.Text = Sheets("Daten").Cells(57, 5).Text
TextBox62.Text = Sheets("Daten").Cells(57, 4).Text
TextBox63.Text = Sheets("Daten").Cells(57, 7).Text
TextBox64.Text = Sheets("Daten").Cells(58, 5).Text
TextBox65.Text = Sheets("Daten").Cells(58, 4).Text
TextBox66.Text = Sheets("Daten").Cells(58, 7).Text
TextBox67.Text = Sheets("Daten").Cells(59, 5).Text
TextBox68.Text = Sheets("Daten").Cells(59, 4).Text
TextBox69.Text = Sheets("Daten").Cells(59, 7).Text
TextBox70.Text = Sheets("Daten").Cells(60, 5).Text
TextBox71.Text = Sheets("Daten").Cells(60, 4).Text
TextBox72.Text = Sheets("Daten").Cells(60, 7).Text
TextBox73.Text = Sheets("Daten").Cells(61, 5).Text
TextBox74.Text = Sheets("Daten").Cells(61, 4).Text
TextBox75.Text = Sheets("Daten").Cells(61, 7).Text
TextBox76.Text = Sheets("Daten").Cells(62, 5).Text
TextBox77.Text = Sheets("Daten").Cells(62, 4).Text
TextBox78.Text = Sheets("Daten").Cells(62, 7).Text
TextBox79.Text = Sheets("Daten").Cells(63, 5).Text
TextBox80.Text = Sheets("Daten").Cells(63, 4).Text
TextBox81.Text = Sheets("Daten").Cells(63, 7).Text
TextBox82.Text = Sheets("Daten").Cells(64, 5).Text
TextBox83.Text = Sheets("Daten").Cells(64, 4).Text
TextBox84.Text = Sheets("Daten").Cells(64, 7).Text
TextBox85.Text = Sheets("Daten").Cells(65, 5).Text
TextBox86.Text = Sheets("Daten").Cells(65, 4).Text
TextBox87.Text = Sheets("Daten").Cells(65, 7).Text
TextBox88.Text = Sheets("Daten").Cells(66, 5).Text
TextBox89.Text = Sheets("Daten").Cells(66, 4).Text
TextBox90.Text = Sheets("Daten").Cells(66, 7).Text
TextBox91.Text = Sheets("Daten").Cells(67, 5).Text
TextBox92.Text = Sheets("Daten").Cells(67, 4).Text
TextBox93.Text = Sheets("Daten").Cells(67, 7).Text
TextBox94.Text = Sheets("Daten").Cells(68, 5).Text
TextBox95.Text = Sheets("Daten").Cells(68, 4).Text
TextBox96.Text = Sheets("Daten").Cells(68, 7).Text
TextBox97.Text = Sheets("Daten").Cells(69, 5).Text
TextBox98.Text = Sheets("Daten").Cells(69, 4).Text
TextBox99.Text = Sheets("Daten").Cells(69, 7).Text
TextBox100.Text = Sheets("Daten").Cells(70, 5).Text
TextBox101.Text = Sheets("Daten").Cells(70, 4).Text
TextBox102.Text = Sheets("Daten").Cells(70, 7).Text
TextBox103.Text = Sheets("Daten").Cells(71, 5).Text
TextBox104.Text = Sheets("Daten").Cells(71, 4).Text
TextBox105.Text = Sheets("Daten").Cells(71, 7).Text
TextBox106.Text = Sheets("Daten").Cells(72, 5).Text
TextBox107.Text = Sheets("Daten").Cells(72, 4).Text
TextBox108.Text = Sheets("Daten").Cells(72, 7).Text
ElseIf Range("name3").Value = "FRW-03" Then
TextBox1.Text = Sheets("Daten").Cells(73, 5).Text
TextBox2.Text = Sheets("Daten").Cells(73, 4).Text
TextBox3.Text = Sheets("Daten").Cells(73, 7).Text
TextBox4.Text = Sheets("Daten").Cells(74, 5).Text
TextBox5.Text = Sheets("Daten").Cells(74, 4).Text
TextBox6.Text = Sheets("Daten").Cells(74, 7).Text
TextBox7.Text = Sheets("Daten").Cells(75, 5).Text
TextBox8.Text = Sheets("Daten").Cells(75, 4).Text
TextBox9.Text = Sheets("Daten").Cells(75, 7).Text
TextBox10.Text = Sheets("Daten").Cells(76, 5).Text
TextBox11.Text = Sheets("Daten").Cells(76, 4).Text
TextBox12.Text = Sheets("Daten").Cells(76, 7).Text
TextBox13.Text = Sheets("Daten").Cells(77, 5).Text
TextBox14.Text = Sheets("Daten").Cells(77, 4).Text
TextBox14.Text = Sheets("Daten").Cells(77, 7).Text
TextBox16.Text = Sheets("Daten").Cells(78, 5).Text
TextBox17.Text = Sheets("Daten").Cells(78, 4).Text
TextBox18.Text = Sheets("Daten").Cells(78, 7).Text
TextBox19.Text = Sheets("Daten").Cells(79, 5).Text
TextBox20.Text = Sheets("Daten").Cells(79, 4).Text
TextBox21.Text = Sheets("Daten").Cells(79, 7).Text
TextBox22.Text = Sheets("Daten").Cells(80, 5).Text
TextBox23.Text = Sheets("Daten").Cells(80, 4).Text
TextBox24.Text = Sheets("Daten").Cells(80, 7).Text
TextBox25.Text = Sheets("Daten").Cells(81, 5).Text
TextBox26.Text = Sheets("Daten").Cells(81, 4).Text
TextBox27.Text = Sheets("Daten").Cells(81, 7).Text
TextBox28.Text = Sheets("Daten").Cells(82, 5).Text
TextBox29.Text = Sheets("Daten").Cells(82, 4).Text
TextBox30.Text = Sheets("Daten").Cells(82, 7).Text
TextBox31.Text = Sheets("Daten").Cells(83, 5).Text
TextBox32.Text = Sheets("Daten").Cells(83, 4).Text
TextBox33.Text = Sheets("Daten").Cells(83, 7).Text
TextBox34.Text = Sheets("Daten").Cells(84, 5).Text
TextBox35.Text = Sheets("Daten").Cells(84, 4).Text
TextBox36.Text = Sheets("Daten").Cells(84, 7).Text
TextBox37.Text = Sheets("Daten").Cells(85, 5).Text
TextBox38.Text = Sheets("Daten").Cells(85, 4).Text
TextBox39.Text = Sheets("Daten").Cells(85, 7).Text
TextBox40.Text = Sheets("Daten").Cells(86, 5).Text
TextBox41.Text = Sheets("Daten").Cells(86, 4).Text
TextBox42.Text = Sheets("Daten").Cells(86, 7).Text
TextBox43.Text = Sheets("Daten").Cells(87, 5).Text
TextBox44.Text = Sheets("Daten").Cells(87, 4).Text
TextBox45.Text = Sheets("Daten").Cells(87, 7).Text
TextBox46.Text = Sheets("Daten").Cells(88, 5).Text
TextBox47.Text = Sheets("Daten").Cells(88, 4).Text
TextBox48.Text = Sheets("Daten").Cells(88, 7).Text
TextBox49.Text = Sheets("Daten").Cells(89, 5).Text
TextBox50.Text = Sheets("Daten").Cells(89, 4).Text
TextBox51.Text = Sheets("Daten").Cells(89, 7).Text
TextBox52.Text = Sheets("Daten").Cells(90, 5).Text
TextBox53.Text = Sheets("Daten").Cells(90, 4).Text
TextBox54.Text = Sheets("Daten").Cells(90, 7).Text
TextBox55.Text = Sheets("Daten").Cells(91, 5).Text
TextBox56.Text = Sheets("Daten").Cells(91, 4).Text
TextBox57.Text = Sheets("Daten").Cells(91, 7).Text
TextBox58.Text = Sheets("Daten").Cells(92, 5).Text
TextBox59.Text = Sheets("Daten").Cells(92, 4).Text
TextBox60.Text = Sheets("Daten").Cells(92, 7).Text
TextBox61.Text = Sheets("Daten").Cells(93, 5).Text
TextBox62.Text = Sheets("Daten").Cells(93, 4).Text
TextBox63.Text = Sheets("Daten").Cells(93, 7).Text
TextBox64.Text = Sheets("Daten").Cells(94, 5).Text
TextBox65.Text = Sheets("Daten").Cells(94, 4).Text
TextBox66.Text = Sheets("Daten").Cells(94, 7).Text
TextBox67.Text = Sheets("Daten").Cells(95, 5).Text
TextBox68.Text = Sheets("Daten").Cells(95, 4).Text
TextBox69.Text = Sheets("Daten").Cells(95, 7).Text
TextBox70.Text = Sheets("Daten").Cells(96, 5).Text
TextBox71.Text = Sheets("Daten").Cells(96, 4).Text
TextBox72.Text = Sheets("Daten").Cells(96, 7).Text
TextBox73.Text = Sheets("Daten").Cells(97, 5).Text
TextBox74.Text = Sheets("Daten").Cells(97, 4).Text
TextBox75.Text = Sheets("Daten").Cells(97, 7).Text
TextBox76.Text = Sheets("Daten").Cells(98, 5).Text
TextBox77.Text = Sheets("Daten").Cells(98, 4).Text
TextBox78.Text = Sheets("Daten").Cells(98, 7).Text
TextBox79.Text = Sheets("Daten").Cells(99, 5).Text
TextBox80.Text = Sheets("Daten").Cells(99, 4).Text
TextBox81.Text = Sheets("Daten").Cells(99, 7).Text
TextBox82.Text = Sheets("Daten").Cells(100, 5).Text
TextBox83.Text = Sheets("Daten").Cells(100, 4).Text
TextBox84.Text = Sheets("Daten").Cells(100, 7).Text
TextBox85.Text = Sheets("Daten").Cells(101, 5).Text
TextBox86.Text = Sheets("Daten").Cells(101, 4).Text
TextBox87.Text = Sheets("Daten").Cells(101, 7).Text
TextBox88.Text = Sheets("Daten").Cells(102, 5).Text
TextBox89.Text = Sheets("Daten").Cells(102, 4).Text
TextBox90.Text = Sheets("Daten").Cells(102, 7).Text
TextBox91.Text = Sheets("Daten").Cells(103, 5).Text
TextBox92.Text = Sheets("Daten").Cells(103, 4).Text
TextBox93.Text = Sheets("Daten").Cells(103, 7).Text
TextBox94.Text = Sheets("Daten").Cells(104, 5).Text
TextBox95.Text = Sheets("Daten").Cells(104, 4).Text
TextBox96.Text = Sheets("Daten").Cells(104, 7).Text
TextBox97.Text = Sheets("Daten").Cells(105, 5).Text
TextBox98.Text = Sheets("Daten").Cells(105, 4).Text
TextBox99.Text = Sheets("Daten").Cells(105, 7).Text
TextBox100.Text = Sheets("Daten").Cells(106, 5).Text
TextBox101.Text = Sheets("Daten").Cells(106, 4).Text
TextBox102.Text = Sheets("Daten").Cells(106, 7).Text
TextBox103.Text = Sheets("Daten").Cells(107, 5).Text
TextBox104.Text = Sheets("Daten").Cells(107, 4).Text
TextBox105.Text = Sheets("Daten").Cells(107, 7).Text
TextBox106.Text = Sheets("Daten").Cells(108, 5).Text
TextBox107.Text = Sheets("Daten").Cells(108, 4).Text
TextBox108.Text = Sheets("Daten").Cells(108, 7).Text
End If
End Sub

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Ungetestet insb. Typen und Klammern
13.06.2016 18:23:59
lupo1 

Private Sub UserForm_Activate()
Me.Caption = "Zugangsberechtigungen an " & Range("name3").Value
With Me
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = .InsideHeight * 2.6
.ScrollWidth = .InsideWidth * 9
End With
TextBox1.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 35, 1), 5).Text
TextBox2.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 35, 1), 4).Text
TextBox105.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 1, 7), 4).Text
TextBox106.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 5), 4).Text
TextBox107.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 4), 4).Text
TextBox108.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 7), 4).Text
End Sub

Anzeige
Fehlerteufel hinten mit Right(..., 1)
13.06.2016 18:25:31
lupo1 

Private Sub UserForm_Activate()
Me.Caption = "Zugangsberechtigungen an " & Range("name3").Value
With Me
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = .InsideHeight * 2.6
.ScrollWidth = .InsideWidth * 9
End With
TextBox1.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 35, 1), 5).Text
TextBox2.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 35, 1), 4).Text
TextBox105.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 1, 1), 7).Text
TextBox106.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 1), 5).Text
TextBox107.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 1), 4).Text
TextBox108.Text = Sheets("Daten").Cells(Right(Range("name3").Value * 36 - 0, 1), 7).Text
End Sub

Anzeige
AW: Fehlerteufel hinten mit Right(..., 1)
13.06.2016 18:40:16
Frank
Hallo Lupo
Danke für die schnelle Antwort
Ich denke mit right(range("name3")wird der Zugang abgefragt. Da gibt er mir auch den richtigen Wert wieder. Der Code hängt sich aber schon an der ersten Zeile (ab Textbox1) auf.
Gruß Frank

AW: Code kürzen
13.06.2016 18:36:58
Hajo_Zi
ich hätte die Tag Eigenschaft benutzt Zeiler und Spalte mit Semikolon getrennt.
If Range("name3").Value = "FRW-01" Then
For InI = 1 To 100
Controls("TextBox" & InI) = Sheets("Daten").Cells(Left(Controls("TextBox" & InI).Tag, InStr(Controls("TextBox" & InI).Tag, ";") - 1) * 1 _
, Mid(Controls("TextBox" & InI).Tag, InStr(Controls("TextBox" & InI).Tag, ";") + 1 * 1))
Next InI
End If

Anzeige
AW: Code kürzen
13.06.2016 18:54:05
Frank
Hallo Hajo
Danke für deinen Lösungsvorschlag.
Aber dafür reichen meine VBA Kenntnisse nicht aus.
Den Code so zu übernehmen geht nicht (Fehlermeldung), und ihn abzuändern fehlt mir der Durchblick.
Gruß Frank

AW: Code kürzen
13.06.2016 19:34:52
Hajo_Zi
Hallo Frank,
Fehlermeldung da mein Beitrag nicht komplett gelesen wurde. Vermute ich mal. Da ich Deine Datei nicht sehen kann. Ich lehne es ab auf fremde Rechner zu schauen.
Gruß Hajo

AW: Code kürzen
13.06.2016 18:50:39
Luschi
Hallo Frank,
mein Code sieht so aus: 

Dim i As Integer, k As Integer
If Range("name3").Value = "FRW-01" Then
k = 1
For i = 1 To 108 Step 3
Me("TextBox" & i).Text = Sheets("Daten").Cells(k, 5).Text
Me("TextBox" & (i + 1)).Text = Sheets("Daten").Cells(k, 4).Text
Me("TextBox" & (i + 2)).Text = Sheets("Daten").Cells(k, 7).Text
k = k + 1
Next i
ElseIf Range("name3").Value = "FRW-02" Then
k = 37
For i = 1 To 108 Step 3
Me("TextBox" & i).Text = Sheets("Daten").Cells(k, 5).Text
Me("TextBox" & (i + 1)).Text = Sheets("Daten").Cells(k, 4).Text
Me("TextBox" & (i + 2)).Text = Sheets("Daten").Cells(k, 7).Text
k = k + 1
Next i
ElseIf Range("name3").Value = "FRW-03" Then
k = 73
For i = 1 To 108 Step 3
Me("TextBox" & i).Text = Sheets("Daten").Cells(k, 5).Text
Me("TextBox" & (i + 1)).Text = Sheets("Daten").Cells(k, 4).Text
Me("TextBox" & (i + 2)).Text = Sheets("Daten").Cells(k, 7).Text
k = k + 1
Next i
End If
Gruß von Luschi
aus klein-Paris

Anzeige
AW: Code kürzen
13.06.2016 18:58:52
Frank
Hallo Luschi
Das war genau das was ich mir vorgestellt hatte. Klappt super.
Danke vielmals
und auch Danke an Lupo und Hajo für ihre Vorschläge
Gruß Frank

AW: Code kürzen-> noch kürzer
13.06.2016 19:08:13
Daniel
so noch einfacher: 
Dim i As Integer, k As Integer
Select Case Range("name3").Value
Case "FRW-01": k = 0
Case "FRW-02": k = 36
Case "FRW-03": k = 72
Case Else: k = -1
end Select
if k > -1 Then
For i = 1 To 108 Step 3
Me("TextBox" & (i + 0)).Text = Sheets("Daten").Cells(k + i, 5).Text
Me("TextBox" & (i + 1)).Text = Sheets("Daten").Cells(k + i, 4).Text
Me("TextBox" & (i + 2)).Text = Sheets("Daten").Cells(k + i, 7).Text
Next i
End if
Gruß Daniel

Anzeige
AW: Code kürzen-> noch kürzer
13.06.2016 19:12:30
Frank
Ihr seid spitze Jungs
Danke für die Hilfe
Gruß Frank

AW: Code kürzen
13.06.2016 20:01:58
Frank
Hallo Luschi
Ich hätte da doch noch eine Frage.
Wie müßte ich denn den Code verändern, wenn ich die Daten der Userform wieder in die Tabelle schreiben möchte. Einfach umdrehen, also:
For i = 1 To 108 Step 3
Sheets("Daten").Cells(k, 5).Text = Me("TextBox" & i).Text
Sheets("Daten").Cells(k, 4).Text = Me("TextBox" & (i + 1)).Text
Sheets("Daten").Cells(k, 7).Text = Me("TextBox" & (i + 2)).Text
k = k + 1
Next i
würde ja nicht funktionieren.

AW: Code kürzen
14.06.2016 09:26:47
Daniel
Hi
im Prinzip schon richtig.
allerdings ist bei Zellen die Eigenschaft .Text nur ReadOnly.
Wenn du den Zellwert verändern willst, musst du die .Value-Eigenschaft nehmen.
Sheets("Daten").Cells(k, 5).Value = Me("TextBox" & i).Text
aufpassen musst du hier jedoch, wenn du auf diese Weise Kommazahlen, Zeiten oder Datumswerte von der TextBox in die Zellen überträgst.
Da die Eintragung in die Zellen erfolgt und es sich um Texte handelt die Excel dann in Zahl oder Zeit/Datum umwandelt und die Eintragung von VBA durchgeführt wird, müssten die Textwerte im Englischen Format vorliegen um richtig erkannt zu werden!
dh bei Zahlen oder Datumswerten müsstest du per Umwandlungsformel umwandlen:
..Cells(...).Value = CDbl(TextboxX.Text)
..Cells(...).Value = CDate(TextboxX.Text)
Gruß Daniel

Anzeige
AW: Code kürzen
14.06.2016 09:42:51
Frank
Hallo Daniel
Danke für den Tip.
Ich arbeite dort nicht mit Zahlen- oder Datumswerten, daher bräuchte ich keine Umwandlung vornehmen. Ich werde das morgen mal ausprobieren, heute komme ich nicht an die Datei dran. Aber es scheint mir schon logisch zu sein.
Danke noch mal.
Gruß Frank

Links zu Excel-Dialogen

ScrollBar TextBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige