Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Macro?
Ive 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Macro?
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 Befo=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" wrote: Ive 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Macro?
"Pete Rooney" wrote: 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 Befo=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" wrote: Ive 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Macro | Excel Discussion (Misc queries) | |||
Macro Creating | New Users to Excel | |||
Creating a macro | New Users to Excel | |||
Need Help Creating A Macro | Excel Worksheet Functions | |||
Creating a macro | Excel Worksheet Functions |