In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

Mark_J wrote:
|| In Microsoft Excel how do you open a workbook to ensure it only
|| opens one a specified worksheet, no matter what spreadsheet you last
|| saved the workbook in.

I've never heard that that's possible.

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Hi!

It's possible but requires VBA code.

Biff

-----Original Message-----
Mark_J wrote:
|| In Microsoft Excel how do you open a workbook to
ensure it only
|| opens one a specified worksheet, no matter what
spreadsheet you last
|| saved the workbook in.

I've never heard that that's possible.

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk


.

Mark_J Wrote:
In Microsoft Excel how do you open a workbook to ensure it only opens
one a
specified worksheet, no matter what spreadsheet you last saved the
workbook
in.

It is VARY do able. In VB go to your project and click the
ThisWorkbook

Paste the following-----

Option Explicit

Private Sub Workbook_Open()
Sheets("ENTER YOUR WORKSHEET NAME HERE").Select
End Sub

Your workbook will open at the sheet you enetered evry time regardless
when it was saved on.
Good Luck
Pat


--
jenkinspat

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson

Thanks worked a treat. Any ideas on how I can stop access to the options in
a protected worksheet/workbook?

Kind Regards...Mark

"jenkinspat" wrote:


Mark_J Wrote:
In Microsoft Excel how do you open a workbook to ensure it only opens
one a
specified worksheet, no matter what spreadsheet you last saved the
workbook
in.

It is VARY do able. In VB go to your project and click the
ThisWorkbook

Paste the following-----

Option Explicit

Private Sub Workbook_Open()
Sheets("ENTER YOUR WORKSHEET NAME HERE").Select
End Sub

Your workbook will open at the sheet you enetered evry time regardless
when it was saved on.
Good Luck
Pat


--
jenkinspat

Thanks Dave work a treat. Any ideas on how I can stop people accessing the
options in a protected worksheet or workbook?

Kind Regards...Mark

"Dave Peterson" wrote:

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson

You can stop the users from getting to Tools|options with code like:

Option Explicit
Private Sub Workbook_Activate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, False)
End Sub
Private Sub Workbook_Deactivate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, True)
End Sub

Sub EnableDisableByID(myID As Long, TurnOn As Boolean)
Dim myCommandBar As CommandBar
Dim myCtrl As CommandBarControl

For Each myCommandBar In Application.CommandBars
Set myCtrl = myCommandBar.FindControl(ID:=myID, recursive:=True)
If myCtrl Is Nothing Then
'do nothing
Else
myCtrl.Enabled = TurnOn
End If
Next myCommandBar
End Sub

You can plop all 3 subs right into the ThisWorkbook module. (The last one could
go into a general module, instead.)

But personally, I think won't stop any user who can find the newsgroups.



Mark wrote:

Thanks Dave work a treat. Any ideas on how I can stop people accessing the
options in a protected worksheet or workbook?

Kind Regards...Mark

"Dave Peterson" wrote:

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson


--

Dave Peterson

Dave, This code works great, but now I've disabled my Options and cannot get
it enabled again. Simply deleting the code from VBA This Workbook did not
work. Please advise.

Thanks

"Dave Peterson" wrote:

You can stop the users from getting to Tools|options with code like:

Option Explicit
Private Sub Workbook_Activate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, False)
End Sub
Private Sub Workbook_Deactivate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, True)
End Sub

Sub EnableDisableByID(myID As Long, TurnOn As Boolean)
Dim myCommandBar As CommandBar
Dim myCtrl As CommandBarControl

For Each myCommandBar In Application.CommandBars
Set myCtrl = myCommandBar.FindControl(ID:=myID, recursive:=True)
If myCtrl Is Nothing Then
'do nothing
Else
myCtrl.Enabled = TurnOn
End If
Next myCommandBar
End Sub

You can plop all 3 subs right into the ThisWorkbook module. (The last one could
go into a general module, instead.)

But personally, I think won't stop any user who can find the newsgroups.



Mark wrote:

Thanks Dave work a treat. Any ideas on how I can stop people accessing the
options in a protected worksheet or workbook?

Kind Regards...Mark

"Dave Peterson" wrote:

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson


--

Dave Peterson

Hi Dorianne

With Dave's code it Enable 522 when you close or go to another workbook.
Insert the code in a new workbook and test it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dorianne" wrote in message ...
Dave, This code works great, but now I've disabled my Options and cannot get
it enabled again. Simply deleting the code from VBA This Workbook did not
work. Please advise.

Thanks

"Dave Peterson" wrote:

You can stop the users from getting to Tools|options with code like:

Option Explicit
Private Sub Workbook_Activate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, False)
End Sub
Private Sub Workbook_Deactivate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, True)
End Sub

Sub EnableDisableByID(myID As Long, TurnOn As Boolean)
Dim myCommandBar As CommandBar
Dim myCtrl As CommandBarControl

For Each myCommandBar In Application.CommandBars
Set myCtrl = myCommandBar.FindControl(ID:=myID, recursive:=True)
If myCtrl Is Nothing Then
'do nothing
Else
myCtrl.Enabled = TurnOn
End If
Next myCommandBar
End Sub

You can plop all 3 subs right into the ThisWorkbook module. (The last one could
go into a general module, instead.)

But personally, I think won't stop any user who can find the newsgroups.



Mark wrote:

Thanks Dave work a treat. Any ideas on how I can stop people accessing the
options in a protected worksheet or workbook?

Kind Regards...Mark

"Dave Peterson" wrote:

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson


--

Dave Peterson