Problem on inserting photo image to a password protected worksheet

[Andy Soho]

Hi,

I have created a worksheet using Excel 97 to be shared by my associates. I
have password-protected the worksheet as I want to keep the integrity of the
format. There are some cells that user input are allowed and user is
required to insert a JPG image on a cell that is un-protected. The problem
is Insert - Picture - From File is disabled because of the worksheet
protection. What should be done to have this feature enabled while still
have the worksheet protection kept.

Now, user has to use the copy and paste method to insert the photo which is
not time efficient.

Thanks
Andy

[Dave Peterson]

Maybe you could give them a macro that would prompt them for the name of the
file, unprotect the worksheet, add the picture and reprotect the worksheet.

If yes, how about this to get you started:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim myPictName As Variant
Dim myPict As Picture

Set curWks = ActiveSheet

myPictName = Application.GetOpenFilename
If myPictName = False Then
'user hit cancel
Exit Sub
End If

With curWks
.Unprotect Password:="hi"
Set myPict = .Pictures.Insert(myPictName)
With ActiveCell
myPict.Top = .Top
myPict.Width = .Width
myPict.Left = .Left
myPict.Height = .Height
End With
.Protect Password:="hi"
End With

End Sub

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

In fact, if I protect the worksheet in code, I can give it a special parameter
that allows the code to make changes that the user can't.

If I add this to Auto_Open() (or workbook_open):
Sub auto_open()
Worksheets("sheet1").Protect Password:="hi", userinterfaceonly:=True
End Sub

I can drop the .unprotect and .protect lines from the code.

(The userinterfaceonly setting has to be reset each time you open the workbook.
xl doesn't remember it if you close the workbook.)

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

In fact, if I protect the worksheet in code, I can give it a special parameter
that allows the code to make changes that the user can't.

If I add this to Auto_Open() (or workbook_open):
Sub auto_open()
Worksheets("sheet1").Protect Password:="hi", userinterfaceonly:=True
End Sub

I can drop the .unprotect and .protect lines from the code.

(The userinterfaceonly setting has to be reset each time you open the workbook.
xl doesn't remember it if you close the workbook.)

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

In fact, if I protect the worksheet in code, I can give it a special parameter
that allows the code to make changes that the user can't.

If I add this to Auto_Open() (or workbook_open):
Sub auto_open()
Worksheets("sheet1").Protect Password:="hi", userinterfaceonly:=True
End Sub

I can drop the .unprotect and .protect lines from the code.

(The userinterfaceonly setting has to be reset each time you open the workbook.
xl doesn't remember it if you close the workbook.)

--

Dave Peterson
ec3...@msn.com

[Andy Soho]

Hi Dave,

My earlier reply seemed gone astray.

Thanks, I tried your suggestions and they work perfectly the way I wanted.
Many thanks.
One last question, is it possible to change the code so that the photo will
fit a 36 x 192 (column x row) size of cell pre-defined for the photo ?

Regards
Andy

"Dave Peterson" <ec3...@msn.com> ???????:3DF36309...@msn.com...

[Dave Peterson]

Answered in a private email:

Something to the effect of playing around with this line:

With ActiveCell

Make it look something like:

With ActiveCell.resize(36,192)

The resize(x,y) says to resize the original range (activecell) so that it
includes x rows and y columns.

===========

Andy, in case you didn't get my other reply to your email (which asked how he
could let the user move & resize the picture):

===========

You can resize by code (unprotect, do the work, and re-protect) or you can
unprotect it for the user.

I'm not sure how you know how to resize your picture so that you could do it by
code.

Maybe something like this to allow the user to do the resizing.
(if the active cell is in one of the rows that holds the picture.)

Option Explicit
Sub allow_user()

Dim myPict As Picture
Dim curWks As Worksheet
Set curWks = ActiveSheet

For Each myPict In curWks.Pictures
If (ActiveCell.Row >= myPict.TopLeftCell.Row) _
And (ActiveCell.Row <= myPict.BottomRightCell.Row) Then
curWks.Unprotect Password:="hi"
myPict.Select
Exit For
End If
Next myPict

End Sub

Then in the code behind the worksheet with the pictures, reprotect it as soon as
they click on a cell.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.ProtectContents Then
'do nothing
Else
Me.Protect Password:="hi"
End If

End Sub

--

Dave Peterson
ec3...@msn.com