Excel - image follows the scrollbar
tom
-
tom -
tom -
Hello,
Let me explain, I am currently creating a program using VBA.
In an Excel sheet, I have inserted images and assigned a macro to each of them.
I want the user to launch the macros using these images acting as buttons in the spreadsheet.
However, the problem is that if the user scrolls through the Excel sheet (e.g., scrolling right or down), the images remain fixed on the spreadsheet, meaning they appear to move relative to the user. I would like them to be fixed on the user's screen, meaning they do not move from the user's perspective.
Thank you to anyone who understands my problem!
Let me explain, I am currently creating a program using VBA.
In an Excel sheet, I have inserted images and assigned a macro to each of them.
I want the user to launch the macros using these images acting as buttons in the spreadsheet.
However, the problem is that if the user scrolls through the Excel sheet (e.g., scrolling right or down), the images remain fixed on the spreadsheet, meaning they appear to move relative to the user. I would like them to be fixed on the user's screen, meaning they do not move from the user's perspective.
Thank you to anyone who understands my problem!
4 réponses
I could also advise you the following macro:
_Sub resizePic()
____Dim rngCanSee As Range
____Set ecran = ActiveWindow.VisibleRange
____With ActiveSheet.Pictures(1)
_______' Here you can define the desired position.
_______' You can use ecran.VisibleRange.Left and ecran.VisibleRange.Top
_______.Left = ecran.Left + 20
_______.Top = ecran.Top + 20
____End With
_End Sub
This macro repositions the image to the desired location each time a new window is selected if you call it in this event:
_Private Sub Worksheet_SelectionChange(ByVal Target As Range)
____Call resizePic
_End Sub
However, it is not repositioned if the scrolls are moved using the mouse until after clicking on the screen.
Yassine,
_Sub resizePic()
____Dim rngCanSee As Range
____Set ecran = ActiveWindow.VisibleRange
____With ActiveSheet.Pictures(1)
_______' Here you can define the desired position.
_______' You can use ecran.VisibleRange.Left and ecran.VisibleRange.Top
_______.Left = ecran.Left + 20
_______.Top = ecran.Top + 20
____End With
_End Sub
This macro repositions the image to the desired location each time a new window is selected if you call it in this event:
_Private Sub Worksheet_SelectionChange(ByVal Target As Range)
____Call resizePic
_End Sub
However, it is not repositioned if the scrolls are moved using the mouse until after clicking on the screen.
Yassine,
Hello,
We have the possibility to position ourselves relative to the bottom of the page and account for resizing. It's just a matter of knowing how the commands should reposition when there is a change in size.
The following is an example that allows you to position a shape 20 pixels from the bottom and in the middle of the screen:
.... Sub resizePic()
........ Set screen = ActiveWindow.VisibleRange
........ With ActiveSheet.Shapes(1)
............ .Left = screen.Left + screen.Width / 2 - .Width / 2
............ .Top = screen.Top + screen.Height - .Height - 20
........ End With
..... End Sub
I won't comment on the macro; I think it's clear enough.
In general, buttons are positioned at the bottom left. So even if the screen is reduced, we can still fit the images to the left.
Otherwise, macros for resizing windows can be used. Here is an example:
Sub resizeWin()
.... Set screen = ActiveWindow.VisibleRange
.... With ActiveSheet.Shapes(1)
........ If ActiveWindow.WindowState = xlNormal Then
............ If screen.Width < .Width Then ActiveWindow.Width = ActiveWindow.Width - screen.Width + .Width
............ If screen.Height < .Width Then ActiveWindow.Height = ActiveWindow.Height - screen.Height + .Height
........ End If
.... End With
End Sub
What you put in the event:
Private Sub Workbook_WindowResize(ByVal Wn As Window)
.... resizeWin
End Sub
I think you can still submit the exercise to your teacher ;-)
We have the possibility to position ourselves relative to the bottom of the page and account for resizing. It's just a matter of knowing how the commands should reposition when there is a change in size.
The following is an example that allows you to position a shape 20 pixels from the bottom and in the middle of the screen:
.... Sub resizePic()
........ Set screen = ActiveWindow.VisibleRange
........ With ActiveSheet.Shapes(1)
............ .Left = screen.Left + screen.Width / 2 - .Width / 2
............ .Top = screen.Top + screen.Height - .Height - 20
........ End With
..... End Sub
I won't comment on the macro; I think it's clear enough.
In general, buttons are positioned at the bottom left. So even if the screen is reduced, we can still fit the images to the left.
Otherwise, macros for resizing windows can be used. Here is an example:
Sub resizeWin()
.... Set screen = ActiveWindow.VisibleRange
.... With ActiveSheet.Shapes(1)
........ If ActiveWindow.WindowState = xlNormal Then
............ If screen.Width < .Width Then ActiveWindow.Width = ActiveWindow.Width - screen.Width + .Width
............ If screen.Height < .Width Then ActiveWindow.Height = ActiveWindow.Height - screen.Height + .Height
........ End If
.... End With
End Sub
What you put in the event:
Private Sub Workbook_WindowResize(ByVal Wn As Window)
.... resizeWin
End Sub
I think you can still submit the exercise to your teacher ;-)
Hello,
Try to split the window. This will divide it into several windows, one of which is movable (where the button photos will be placed). Then press the freeze button.
This method, personally, doesn't satisfy me because it limits the display. But it can help with this matter.
Best regards,
Yassine
Try to split the window. This will divide it into several windows, one of which is movable (where the button photos will be placed). Then press the freeze button.
This method, personally, doesn't satisfy me because it limits the display. But it can help with this matter.
Best regards,
Yassine
Good evening,
After numerous tests, I've come to a conclusion:
I want to place my images via the macro at the bottom of the page.
The problem is, depending on the size of the computer screen or the zoom in Excel, the previous macro won't always position the images at the bottom.
On my PC, they are at the bottom, but on another one, you can’t even see them because they are too low...
Not practical for submitting the project to my teacher... I'm not going to ask him for the size of his screen!
ActiveWindow.VisibleRange.Bottom doesn't work! What should I do then?
After numerous tests, I've come to a conclusion:
I want to place my images via the macro at the bottom of the page.
The problem is, depending on the size of the computer screen or the zoom in Excel, the previous macro won't always position the images at the bottom.
On my PC, they are at the bottom, but on another one, you can’t even see them because they are too low...
Not practical for submitting the project to my teacher... I'm not going to ask him for the size of his screen!
ActiveWindow.VisibleRange.Bottom doesn't work! What should I do then?
However, I'm not quite sure what the private sub is for...
There should be one that triggers resizePic as soon as the user scrolls down in their Excel sheet (for example, when they go further down the sheet).
- you can place the resizePic macro in a module (Module1 for example)
- the "private sub," on the other hand, is created automatically when you go to the code for Sheet 1 ("Feuil1") and switch the value of the ComboBox (in the top left) from "General" to "Worksheet."
It's working well! Good job, you have to know the syntax!!
However, indeed, if we move the scrolls with the mouse, it doesn't work anymore.
But it's already quite nice there! Thank you.