Creating a Macro? V2
 

I don't know how to reply to a post and get it to come back to the top of the
list so I am starting a new thread under the same heading. Sorry everyone.
Pete and Gord responded to my original thread but unfortunately I was not
able to get their advice to work. My original question related to a four page
survey. 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 when the
submit button is activated so that when the recipients open the workbook they
see page one first and not page four.

The VBE I am using is below and is one of Debras (I just wish I knew how to
write this stuff, but I am learning a little bit, obviously not enough though)

Sub Mail_workbook_1()

Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation


Exit Sub
End If
End If

On Error Resume Next
wb.SendMail Array("email.address"), _
"Exit Survey"
On Error GoTo 0
End Sub

Sorry this is so long.

Cheers

Creating a Macro? V2
 

Hi, Lynda,

I read your mail again.

Open your workbook and hit Alt-F11 to go into the VB editor.

In the VBA Project window (top left), double click on "ThisWorkbook" and
enter the following:

Private Sub Workbook_Open()
sheets("Sheet 1").activate
End Sub

then do Alt-F11 again and save your workbook.

Sheet 1 will be made the active workbook whenever you open the workbook
subsequently.

Regards

Pete




"Lynda" wrote:

I don't know how to reply to a post and get it to come back to the top of the
list so I am starting a new thread under the same heading. Sorry everyone.
Pete and Gord responded to my original thread but unfortunately I was not
able to get their advice to work. My original question related to a four page
survey. 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 when the
submit button is activated so that when the recipients open the workbook they
see page one first and not page four.

The VBE I am using is below and is one of Debras (I just wish I knew how to
write this stuff, but I am learning a little bit, obviously not enough though)

Sub Mail_workbook_1()

Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation


Exit Sub
End If
End If

On Error Resume Next
wb.SendMail Array("email.address"), _
"Exit Survey"
On Error GoTo 0
End Sub

Sorry this is so long.

Cheers

Creating a Macro? V2
 

Hi Lynda,

(a different Pete this time). To see your previous posts click on this
link:

http://groups.google.com/groups/sear...XB3g&scoring=d

You might want to add it to your Favourites, as it will update when
you add more posts.

Hope this helps.

Pete

On Jul 22, 10:25*am, Lynda wrote:
I don't know how to reply to a post and get it to come back to the top of the
list so I am starting a new thread under the same heading. Sorry everyone..
Pete and Gord responded to my original thread but unfortunately I was not
able to get their advice to work. My original question related to a four page
survey.

<snipped

Cheers

Creating a Macro? V2
 

You guys are great, thank you so much for your patience. Pete R, once again I
will try your suggestion when I return to work tomorrow but I think I
understand this time.
Pete_UK, I will add that to my favorites. Thank you both so much. Pete R I
will be back tomorrow to let you know how I got on.

Cheers

Creating a Macro? V2
 

You're welcome.

Pete

On Jul 22, 12:07*pm, Lynda wrote:
You guys are great, thank you so much for your patience. Pete R, once again I
will try your suggestion when I return to work tomorrow but I think I
understand this time.
Pete_UK, I will add that to my favorites. Thank you both so much. Pete R I
will be back tomorrow to let you know how I got on.

Cheers

Creating a Macro? V2
 

Lynda,

No problem - the golden rule has to be - take a copy and make your code
changes to that to test that all works OK. You might or might not know that
there is a "programming" sub option avail from the "Excel" option on the left
hand side of this screen.

When a suggestion has helped you, you can click the "Was this post helpful
to you?" button at the bottom right hand corner of the screen. This puts a
green tick alongside the question (and increases the "helpfulness" rating of
the person who supplied the answer - this is how they achieve the bronze,
silver and gold icons you see alongside some people's names)

I find it useful to mooch through the green ticked questions every now and
then - as they're questions with answers, you generally learn something new
with each one!

Have fun!

Pete

"Lynda" wrote:

You guys are great, thank you so much for your patience. Pete R, once again I
will try your suggestion when I return to work tomorrow but I think I
understand this time.
Pete_UK, I will add that to my favorites. Thank you both so much. Pete R I
will be back tomorrow to let you know how I got on.

Cheers