Code kürzen
13.06.2016 18:14:25
Frank
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