Opening and closing a picture dialogue box

J

jontait

Hi all

I need some help (I'm not very good with VB):

I'd like to have a command button within my spreadsheet that whe
clicked on opens up the INSERT PICTURE FROM FILE dialogue box, whic
then allows a user to select a picture then click OK.

Sounds easy? Well the problem is that the spreadsheet is going to b
password protected. Therefore the command button needs to:

1. When clicked on automatically unprotect the spreadsheet (by secretl
inserting the password - i.e. without the users seeing what it is)
2. Open up the INSERT PICTURE FROM FILE dialogue box
3. Allow a user to find their pic and click OK (go to 4)
3a. If the user clicks cancel go to 4
4. Close the dialogue box and automatically re-protect th
spreadsheet.
5. Allow the user to re-size and drag the picture around to where the
want it.

Is this possible?? I really want the spreadsheet protected as there ar
certain control boxes and nested IF's that I don't want the user to b
able to alter, but one of the key features is that they are to be abl
to enter a picture into the sheet.

Thank you for all your hel
 
R

Robin Hammond

This works in XP. In earlier versions (certainly 97, not sure about 2000)
the protection object doesn't have a drawingobjects parameter, so you might
have trouble allowing users to move the pics around.

For an explanation of the commandbar thing in the code, see
http://www.enhanceddatasystems.com/ED/Pages/ExcelObscureDialogs.htm

Sub GetPicture()

Dim cbInsertPicFromFile As CommandBarControl
Set cbInsertPicFromFile = CommandBars.FindControl(ID:=2619)
ActiveSheet.Unprotect Password:="MyPassword"
cbInsertPicFromFile.Execute
ActiveSheet.Protect Password:="MyPassword", DrawingObjects:=False,
Contents:=True

End Sub

HTH,

Robin Hammond
www.enhanceddatasystems.com
 
T

TroyW

Try the code below. The code needs to go into the Sheet object code pane,
not a standard Module code pane. The example below assumes the name of the
button is: CommandButton1
Replace "mypassword" with the password for your sheet.

Troy


Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="mypassword"
Application.Dialogs(xlDialogInsertPicture).Show
ActiveSheet.Protect Password:="mypassword"
End Sub
 
J

jontait

Hi troy

Thanks for your help but unfortunately it gave me an error.

When I clicked on the button I got the following message:

Runtime error '1004'
Unprotect method of worksheet class failed

Then when I hit the debug button the first line of your code i
highlighted.

Do I need to change something? I'm working on Excel 97 at the moment
does that have any bearing?

Thanks again for your help - much appreciate
 
J

jontait

Sorry Robin

I must be missing something, I can't get it to work. Ive created a ne
Command Button and then in its VB code I've copied and pasted you
example and changed the password to reflect my individual password, bu
still no luck

I'm really bad at VB, very much a beginner. Would it be possible fo
you to PM or attach an example workbook here for me so I can "revers
engineer" it to meet my needs?

Sorry to be a pain in the neck!

Jo
 
T

TroyW

Try setting the "TakeFocusOnClick" property of CommandButton1 to False.
Excel97 is picky about this.

Troy
 
J

jontait

Thanks Troy

That works a treat, but the user is unable to change th
height/width/position of the image

Is there any way of adding to that code to tell excel either:

1. Make the picture say 2inches high by 1inch wide? And also for th
picture to sit in a particular range on the sheet?

2. OR allow the user the ability to move and re-size the picture usin
the "drag handles" on its axis

I know I'm asking a lot here but the images are likely to be digita
camera downloads so they will be quite large when displayed on th
screen

Thanks so much for your help!
 
T

TroyW

The picture will appear relative to the location of the activecell. The
upper left corner of the picture will be aligned with the active cell. You
have a couple of options on changing the size of the picture.

1) Include a statement that makes all pictures have a specific height (e.g.
100)
2) Change the protection so that DrawingObjects are editable by the user
(move and resize).

See the code below for examples.

Troy


Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="mypassword"
Application.Dialogs(xlDialogInsertPicture).Show

'''Define the height of the picture as 100 (width will automatically keep
aspect ratio).
Selection.ShapeRange.Height = 100

'''Password Protect the sheet Contents, but leave the DrawingObjects
editable.
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Password:="mypassword"
End Sub
 
J

jontait

Hi Troy

The second option would be best for me, then the user could move an
resize the image they've imported. However when i enter the code int
the VB editor

Private Sub CommandButton1_Click()
TakeFocusOnClick = False
ActiveSheet.Unprotect Password:="mypassword"
Application.Dialogs(xlDialogInsertPicture).Show
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
ActiveSheet.Protect Password:="mypassword"
End Sub

it shows this line in red:
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,

Is there a problem here that I'm not seeing?

I'm on a very steep learning curve here but this forum is invaluable!

Thanks agai
 
T

TroyW

The line got word-wrapped. I've reformatted the code so that hopefully the
longer lines won't wrap to the next line. I would consider keeping the code
that automatically sizes the picture to an initial size. The user may find
it easier to enlarge a smaller picture than trying to reduce a large picture
that spills out of the active window.

FYI, if the user holds down the Alt key while they are moving the image with
the mouse, it will "snap" to the corners of the cell grid lines. The Alt key
can also be used when changing the size of the picture to "snap" it to a
cell grid line.

Troy


Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="mypassword"
Application.Dialogs(xlDialogInsertPicture).Show

'''Initialize the height of the picture as 150
'''(width will automatically keep aspect ratio).
Selection.ShapeRange.Height = 150

'''Password Protect the sheet Contents,
'''but leave the DrawingObjects editable.
ActiveSheet.Protect _
DrawingObjects:=False, _
Contents:=True, _
Password:="mypassword"

End Sub
 
J

jontait

Thanks for your help, however when i run the code from my button (afte
I've protected the sheet using the correct password!) I get th
following error:

RUN TIME ERROR '1004'

YOU CANNOT USE THIS COMMAND ON A PROTECTED SHEET. TO UNPROTECT TH
SHEET USE THE UNPROTECT SHEET COMMAND (TOOLS MENU, PROTECTION SUBMENU
YOU MAY BE PROMPTED FOR A PASSWOR

When I hit the debug button the line of code abou
Applications.Dialogues etc is shown in yellow

Is there something missing or going wrong somewhere??

Your help is very much appreciated. Its all starting to make sense no
but its so frustrating not knowing how to fix these bugs (a minefiel
for me but childs play to anyone else!)

Thanks agai
 
J

jontait

Troy

We've cracked it! The following code works perfectly. As long as you'v
protected the worksheet prior to it being run then its fine. I've onl
tried it on Excel 97 but I'm hoping to run it on Excel XP and Exce
2000 as soon as I can. Thanks so much for all your help, I know I'v
taken up a lot of your time...its been so useful!

Private Sub CommandButton1_Click()

CommandButton1.TakeFocusOnClick = False
ActiveSheet.Unprotect Password:="xxxxx"
Application.Dialogs(xlDialogInsertPicture).Show
Selection.ShapeRange.Height = 150
ActiveSheet.Protect Password:="xxxxx", DrawingObjects:=False
Contents:=True

End Su
 
T

TroyW

I would make one more change to your code. I forgot to trap for the
possibility that the user hits Cancel on the InsertPicture dialog. The new
code exits the subroutine if a Cancel (False) value is encountered. See the
code below.

Troy


Private Sub CommandButton1_Click()
Dim bRtn As Boolean

ActiveSheet.Unprotect Password:="mypassword"
bRtn = Application.Dialogs( _
xlDialogInsertPicture).Show

If bRtn = False Then Exit Sub

Selection.ShapeRange.Height = 150

ActiveSheet.Protect _
Password:="mypassword", _
DrawingObjects:=False, _
Contents:=True

End Sub
 
J

jontait

Hi Troy

Hope you had a good weekend, that's great but unfortunately when th
user clicks CANCEL it goes back to the sheet but now the sheet i
UNPROTECTED.

Is there anyway it can protect the sheet again if the user hit
cancel?

If not then thats fine I'll just make a text box next to the butto
explaining what to do if the user hits cancel

Many thank
 
Top