in Excel how do you set a workbook to only open on the first work.

M

Mark_J

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.
 
G

Gordon

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.
 
J

jenkinspat

Mark_J said:
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.
Click to expand...

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
 
M

Mark

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

Kind Regards...Mark
 
M

Mark

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
 
D

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.
 
D

Dorianne

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
 
R

Ron de Bruin

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 said:
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
Click to expand...
 
Top