PDA

View Full Version : [SOLVED] VBA code excel



mcknikker
01-06-2020, 07:37 AM
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
01-06-2020, 08:58 AM
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
01-06-2020, 09:09 AM
.
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
01-06-2020, 10:30 AM
Can you supply a a very simple workbook with object and code that shows this error?

snb
01-07-2020, 02:10 AM
The Click-event can't contain parameters, arguments

Sub Rectangle1_Click()

End Sub

Logit
01-07-2020, 08:02 AM
.
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
01-07-2020, 08:16 AM
Thanks!

mcknikker
01-07-2020, 08:17 AM
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
01-07-2020, 08:33 AM
You are welcome.

p45cal
01-07-2020, 08:48 AM
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
01-07-2020, 11:44 PM
works like a charm, Thnx :)