Can you make pictures jump to the following page

[dpaton05]

I have a spreadsheet with lines that get inserted into a table. At the bottom I have signatures that need to be pushed down if the new lines added to the table reach them. Is there a way to make the signatures remain stationary until the lines meet them and then get pushed to the bottom of the following page? The signatures are in an active x control.

 

[dpaton05]

.
This macro will position a PNG image in the cell indicated.

For testing purposes the cell is presently set at F46.

Sub CenterInCell()


Dim myImage As Shape
Dim cellLocation As Range


Set myImage = ActiveSheet.Shapes("Picture1")
Set cellLocation = ActiveSheet.Range("F46")


myImage.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2)
myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2)


End Sub

The code is generic in nature. The PNG image does not need to be placed inside an ActiveX Image Control.
The PNG image can be pasted directly to the worksheet.

The only thing required now is to know which cell the PNG image should be placed in .... and determine a
convenient method of activating the macro.

For example: is there any reason why the PNG image cannot be located at the bottom of all sheets in the workbook rather than
having to move it from one sheet to another ?

If the image must be moved from one sheet to another, would the presence of a command button at the bottom of each worksheet
present a problem ? Each button would be connected to the macro. The user would only need to click the button to create the PNG signature
image at the bottom of the sheet.

If any of the sheets need to be printed with the signature, the command button should not appear in the printing.

Let me know your thoughts.

• To avoid additional files being required to activate the spreadsheet, I thought that adding it to an active x control would mean that it is embedded so not requiring an additional file to copy it from.
• I want the png to be pasted at the end of the document for several reasons. 1. It is an official document and having it just printed once at the end looks more professional. 2. The signature is rather large and having on every page would take up space that could otherwise be occupied by other things, thus reducing the room required to present the information contained therein.
• The idea of having a command button at the bottom of each page sounds interesting. Having a command button on each page would not present any problems, as I could set the printable property to false so it won't be printed. I am guessing that when the button is clicked, the document will be finished and you click the button on the last page and it will paste the signature at the bottom. But what happens if there is no room for the signature at the bottom of the page? Is it possible to make it put the signature of the following page if it is out of room?

 

[Logit]

.
Paste into a Routine Module :

Option Explicit


Sub ShowPic()


Dim myImage As Shape
Dim cellLocation As Range


Set myImage = ActiveSheet.Shapes("Picture 1")   '<--- Use a PNG image inserted directly on the sheet.
                                                '     Named Picture 1  (space before 1)
Set cellLocation = ActiveSheet.Range("F43")     '<--- sets location of image on active sheet
ActiveSheet.Shapes("Picture 1").Visible = True


myImage.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2)
myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2)


End Sub




Sub HidePic()


Dim myImage As Shape
Set myImage = ActiveSheet.Shapes("Picture 1")


 ActiveSheet.Shapes("Picture 1").Visible = False
End Sub

- Use two command buttons on Sheet1. One captioned SHOW the other captioned HIDE.
- Insert Image / your PNG file directly to the worksheet.
- Edit the cell reference in the code where the image should be displayed.
-Save the workbook after testing the command buttons.

Now copy the buttons and the image then paste to the next sheet. Repeat this process for as many sheets as you'll need.
No need to assign these additional buttons to a macro if you have copied and pasted ... the macros will remain the same.

Download workbook : https://www.amazon.com/clouddrive/share/flNTkJFTag9LQiubgde3LfLGdj2pNqGTzVJzekeF4pn

 

[Hjemmet]

is it possibel to get this picture Being show when there has NOT been any activety on sheets Just like Screensaver


i have been looking at your files and change picture as i like to see

and it works just fine

 

[Logit]

.

is it possibel to get this picture Being show when there has NOT been any activety on sheets Just like Screensaver

Are you saying you want to use the PNG file as a screensaver ?

 

[Hjemmet]

���� yeah but not like the version ther is with Windows
my version shoild be in the Excel file on specific sheet’s
when no activty in sheets then start showing picture in
5 or 6 cell’s
exampel.
time no activity then show picture in celle B10
and time showing picture and then Fadeout and no picture
in cell B10.
then few sec between the picture has been showen to next Picture
showen in Celle G20 as same way as picture in B10
and so on to picture 6

when activity to sheet when this function is running then reset timer
hope that was a better descripe of what i wish

 

[dpaton05]

.
Paste into a Routine Module :

Option Explicit


Sub ShowPic()


Dim myImage As Shape
Dim cellLocation As Range


Set myImage = ActiveSheet.Shapes("Picture 1")   '<--- Use a PNG image inserted directly on the sheet.
                                                '     Named Picture 1  (space before 1)
Set cellLocation = ActiveSheet.Range("F43")     '<--- sets location of image on active sheet
ActiveSheet.Shapes("Picture 1").Visible = True


myImage.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2)
myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2)


End Sub




Sub HidePic()


Dim myImage As Shape
Set myImage = ActiveSheet.Shapes("Picture 1")


 ActiveSheet.Shapes("Picture 1").Visible = False
End Sub

What do you mean by paste into a routine module? If I put the Option Explicit in to the code first, it gives me an error.

 

[Logit]

.
If it works for you without using OPTION EXPLICIT, that's ok. Leave it out.

A "Routine Module" referes to the insertion of a Module which is selected from the menu bar in the VBE.