Creating a Macro?

[Lynda]

I’ve created a four page survey in a workbook. On page four I have a submit
button which returns the survey by email to two people in our HR area. While
one is very happy with my effort, thanks to a number of you people, the other
one is annoyed that the survey comes back to him showing page four and not
page one. While I have explained to him that all he needs to do is click on
the page one tab he insists he wants the survey to come back with page one at
the front. Can anyone tell me how I might be able to put a macro or something
on the submit button so that when the submit button is pressed it will
activate the macro to return the survey to page one as well as still email
the survey. Hope this makes sense.
Thank you in advance.

Cheers

 

[Pete Rooney]

Good afternoon,

You'd need to find the macro that was attached to the button first.

You can do this by holding down [Control] and clicking on the button.

The move the mouse pointer to the heavy border that is now displayed around
the button and right click. From the shortcut menu displayed, click on
"Assign Macro" - the assigned macro will be displayed in a dialog box.

Press [Escape] then hold down [Alt] and press F11.

This will take you into the Visual basic editor.

Look for a macro with the name you just noted - you can use [Control]+f to
find it - it will be preceded by the work "Sub", so, you're looking for
something like "Sub SubmitForm()"

The, you'd need to insert the following line into the macro:

Sheets("Sheet3").Move Before:=Sheets(1)

where "Sheet 3" is the tab name of the worksheet you want to move.

The issue is, where precisely in the macro you insert the command - the
macro may need to do things with the worksheet where it is before the send is
actioned!

You may also wish to think about moving the worksheet back again before you
save the workbook, otherwise, you'll antagonise the first person!

And perhaps speak to the person who wrote the macro before you start hacking
their code!

And finally, save yourself a copy of the workbook somewhere safe before you
do ANYTHING!

Regards

Pete


Good luck

 

[Lynda]

Good afternoon,

You'd need to find the macro that was attached to the button first.

You can do this by holding down [Control] and clicking on the button.

The move the mouse pointer to the heavy border that is now displayed around
the button and right click. From the shortcut menu displayed, click on
"Assign Macro" - the assigned macro will be displayed in a dialog box.

Press [Escape] then hold down [Alt] and press F11.

This will take you into the Visual basic editor.

Look for a macro with the name you just noted - you can use [Control]+f to
find it - it will be preceded by the work "Sub", so, you're looking for
something like "Sub SubmitForm()"

The, you'd need to insert the following line into the macro:

Sheets("Sheet3").Move Before:=Sheets(1)

where "Sheet 3" is the tab name of the worksheet you want to move.

The issue is, where precisely in the macro you insert the command - the
macro may need to do things with the worksheet where it is before the send is
actioned!

You may also wish to think about moving the worksheet back again before you
save the workbook, otherwise, you'll antagonise the first person!

And perhaps speak to the person who wrote the macro before you start hacking
their code!

And finally, save yourself a copy of the workbook somewhere safe before you
do ANYTHING!

Regards

Pete


Good luck

I’ve created a four page survey in a workbook. On page four I have a submit
button which returns the survey by email to two people in our HR area. While
one is very happy with my effort, thanks to a number of you people, the other
one is annoyed that the survey comes back to him showing page four and not
page one. While I have explained to him that all he needs to do is click on
the page one tab he insists he wants the survey to come back with page one at
the front. Can anyone tell me how I might be able to put a macro or something
on the submit button so that when the submit button is pressed it will
activate the macro to return the survey to page one as well as still email
the survey. Hope this makes sense.
Thank you in advance.

Cheers

Thank you Pete, as it is just after midnight here I will need to try this at
work tomorrow. In the mean time I am not sure if I have misunderstood what
you have written but I do not actually want to move a worksheet .The survey
is posted on our intranet at work for people exiting our employment. Once
they have completed the survey they activate the submit button returning the
survey to our HR people.
The survey contains four pages (worksheets) with the submit button on the
last page (page four). When the submit button is activated it emails the
workbook. When the recipients opens the workbook it opens at page four
because that was the last page open and the one with the submit button on it.
I want the macro to basically return the workbook to page one so that when
the recipients open the workbook they see page one first and not page four.

Cheers

 

[Gord Dibben]

Post your macro code, but at the start of the Submit macro code you should
be able to add a line like

Sheets("Sheet1").Activate


Gord Dibben MS Excel MVP