Pictures on modeless windows

[Jos Vens]

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

[Earl Kiosterud]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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" <[email protected]> schreef in bericht
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[Earl Kiosterud]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> schreef in bericht
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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 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" <[email protected]> schreef in bericht
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> schreef in bericht
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>