AW: UserForm in makro einbinden
Michael
Den Teil im Modul unter dem Userform1.show
kopier in die zwei button ereignissen also das dan wei folgt aussieht:
Modul:
Sub Shapes_Längen_markieren()
UserForm1.Show (--> Benutzer trifft Auswahl mit Button, Makros siehe unten)
End Sub
Formulare:
Private Sub CommandButton1_Click()
UserForm1.Hide
MinMax = 21
Worksheets(1).Activate
intLastRow = Cells(Rows.Count, 2).End(xlUp).Row
For intcounter = 3 To intLastRow
n = Worksheets(1).Cells(intcounter, 2).Value
Länge = Worksheets(1).Cells(intcounter, MinMax).Value
Worksheets(2).Select
ActiveSheet.Shapes(n).Select
Select Case Länge
Case Is >= 0
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 57 'Meeresgrün
Case Is >= 50
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3 'Grelles Grün
Case Is >= 100
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 54 'Blaugrau
Case Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5 'Gelb
End Select
Next intcounter
SendKeys ("{ESC}")
usw.
End Sub
Private Sub CommandButton2_Click()
UserForm.Hide
MinMax = 22
Worksheets(1).Activate
intLastRow = Cells(Rows.Count, 2).End(xlUp).Row
For intcounter = 3 To intLastRow
n = Worksheets(1).Cells(intcounter, 2).Value
Länge = Worksheets(1).Cells(intcounter, MinMax).Value
Worksheets(2).Select
ActiveSheet.Shapes(n).Select
Select Case Länge
Case Is >= 0
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 57 'Meeresgrün
Case Is >= 50
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3 'Grelles Grün
Case Is >= 100
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 54 'Blaugrau
Case Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 5 'Gelb
End Select
Next intcounter
SendKeys ("{ESC}")
usw.
End Sub
Dann müssts eigentlich funktionieren
mfg Michael