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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
Thankl you, i thought so, does anyone know what code i should use to move a shape from one position to another?
 
Upvote 0
I think I would just unhide it

Code:
Private Sub Workbook_Open()
Sheets("Front Page").Shapes("Rectangle 1").Visible = True
End Sub
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top