Creating Phasing plans in excel

[lalit.sis]

Hi Chandoo,


I am impressed with you expertise in excel. Here is a not so difficult one for you:


I have been creating monthly construction phasing plans in excel using a background template and lots of freeform layers, the size of the file goes up to 15mb at time. I am sing a jpg site plan drawing and just copy across columns for next month slide.


The problem is every now and then the background jpg image tends to move due to an unwanted click, creating a kios! Is there a way we can freeze the background jpg image. I tried to do it with protection but if you protect it you just can not copy anything across and the limitation are disappointing.


I know you will come up with something brilliant.


Cheers.


Lalit.

 

[Hui]

Lalit


One option would be to use a macro to reset the location every time the sheet is activated

To do this you'll need to know where the picture should be located.


Position the picture where you want it


Copy the next subroutine to a module in VBA

Alt F11, Right click on your workbook and Insert Module

[pre]

Sub Show_Location()
With Selection
MsgBox "Top = " + Str(.Top) + vbCr + _
"Height = " + Str(.Height) + vbCr + _
"Left = " + Str(.Left) + vbCr + _
"Width = " + Str(.Width)
End With
End Sub

On your worksheet insert another shape and assign the macro Show_Location to it.

Now select your background picture and click the button with the macro assigned

It will bring up a Dialog showing you the coordinates of the location, write them down.


Next copy the following code to the worksheet object for the sheet where your picture is.

In VBA double click the worksheet name where your picture is

paste the following code

Adjust the values based on the location from the dialog box earlier


Private Sub Worksheet_Activate()
With Shapes("Picture 1") 'Change the name to suit your Picture
.Top = 100
.Height = 100
.Left = 100
.Width = 100
End With
End Sub

[/pre]
Now every time you leave the sheet and return to it it will relocate it for you

If it is accidentally resized, leave the sheet and return and it will be reset

 

[lalit.sis]

Thanks very much Hui,


I give a try a later this week.


Cheers.