How select all pictures in a worksheet and make visible?

[Gunnar Johansson]

I have a vba code that has worked well for a while, but during some changes
all pictures become hidden when I run the code. I have searched for a
explanation and will contiune, but would need a quick and dirty solution
right now to just tell them in the end of the code like:
"Worksheet(1).pictures.visible = True" .

Of course is the above mentioned example not working, but it give the idea
of my needs. Probably the solutions is a for each..next solution and that's
ok, of cource.

NOTE:
You have to separate it from the chartobjects also within the worksheet
/

Reagrds

 

[Norman Jones]

Hi Gunnar,

Try changing:

Worksheet(1).pictures.visible = True

to:

Worksheets(1).Pictures.Visible = True

 

[Gunnar Johansson]

Sorry,
That syntax doesn't work, that was just an example.

/Regards

 

[Dave Peterson]

The only time I've seen Norman's suggestion fail was when there were lots of
pictures on that worksheet. (You sure you wanted to use worksheets(1)--maybe it
would be better like:

Worksheets("sheet1").Pictures.Visible = False

(change sheet1 to the correct name)

if it doesn't work, one workaround is to iterate through the pictures:

Dim myPict As Picture
For Each myPict In ActiveSheet.Pictures
myPict.Visible = True
Next myPict

 

[Tom Ogilvy]

did you miss that Norman changed
Worksheet(1)
to
Worksheets(1)

adding an "s"

 

[Gunnar Johansson]

Thank you Norman, Tom and Dave!

It work now. The pictures was_not_hidden_! When I ran following code to
"exchange" chartobjects to pics (less trouble when users paste them into
powerpoint, secure source links of the charts etc) it pasted an object (the
picture?) a few pixels big and scaled it up 3600% to right size - same as
the chart. Since Excel doesn't allow this kind of big numbers in the
prercentage fields of the pictures, it went into trouble.

It work ok now, both this sub below (has not changed it, but now it's ok)
and of course the: Sheet102.Pictures.Visible = True
About the name of the sheet: I always use the CodeName in the code

I don't need an reply of this, just want to share my code and experience to
you. /Regards


Good Sub to create Pictures instead of chartsobjects in a worksheet
and then hide the charts for protection:

Sub CreatePicsHideCharts()
On Error GoTo ErrorHandler
Dim chtobj As ChartObject

Application.EnableEvents = False
Application.ScreenUpdating = False

Sheet102.Pictures.Delete
Sheet102.ChartObjects.Visible = True
Sheet102.Activate
Sheet102.Range("A1").Select

For Each chtobj In Sheet102.ChartObjects
chtobj.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheet102.Paste
Selection.Visible = True
With Selection
.Left = chtobj.Left
.Top = chtobj.Top
.Width = chtobj.Width
.Height = chtobj.Height
End With

chtobj.Chart.ProtectData = False
chtobj.Visible = False
chtobj.Chart.ProtectData = True
Next chtobj

Exit Sub

'Errorhandler RappCopyPic
ErrorHandler:
MsgBox prompt:="Unexpected error (errorkod " & Str$(Err.Number) & ") " & _
"take place in Sub CreatePicsHideCharts initiated by ws_activate in Report
Sheet" & vbCrLf _
& "Error description: " & Err.Description, _
Buttons:=vbCritical + vbMsgBoxHelpButton, _
Title:="Error!", _
HelpFile:=Err.HelpFile, _
Context:=Err.HelpContext
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

About the name of the sheet: I always use the CodeName in the code

/Regards,