Select case statement - it will not work and i dont understand why! :o(

[Thomas]

Hi NG,

Can anyone tell me what i am doing wrong in the below code?
In the first version i was actively selecting the rectangle, and it was
working. I changed it to a with - statement because it was pretty
annoying that the rectangle constantly was selected.
But now?

thx in advance
Thomas

--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Number
Number = Range("a1").Value
Select Case Number
Case 1
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 44
.Transparency = 0.8
End With
Case 2
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 34
.Transparency = 0.5
End With
Case 3
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 24
.Transparency = 0.3
End With
Case Else
With ActiveSheet.Shapes("Rectangle 1").ShapeRange.Fill
Visible = msoFalse
End With
End Select
End Sub

 

[Tom Ogilvy]

this worked for me:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Number
Number = Range("a1").Value
Select Case Number
Case 1
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 44
.Transparency = 0.8
End With
End With
Case 2
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 34
.Transparency = 0.5
End With
End With
Case 3
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoTrue
With .Fill
.Solid
.ForeColor.SchemeColor = 24
.Transparency = 0.3
End With
End With
Case Else
With ActiveSheet.Shapes("Rectangle 1")
.Visible = msoFalse
End With
End Select
End Sub

 

[Thomas]

And for me too. Great!

Thank you

 

[Don Guillett]

try this which will change the shape when you change cell a1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target
Case 1: x = 4
y = 0.8
Case 2: x = 5
y = 0.5
Case 3: x = 6
y = 1
Case Else
End Select
With Worksheets(1).Shapes(1).Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = x
.Transparency = y
End With
End Sub