

July 17, 2018

Hi I have a sheet with a lot of shapes on some of whic I am using as buttons I tried a short piece of code to fade the text of the clicked shape but it gives a type mismatch error on the If line
Sub ClickedBtn()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Name = "btn_Hetton" Or "btn_GMH" Or "btn_Kibi" Or "btn_MHarb" Or "btn_All" Then
shp.Fill.BackColor.RGB = RGB(0, 0, 0)
Else
End If
Next shp
ActiveSheet.Shapes(Application.Caller).Fill.BackColor.RGB = RGB(192, 192, 192)
End Sub


Trusted Members
Moderators

November 1, 2018

You can't write Or criteria like that - you have to repeat the test of the shape name each time.
If shp.Name = "btn_Hetton" Or shp.Name = "btn_GMH" Or shp.Name = "btn_Kibi" Or shp.Name = "btn_MHarb" Or shp.Name = "btn_All"
Alternatively, use a Select Case construction:
For Each shp In ActiveSheet.Shapes
Select Case shp.Name
Case "btn_Hetton", "btn_GMH", "btn_Kibi", "btn_MHarb", "btn_All"
shp.Fill.BackColor.RGB = RGB(0, 0, 0)
Case Else
' do nothing
End Select
Next shp

Answers Post


Trusted Members
Moderators

November 1, 2018



Trusted Members

October 17, 2018

And the file will also not work if there are unresolved references because it points either to a renamed file of an entire different file.
When posting a file make sure it works for the perosons outside your environment
e.g.
='Z:\Daten\5_Werksorganisation\2_QS\MGB_5\7_Complaints\Customer\Copy of Customer Complaint Log 20222.xlsx'!CompLog[Comp number]
1 Guest(s)
