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
Hi,
Please supply your file so we don't have to recreate it.
Thanks
Phil
File attached it has links to ther files so graphs wont change but everything else should work
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
Thanks,
I tried with the case select but it doesnt appear to do anything it runs but doesnt change font and skips over the Application.Caller Shape
There is no code there to change font?
Application.Caller will only work if the code is called by clicking a button.
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:Daten5_Werksorganisation2_QSMGB_57_ComplaintsCustomerCopy of Customer Complaint Log 20222.xlsx'!CompLog[Comp number]
I mistyped its the back color thats supposed to change, but I now changed it to forecolor and it works.
thanks
