Combobox Inhalt übernehmen
26.07.2016 20:58:53
Faja
ich bearbeite eine Datei, in welcher Abwesenheiten erfasst werden, diese Datei stammt nicht von mir, habe sie aber geerbt.
Es gibt einen Button hinter dem folgender Code hinterlegt ist.
Sub Vertretung()
Selection.FormulaR1C1 = _
"=IF(R2C=1,"" "",IF(R1C=6,"" "",IF(R1C=7,"" "",IF(R1C=0,"" "",""V""))))"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub
Dies führt dazu, dass in den ausgewählten Zellen ein "V" erscheint, außer bei Samstagen und Sonntagen.
Ich haben nun eine Userbox eingefügt und auch befüllt.
Folgendes funktioniert:
Private Sub CommandButton3_Click()
Selection.FormulaR1C1 = _
UserForm1.ComboBox1.Value
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub
Private Sub CommandButton1_Click()
Selection.FormulaR1C1 = _
"=IF(R2C=1,"" "",IF(R1C=6,"" "",IF(R1C=7,"" "",IF(R1C=0,"" "",UserForm1.ComboBox1.Value) _
)))"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub
Selection.FormulaR1C1 = _
"=IF(R2C=1,"" "",IF(R1C=6,"" "",IF(R1C=7,"" "",IF(R1C=0,"" "",UserForm1.ComboBox1.Value) _
)))"
Vielen Dank im Voraus.