Hi,

I have a sheet with the names of my students. I let the user see a picture
of them on a modeless window (so cells can still be modified). To see the
form with the picture, a button must be pressed on the toolbar.

Q1: when the form is displayed, how can I give back focus on the sheet
(without clicking on it)?
Q2: how can I prevent that the picture is stretched out (misformed) when it
is too small, but is always shrinked when it is too big (so you see the
whole picture without deformation). When I use a picture-object, I cannot
shrink, so I used a button (with picture), but the I get a deformation.

Can you help me?
Thanks
Jos Vens

Jos,

I set up UserForm1. I used the Image tool in the Toolbox, and made Image1.
I used to following code to show a modeless UserForm:

UserForm1.Show xlModeless
UserForm1.Image1.PictureSizeMode = fmPictureSizeModeZoom

To put focus back on the sheet, either or both of these:
'Sheets("Sheet1").Activate
'Sheets("Sheet1").Range("A2").Select

Then to change the picture, at any time:

PictureName = "C:\C5.jpg"
UserForm1.Image1.Picture = LoadPicture(PictureName)

It didn't deform the picture.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Hi,

I have a sheet with the names of my students. I let the user see a
picture of them on a modeless window (so cells can still be modified). To
see the form with the picture, a button must be pressed on the toolbar.

Q1: when the form is displayed, how can I give back focus on the sheet
(without clicking on it)?
Q2: how can I prevent that the picture is stretched out (misformed) when
it is too small, but is always shrinked when it is too big (so you see the
whole picture without deformation). When I use a picture-object, I cannot
shrink, so I used a button (with picture), but the I get a deformation.

Can you help me?
Thanks
Jos Vens

Thanks Earl,

I could solve my problems, except one thing:

When I'm on row 45, I jump to the next column row 5, so the user can put in
the next column of data. If I do this, the picture is not loaded. Even
when I force the Set_Picture procedure in the Workbook_SheetChange event, I
appears for an instant and then disappears.

Thanks
Jos Vens

I use the event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

SET_Picture (Target.Row)

End Function

and the procedure

Function SET_Picture(vRow As Long, Optional vForce)

Dim vFile As String, vFolder As String, vExt As String

If (IsMissing(vForce)) Then
vForce = False
End If

If (Libra_FrmFoto.Visible) Or CBool(vForce) Then

If (vRow 4) And (vRow < 45) Then

vFolder = GET_pPath("Data", spParameters.[gCampus]) & "Foto\" &
spParameters.[gKlas] & "\"
If (HFS_Exist(vFolder)) Then

vFile = Dir(vFolder)
vExt = IIf(vFile = "", "", Right(vFile, 4))

Else

HFS_CreateDir vFolder
vExt = ""

End If

If (vExt = "") Then

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

Else

vFile = vFolder & Format(vRow - 4, "00") & vExt

Libra_FrmFoto.Caption = ActiveSheet.Cells(vRow, 2)
Libra_FrmFoto.vFoto.Picture = LoadPicture(vFile)
Libra_FrmFoto.Repaint

End If

Else

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

End If

End If

End Function





"Earl Kiosterud" schreef in bericht
...
Jos,

I set up UserForm1. I used the Image tool in the Toolbox, and made
Image1. I used to following code to show a modeless UserForm:

UserForm1.Show xlModeless
UserForm1.Image1.PictureSizeMode = fmPictureSizeModeZoom

To put focus back on the sheet, either or both of these:
'Sheets("Sheet1").Activate
'Sheets("Sheet1").Range("A2").Select

Then to change the picture, at any time:

PictureName = "C:\C5.jpg"
UserForm1.Image1.Picture = LoadPicture(PictureName)

It didn't deform the picture.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Hi,

I have a sheet with the names of my students. I let the user see a
picture of them on a modeless window (so cells can still be modified).
To see the form with the picture, a button must be pressed on the
toolbar.

Q1: when the form is displayed, how can I give back focus on the sheet
(without clicking on it)?
Q2: how can I prevent that the picture is stretched out (misformed) when
it is too small, but is always shrinked when it is too big (so you see
the whole picture without deformation). When I use a picture-object, I
cannot shrink, so I used a button (with picture), but the I get a
deformation.

Can you help me?
Thanks
Jos Vens

Jos,

I haven't looked at your code in much detail -- I don't know what some of
the stuff is. But since it fails when you jump to the top of the next
column (I don't see code that does that either), perhaps the
Selection_Change is getting fired again. If so, use

Application.EnableEvents = False ' prevent re-firing
change the selection
Applicationi.EnableEvents = True
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Thanks Earl,

I could solve my problems, except one thing:

When I'm on row 45, I jump to the next column row 5, so the user can put
in the next column of data. If I do this, the picture is not loaded.
Even when I force the Set_Picture procedure in the Workbook_SheetChange
event, I appears for an instant and then disappears.

Thanks
Jos Vens

I use the event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

SET_Picture (Target.Row)

End Function

and the procedure

Function SET_Picture(vRow As Long, Optional vForce)

Dim vFile As String, vFolder As String, vExt As String

If (IsMissing(vForce)) Then
vForce = False
End If

If (Libra_FrmFoto.Visible) Or CBool(vForce) Then

If (vRow 4) And (vRow < 45) Then

vFolder = GET_pPath("Data", spParameters.[gCampus]) & "Foto\" &
spParameters.[gKlas] & "\"
If (HFS_Exist(vFolder)) Then

vFile = Dir(vFolder)
vExt = IIf(vFile = "", "", Right(vFile, 4))

Else

HFS_CreateDir vFolder
vExt = ""

End If

If (vExt = "") Then

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

Else

vFile = vFolder & Format(vRow - 4, "00") & vExt

Libra_FrmFoto.Caption = ActiveSheet.Cells(vRow, 2)
Libra_FrmFoto.vFoto.Picture = LoadPicture(vFile)
Libra_FrmFoto.Repaint

End If

Else

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

End If

End If

End Function





"Earl Kiosterud" schreef in bericht
...
Jos,

I set up UserForm1. I used the Image tool in the Toolbox, and made
Image1. I used to following code to show a modeless UserForm:

UserForm1.Show xlModeless
UserForm1.Image1.PictureSizeMode = fmPictureSizeModeZoom

To put focus back on the sheet, either or both of these:
'Sheets("Sheet1").Activate
'Sheets("Sheet1").Range("A2").Select

Then to change the picture, at any time:

PictureName = "C:\C5.jpg"
UserForm1.Image1.Picture = LoadPicture(PictureName)

It didn't deform the picture.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Hi,

I have a sheet with the names of my students. I let the user see a
picture of them on a modeless window (so cells can still be modified).
To see the form with the picture, a button must be pressed on the
toolbar.

Q1: when the form is displayed, how can I give back focus on the sheet
(without clicking on it)?
Q2: how can I prevent that the picture is stretched out (misformed) when
it is too small, but is always shrinked when it is too big (so you see
the whole picture without deformation). When I use a picture-object, I
cannot shrink, so I used a button (with picture), but the I get a
deformation.

Can you help me?
Thanks
Jos Vens

Hi Earl,

I found the solution!

The problem was the precedence of events:

First is executed
Private Sub Workbook_SheetChange(ByVal vSheet As Object, ByVal vCell As
Excel.Range)

Afterwards
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

In the SheetChange-event, I jumped to row 5, but for the
SheetSelectionChange, the activecell.row was still 45, so no picture was
shown. I force now the row with a global variable which holds the
active-jumprow (for me this is 5). In the picture settting, I use this row
in stead of the activerow (which still is 45).

thanks for your help!!!! (but I think this might be a bug in Excel?)

Jos


"Earl Kiosterud" schreef in bericht
...
Jos,

I haven't looked at your code in much detail -- I don't know what some of
the stuff is. But since it fails when you jump to the top of the next
column (I don't see code that does that either), perhaps the
Selection_Change is getting fired again. If so, use

Application.EnableEvents = False ' prevent re-firing
change the selection
Applicationi.EnableEvents = True
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Thanks Earl,

I could solve my problems, except one thing:

When I'm on row 45, I jump to the next column row 5, so the user can put
in the next column of data. If I do this, the picture is not loaded.
Even when I force the Set_Picture procedure in the Workbook_SheetChange
event, I appears for an instant and then disappears.

Thanks
Jos Vens

I use the event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

SET_Picture (Target.Row)

End Function

and the procedure

Function SET_Picture(vRow As Long, Optional vForce)

Dim vFile As String, vFolder As String, vExt As String

If (IsMissing(vForce)) Then
vForce = False
End If

If (Libra_FrmFoto.Visible) Or CBool(vForce) Then

If (vRow 4) And (vRow < 45) Then

vFolder = GET_pPath("Data", spParameters.[gCampus]) & "Foto\" &
spParameters.[gKlas] & "\"
If (HFS_Exist(vFolder)) Then

vFile = Dir(vFolder)
vExt = IIf(vFile = "", "", Right(vFile, 4))

Else

HFS_CreateDir vFolder
vExt = ""

End If

If (vExt = "") Then

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

Else

vFile = vFolder & Format(vRow - 4, "00") & vExt

Libra_FrmFoto.Caption = ActiveSheet.Cells(vRow, 2)
Libra_FrmFoto.vFoto.Picture = LoadPicture(vFile)
Libra_FrmFoto.Repaint

End If

Else

Libra_FrmFoto.Caption = ""
Libra_FrmFoto.vFoto.Picture = LoadPicture("")

End If

End If

End Function





"Earl Kiosterud" schreef in bericht
...
Jos,

I set up UserForm1. I used the Image tool in the Toolbox, and made
Image1. I used to following code to show a modeless UserForm:

UserForm1.Show xlModeless
UserForm1.Image1.PictureSizeMode = fmPictureSizeModeZoom

To put focus back on the sheet, either or both of these:
'Sheets("Sheet1").Activate
'Sheets("Sheet1").Range("A2").Select

Then to change the picture, at any time:

PictureName = "C:\C5.jpg"
UserForm1.Image1.Picture = LoadPicture(PictureName)

It didn't deform the picture.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jos Vens" wrote in message
...
Hi,

I have a sheet with the names of my students. I let the user see a
picture of them on a modeless window (so cells can still be modified).
To see the form with the picture, a button must be pressed on the
toolbar.

Q1: when the form is displayed, how can I give back focus on the sheet
(without clicking on it)?
Q2: how can I prevent that the picture is stretched out (misformed)
when it is too small, but is always shrinked when it is too big (so you
see the whole picture without deformation). When I use a
picture-object, I cannot shrink, so I used a button (with picture), but
the I get a deformation.

Can you help me?
Thanks
Jos Vens