Log In
Register
Home
July 17, 2018
Offline
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
See Answer
October 5, 2010
Offline
Hi,
Please supply your file so we don't have to recreate it.
Thanks
Phil
July 17, 2018
Offline
File attached it has links to ther files so graphs wont change but everything else should work
Trusted Members
Moderators
November 1, 2018
Offline
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
July 17, 2018
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
There is no code there to change font?
Application.Caller will only work if the code is called by clicking a button.
Trusted Members
October 17, 2018
Offline
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)
