I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that when the
user clicks on the button, it unhides a worksheet and places the cursor in a
specific cell for that worksheet to allow the user to begin entering required
data. The macro I created for the button works so far to select the "Routes"
worksheet.

When the user is finish entering data and selects the "Steps" worksheet, I
want the Routes worksheet to be saved and then hidden. Can someone help me
with the syntax to accomplish this event?

TIA

Bearing in mind that you can't save an individual worksheet - you have to
save the whole workbook - place this code in the ThisWorkbook code sheet in
your VBA project. It traps the event where a sheet is deactivated.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Routes" Then
Sh.Visible = False
ActiveWorkbook.Save
End If
End Sub

Regards
Rowan

"Jan" wrote:

I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that when the
user clicks on the button, it unhides a worksheet and places the cursor in a
specific cell for that worksheet to allow the user to begin entering required
data. The macro I created for the button works so far to select the "Routes"
worksheet.

When the user is finish entering data and selects the "Steps" worksheet, I
want the Routes worksheet to be saved and then hidden. Can someone help me
with the syntax to accomplish this event?

TIA

Why not just use

--
Private Sub WorkSheet_Deactivate()
Me.Visible = xlHidden
ActiveWorkbook.Save
End Sub

as a worksheet event?


HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" wrote in message
...
Bearing in mind that you can't save an individual worksheet - you have to
save the whole workbook - place this code in the ThisWorkbook code sheet
in
your VBA project. It traps the event where a sheet is deactivated.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Routes" Then
Sh.Visible = False
ActiveWorkbook.Save
End If
End Sub

Regards
Rowan

"Jan" wrote:

I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that when
the
user clicks on the button, it unhides a worksheet and places the cursor
in a
specific cell for that worksheet to allow the user to begin entering
required
data. The macro I created for the button works so far to select the
"Routes"
worksheet.

When the user is finish entering data and selects the "Steps" worksheet,
I
want the Routes worksheet to be saved and then hidden. Can someone help
me
with the syntax to accomplish this event?

TIA

I've entered the code as provided in the This workbook, but nothing happens.
Unlike the command buttons on the Steps worksheet that have a specific macro
to make then work; what makes this code process when the user leaves the
"Routes" worksheet and clicks on the worksheet tab to return to the "Steps"
worksheet. Sorry, but I don't know this stuff very well.

"Rowan" wrote:

Bearing in mind that you can't save an individual worksheet - you have to
save the whole workbook - place this code in the ThisWorkbook code sheet in
your VBA project. It traps the event where a sheet is deactivated.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Routes" Then
Sh.Visible = False
ActiveWorkbook.Save
End If
End Sub

Regards
Rowan

"Jan" wrote:

I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that when the
user clicks on the button, it unhides a worksheet and places the cursor in a
specific cell for that worksheet to allow the user to begin entering required
data. The macro I created for the button works so far to select the "Routes"
worksheet.

When the user is finish entering data and selects the "Steps" worksheet, I
want the Routes worksheet to be saved and then hidden. Can someone help me
with the syntax to accomplish this event?

TIA

This worked once I discovered where exactly to place the code to get it to
execute. I needed to right click on the "Routes" tab, select View code and
enter the syntax.

Thank you.



"Bob Phillips" wrote:

Why not just use

--
Private Sub WorkSheet_Deactivate()
Me.Visible = xlHidden
ActiveWorkbook.Save
End Sub

as a worksheet event?


HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" wrote in message
...
Bearing in mind that you can't save an individual worksheet - you have to
save the whole workbook - place this code in the ThisWorkbook code sheet
in
your VBA project. It traps the event where a sheet is deactivated.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Routes" Then
Sh.Visible = False
ActiveWorkbook.Save
End If
End Sub

Regards
Rowan

"Jan" wrote:

I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that when
the
user clicks on the button, it unhides a worksheet and places the cursor
in a
specific cell for that worksheet to allow the user to begin entering
required
data. The macro I created for the button works so far to select the
"Routes"
worksheet.

When the user is finish entering data and selects the "Steps" worksheet,
I
want the Routes worksheet to be saved and then hidden. Can someone help
me
with the syntax to accomplish this event?

TIA

Sorry, should have told you that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jan" wrote in message
...
This worked once I discovered where exactly to place the code to get it to
execute. I needed to right click on the "Routes" tab, select View code
and
enter the syntax.

Thank you.



"Bob Phillips" wrote:

Why not just use

--
Private Sub WorkSheet_Deactivate()
Me.Visible = xlHidden
ActiveWorkbook.Save
End Sub

as a worksheet event?


HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" wrote in message
...
Bearing in mind that you can't save an individual worksheet - you have
to
save the whole workbook - place this code in the ThisWorkbook code
sheet
in
your VBA project. It traps the event where a sheet is deactivated.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Routes" Then
Sh.Visible = False
ActiveWorkbook.Save
End If
End Sub

Regards
Rowan

"Jan" wrote:

I am using Excel 2003 and I'm a novice with VBA.
I have 2 worksheets(for now): Steps and Routes.

I plan to have several command buttons on the Steps worksheet that
when
the
user clicks on the button, it unhides a worksheet and places the
cursor
in a
specific cell for that worksheet to allow the user to begin entering
required
data. The macro I created for the button works so far to select the
"Routes"
worksheet.

When the user is finish entering data and selects the "Steps"
worksheet,
I
want the Routes worksheet to be saved and then hidden. Can someone
help
me
with the syntax to accomplish this event?

TIA