macro that runs on startup

ajtilley

New Member
Joined
Jul 19, 2010
Messages
24
Hi

Is there vba code that i can put in an excel 2007 workbook so that when it run so that if macros are enabled or turned on it moves a shape from one position to another.

Thus if macros were enabled the shape would be at the bottom of the page or on a hidden sheet, but if the person enabled macros or they were enabled automaticaly when the sheet was run the shape would instantly move to the front page where it could be used?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You can use the Workbook_Open event which has to go in the ThisWorkbook module. In the VBE in the Project window double click ThisWorkbook and paste in

Code:
Private Sub Workbook_Open()
'
'code to move shape
'
End Sub
 

ajtilley

New Member
Joined
Jul 19, 2010
Messages
24
Thankl you, i thought so, does anyone know what code i should use to move a shape from one position to another?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I think I would just unhide it

Code:
Private Sub Workbook_Open()
Sheets("Front Page").Shapes("Rectangle 1").Visible = True
End Sub
 

ajtilley

New Member
Joined
Jul 19, 2010
Messages
24

ADVERTISEMENT

Excellent, thats much better

I assume if i add a Before-close option i can get it to dissapear when the document closes as well. Ready for the next time someone loads the document up.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Yes, and you'll need to save as well

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Front Page").Shapes("Rectangle 1").Visible = False
Me.Save
End Sub
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
I do the same thing as sometimes the file will be saved in a location that is not trusted and run by folks not familar with macros. I have a callout box pointing to the options button telling them to slect it and enable macros. If/when the macros are enabled, the code hides rather than moves it.

Code:
Private Sub Workbook_Open()
    Worksheets("data").Shapes("Rectangular Callout 1").Visible = False
End Sub

Private Sub Workbook_beforeClose(cancel As Boolean)
    Worksheets("data").Shapes("Rectangular Callout 1").Visible = True
End Sub

Like you said, just make sure you make it visible again at closing.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,915
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top