AW: MsgBox ersetzen?
12.09.2005 15:43:45
Matthias
Hallo Markus,
kopiere den Code innerhalb der Prozedur Selection_Change.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim zelle As Range
Dim wert As String
Dim bereich As Range
Set bereich = Sheets("Tabelle1").Range("a8:a15")
'erste Spalte:
If Not Intersect(Target, Range("c10:c13")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
For Each zelle In bereich
If zelle = Target.Offset(0, -2) Then wert = wert & zelle.Offset(0, 3) & vbLf
Next
With Me.Shapes("Text Box 1")
.TextFrame.Characters.Text = wert
.Left = Target.Left + Target.Width
.Top = Target.Top + Target.Height
.Visible = True
End With
'zweite Spalte:
ElseIf Not Intersect(Target, Range("D10:D13")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
For Each zelle In bereich
If zelle = Target.Offset(0, -2) Then wert = wert & zelle.Offset(0, 3) & vbLf
Next
With Me.Shapes("Text Box 1")
.TextFrame.Characters.Text = wert
.Left = Target.Left + Target.Width
.Top = Target.Top + Target.Height
.Visible = True
End With
'dritte Spalte:
ElseIf Not Intersect(Target, Range("E10:E13")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
For Each zelle In bereich
If zelle = Target.Offset(0, -2) Then wert = wert & zelle.Offset(0, 3) & vbLf
Next
With Me.Shapes("Text Box 1")
.TextFrame.Characters.Text = wert
.Left = Target.Left + Target.Width
.Top = Target.Top + Target.Height
.Visible = True
End With
'weder noch:
Else
With Me.Shapes("Text Box 1")
.Visible = False
End With
End If
End Sub
Gruß Matthias