Solved: shapes.visible

[tkaplan]

I have a worksheet that has a button named cmdTrend. i am trying to write code that depending on certain conditions, the button changes to visible and not visible.

i currently have:

[VBA]
'sheet and workbook are unprotected
if <<condition=true>> Then
'i put this line in to try and see if it would make a difference
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.Shapes("cmdTrend").Select
Selection.Visible = True
Application.CommandBars("Control Toolbox").Visible = False
Else
If ActiveSheet.Shapes("cmdTrend").Visible = True Then
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.Shapes("cmdTrend").Select
Selection.Visible = False
Application.CommandBars("Control Toolbox").Visible = False
End If
End If
[/VBA]

Half of this works - if the button is visible and the condition is false, it sets the button to invisible. however, if the botton is not visible and the condition is true, i receive the following error:
Method 'select' of object 'shapes' failed.

I figure that what is going on is it cant find the shape b/c it is not visible. How can i fix this?

Thanks in advance.
tkaplan

[mvidas]

Hi tkplan,

You can actually have it much simpler than you currently do:[vba] If <<condition=true>> Then
ActiveSheet.Shapes("cmdTrend").Visible = True
Else
ActiveSheet.Shapes("cmdTrend").Visible = False
End If[/vba]Your error was occurring because you were trying to select something hidden, which excel doesnt like to do.
Matt

[tkaplan]

ah. thank you. i thought you couldn't make something visible without it being selected first. but this works so I'm happy

Thanks.

[mvidas]

Glad to help!
It always better if you can avoid .Select'ing something in VBA, as that slows down the macro runtime considerably.

Also, you can close this thread by going to Thread Tools at the top of it and choosing "Mark Thread Solved".. this doesn't actually close it, new comments can still be added, but it helps to clean up the forum.

Let me know if theres anything else I can help you with!
Matt