?
Solved

HOW D I CLEAR PICTURES IN IMAGE CONTROL AND COMMENTS IN CELLS WHEN WORKBOOK IS OPENED

Posted on 2016-11-03
18
Medium Priority
?
140 Views
Last Modified: 2016-11-27
I have an Image control in my my excel worksheet.
I load pictures into the image ActiveX in the worksheet and add comments to a array of cells A4:D5.
Whenever the workbook is closed and opened the last picture loaded in the image control and the previous riddles (comments) appear, but i don't want it to be so.

When the worksheet is opened the riddles must appear only after the start button is pressed (The previous comments must be cleared when the worksheet is opened)

and

the pictures must load only after the user enters the answer and clicks the submit button.
0
Comment
Question by:joseph narh padi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
18 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41873279
Can you supply a workbook please?
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41873405
You may try something like this.....

Private Sub Workbook_Open()
On Error Resume Next
'Assuming Image1 is the name of image control on Sheet1
Sheet1.Shapes("Image1").Visible = False
'Clearing the cells on Sheet1
Sheet1.Range("A4:D5").ClearContents
End Sub

Open in new window

Also in the coding behind the Start button when you want the image to be appeared, you may turn the Visible property to True again.
0
 

Author Comment

by:joseph narh padi
ID: 41873477
Thank very much.
Mr. Martin, it's the same workbook you worked on.

Neeraj, I'll give you feedback. Thanks very much
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:joseph narh padi
ID: 41873489
Hello Neeraj,
I put this in "This Workbook"
The label is cleared of it's content,
the cells are cleared of their content,
but the image in the image frame is not cleared
and the comments in the cell are not cleared.

Private Sub Workbook_Open()
Dim Image2 As Image

On Error Resume Next
'Assuming Image1 is the name of image control on Sheet1
Sheet1.Shapes("Image2").Picture = ""
'Clearing the cells on Sheet1
Sheet1.Range("A1:D5").ClearContents
Sheet1.Range("A1:D5").ClearComment
Sheets("Sheet1").Label1.Caption = ""
End Sub

Open in new window

0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41873492
Line#6 should be like this.....
Sheet1.Shapes("Image2").Picture.Visible = False

Open in new window

0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41873497
Or you may try something like this....

Sheet1.Image2.Picture = LoadPicture("")

Open in new window

0
 

Author Comment

by:joseph narh padi
ID: 41873508
I have modified it to

Private Sub Workbook_Open()
Dim Image2 As Image

On Error Resume Next
'Assuming Image1 is the name of image control on Sheet1
Sheet1.Shapes("Image2").Picture.Clear
'Clearing the cells on Sheet1
Sheet1.Range("A1:D5").ClearContents
Sheet1.Range("A1:D5").ClearComment
Sheets("Sheet1").Label1.Caption = ""
Worksheets("Sheet1").Select ' assume you use Sheet1
Cells.Select
Selection.Clear
End Sub

Open in new window


the comments have been cleared but the picutre in the image is not cleared
0
 

Author Comment

by:joseph narh padi
ID: 41873517
Okay, Thanks
0
 

Author Comment

by:joseph narh padi
ID: 41873525
I used

Sheet1.Image2.Picture = LoadPicture("")

Open in new window


and it gave an error
Method or data member not found
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41873599
In the line above, Sheet1 is the code name of the sheet not the sheet name and Image2 is the name of the image control.
Make sure both are found and correct.
0
 

Author Comment

by:joseph narh padi
ID: 41873615
Thanks very much.

I'm not sure of how they should be found but the work is in sheet1 and the name of the Image control is Image2.
I declared the Image control.
What else should I do?
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41873618
Can you upload the workbook here?
0
 

Author Comment

by:joseph narh padi
ID: 41873725
Thanks.
I'm currently at work but I posted one in this forum title How to use mouse move event.
Please if you don't mind, you may download it from there.
0
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41873743
You got an error because the name of your image control was Image1 not Image2, see the properties of the Image Control.
Please try this and it should work for you.
Private Sub Workbook_Open()
On Error Resume Next
'Assuming Image1 is the name of image control on Sheet1
Sheet1.Image1.Picture = LoadPicture("")
'Clearing the cells on Sheet1
Sheet1.Range("A1:D5").ClearContents
Sheet1.Range("A1:D5").ClearComments
Sheets("Sheet1").Label1.Caption = ""
Worksheets("Sheet1").Select ' assume you use Sheet1
Cells.Select
Selection.Clear
End Sub

Open in new window

0
 

Author Comment

by:joseph narh padi
ID: 41873779
Okay. Thanks. But I used image2 for the load picture. And it's reference in the sheet1 method reads
Sub Image2 ()
What constitutes the name then?
Okay I'll check from the property window.

I'll give you feedback as soon as I get home. Thanks  once again
0
 

Author Comment

by:joseph narh padi
ID: 41874065
Thanks very much.
I checked from the property window and it is Image1.
I have also realised that i referenced it Image1 in the loadpicture method.
Why then is it referenced Image2 in the worksheet?
If I use design mode and select the image control, it shows in the formula bar on the left "Image2" and on the right "EMBED ("Forms.Image1", "")"
That's why I thought the name was Image2.
Thanks very much. it works perfectly well

I have another post heading

CONDITIONAL FORMATING OF CELLS USING EXCEL VBA CODES

in this forum based on the same workbook. would you mind looking at it?
0
 

Author Closing Comment

by:joseph narh padi
ID: 41903386
Thanks very much for your cooperation. I appreciate you for the assistance and the promptness with which you attended to my question. God bless you
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question