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.
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.