VBA code excel

[mcknikker]

I'm trying to write a vba script to change the background color of a rectangle (button).
By click it has to change the color between Red or green.

The example code i use is:
Sub Rechthoek1_Klikken(ByVal target As Object)
If target.Interior.ColorIndex = xlNone Then
target.Interior.ColorIndex = 43
ElseIf target.Interior.ColorIndex = 43 Then
target.Interior.ColorIndex = xlNone
End If
End Sub


The error message i receive is:
"The argument is not optional"

Thanks in advance

BR,
Stephan

[SamT]

What is the Name of the Shape that calls Sub Rechthoek1_Klikken when clicked

Try something like

Shapes("Name of Shape").Fill.BackColor.SchemeColor = 43

[Logit]

.
Or if you use ACTIVEX button ... paste in the worksheet module (this example is using Sheet1) :



Option Explicit


Private Sub CommandButton1_Click()

ClickButClrChng


End Sub


Sub ClickButClrChng()


If Sheets("Sheet1").CommandButton1.BackColor = vbRed Then
Sheets("Sheet1").CommandButton1.BackColor = RGB(224, 224, 224)
Else
Sheets("Sheet1").CommandButton1.BackColor = vbRed
End If


End Sub

[p45cal]

Can you supply a a very simple workbook with object and code that shows this error?

[snb]

The Click-event can't contain parameters, arguments

Sub Rectangle1_Click()

End Sub

[Logit]

.
Paste this in the Sheet Level Module where the rectangle is located :



Option Explicit


Sub ClickRect1()


If Shapes("Rectangle 1").Fill.BackColor.SchemeColor = 43 Then
Shapes("Rectangle 1").Fill.BackColor.SchemeColor = 22
Else
Shapes("Rectangle 1").Fill.BackColor.SchemeColor = 43
End If


End Sub




Then Right Click the rectangle and select ASSIGN MACRO, selecting the above macro.

[mcknikker]

Thanks!

[mcknikker]

Thank you all for helping out!

It worked by using the code:

Sub Rechthoek2_Klikken()


If Blad1.Shapes("Rechthoek 2").Fill.ForeColor.RGB = RGB(12, 114, 38) Then
Blad1.Shapes("Rechthoek 2").Fill.ForeColor.RGB = RGB(110, 18, 19)
ElseIf Blad1.Shapes("Rechthoek 2").Fill.ForeColor.RGB = RGB(110, 18, 19) Then
Blad1.Shapes("Rechthoek 2").Fill.ForeColor.RGB = RGB(12, 114, 38)


End If
End Sub

[Logit]

You are welcome.

[p45cal]

Similar:
Sub Rechthoek2_Klikken()
With Blad1.Shapes("Rechthoek 2").Fill.ForeColor
.RGB = IIf(.RGB = RGB(12, 114, 38), RGB(110, 18, 19), RGB(12, 114, 38))
End With
End Sub

It has one advantage which is that if the shape is not initially one of those two colours it will still work.

[mcknikker]

works like a charm, Thnx :)