Need Help - save sheet then hide when....
 

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

Need Help - save sheet then hide when....
 

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

Need Help - save sheet then hide when....
 

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

Need Help - save sheet then hide when....
 

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

Need Help - save sheet then hide when....
 

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

Need Help - save sheet then hide when....
 

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