Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to get Image.SpecialEffect on module-level

Status
Not open for further replies.

Vashkar

Programmer
Sep 16, 2012
14
NL
One dumbass question but it drives me insane for the last couple of weeks.

Excel 2013:

Worksheet has 7 ActiveX-Images shaped as buttons with properties SpecialEffect = fmSpecialEffectRaised
On click the pressed button will go to Sunken, all the rest will be raised

On Worksheetlevel:
Private sub imgButton1_Click()
imgButton1.SpecialEffect = fmSpecialEffectSunken
imgButton2.specialeffect = fmSpecialEffectRaised
'etc
End Sub

Since this will be on several sheets, I want to adress that piece of code on module-level.
Worksheet:
Private sub imgButton1_Click()
sIDnr = "1": imgButtonState sIDnr
End Sub
etc.

Module:
Sub imgButtonState(sIDnr as String)
Dim iCnt as Integer, sCnt as String
For iCnt = 1 to 7 'loop through buttons
sCnt = right(str(iCnt),1) 'convert integer to string and remove the space in front
ActiveSheet.Shapes("imgButton" & sCnt).SpecialEffect = fmSpecialEffectRaised 'raise all buttons
next iCnt
ActiveSheet.Shapes("imgButton" & sIDnr).SpecialEffect = fmSpecialEffectSunken 'sink the pressed button
End Sub

The problem is that you can't adress .SpecialEffect on module-level.
It results in: runtime error 438 object doesn't support this property or method.
The helpsection of MS is a complete aggrivating disaster area on which you only want to blow up bing and if you find anything on the special effect on the internet it's based on the userform private subs.
So if anybody would be so kind to give me the indication where the specialeffect is residing on module-level you will have definitely save my sanity.
 
I'd try something like this:
ActiveSheet.Shapes("imgButton" & sCnt).OLEFormat.Object.SpecialEffect

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Well I tried the given solution, and proceeded by changing
ActiveSheet.Shapes("imgButton" & sCnt).SpecialEffect = fmSpecialEffectRaised 'raise all buttons
into
ActiveSheet.Shapes("imgButton" & sCnt).OLEFormat.Object.SpecialEffect = 1
The result was error 438, object doesn't support this property or method

It's odd because when you google OLEformat.object, you see it indeed been given often as the solution to set all kinds of formatting.
I have no idea as the why it doesn't work on me. Maybe a 2013-version change?
Yet it put me in the direction of OLEobjects and after some trial and error I found this is the solution that worked for me.

ActiveSheet.OLEObjects("imgButton" & sIDnr).Object.SpecialEffect = 1

Anyway thanks for opening the door to finding a solution....finally.
It's appreciated.

 
For any reason the "Object" has to be called twice:

ActiveSheet.Shapes("imgButton" & sCnt).OLEFormat.Object.Object.SpecialEffect = 1


combo
 
>The problem is that you can't adress .SpecialEffect on module-level

The problem is that ActiveSheet.Shapes("imgButton" & sCnt) returns a Shape, not an Image, and thus does not have a .SpecialEffect property.

The solution that you have found yourself is one way of properly exposing the ActiveX Image object.



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top