Something I thought would be so simple has turned into a nightmare. I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. So, I'm going for Plan II and it has to be dummy proof for the
users. No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

Sub Workbook_Open()
Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
Workbooks("index.xls").Activate
End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
Sub ShowMySheetOnly()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
End If
Next Wks
End Sub

Thank you to any and all who offer their insight/suggestions, etc.

I thought I was following what you want but got lost somewhere along the
line.

Why not hide the requisite sheets in the 'read-only' wb's open event.
Anyway, if you want to run a macro in another workbook you can use the Run
method, eg

Dim sMacro As String
sMacro = "'FileIndex.xls'!ShowMySheetOnly"

Application.Run sMacro

There's a pair of apostrophes bracketing the filename. Normally they are not
necessary but may be required if the filename includes certain characters,
no harm to include the apostrophes even if not required.

Regards,
Peter T

"imelda1ab" wrote in message
...
Something I thought would be so simple has turned into a nightmare. I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. So, I'm going for Plan II and it has to be dummy proof for the
users. No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

Sub Workbook_Open()
Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
Workbooks("index.xls").Activate
End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
Sub ShowMySheetOnly()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
End If
Next Wks
End Sub

Thank you to any and all who offer their insight/suggestions, etc.

On Mar 6, 6:20*pm, "Peter T" <peter_t@discussions wrote:
I thought I was following what you want but got lost somewhere along the
line.

Why not hide the requisite sheets in the 'read-only' wb's open event.
Anyway, if you want to run a macro in another workbook you can use the Run
method, eg

Dim sMacro As String
sMacro = "'FileIndex.xls'!ShowMySheetOnly"

Application.Run sMacro

There's a pair of apostrophes bracketing the filename. Normally they are not
necessary but may be required if the filename includes certain characters,
no harm to include the apostrophes even if not required.

Regards,
Peter T

"imelda1ab" wrote in message

...



Something I thought would be so simple has turned into a nightmare. *I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. *Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. *So, I'm going for Plan II and it has to be dummy proof for the
users. *No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? *The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

* * * *Sub Workbook_Open()
* * * *Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
* * * *Workbooks("index.xls").Activate
* * * *End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
* * * *Sub ShowMySheetOnly()
* * * *Dim Wks As Worksheet
* * * *For Each Wks In ThisWorkbook.Worksheets
* * * *If Wks.Name < "Sheet1" Then
* * * * * *If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
* * * *End If
* * * *Next Wks
* * * *End Sub

Thank you to any and all who offer their insight/suggestions, etc.- Hide quoted text -

- Show quoted text -

Awesome, that worked perfectly! I wish hiding the sheets in the 'read-
only' wb's open event was an option, but for various reasons relating
to the environment it's being used/accessed I can't. Nothing is ever
easy.

You've helped me so much, I can't even begin to express my gratitude.
If you have time to further assist, I have two additional questions:

I have a Read-Only statement on close in my Index.xls doc (If
ThisWorkbook.ReadOnly = True Then ThisWorkbook.Saved = False) that
works if I browse to Index.xls and open read-only; however, launching
it using the macro above I am prompted with the Save Changes dialog.
Is this possible? What/where do I put the code if it's even a
possibility?

Which leads me to my next question: when I close the "Read Only"
document, am I able to force the UserIndex.xls doc to close and not
prompt to save changes? Even a timed event would be acceptable.

"imelda1ab" wrote in message
...
On Mar 6, 6:20 pm, "Peter T" <peter_t@discussions wrote:
I thought I was following what you want but got lost somewhere along the
line.

Why not hide the requisite sheets in the 'read-only' wb's open event.
Anyway, if you want to run a macro in another workbook you can use the Run
method, eg

Dim sMacro As String
sMacro = "'FileIndex.xls'!ShowMySheetOnly"

Application.Run sMacro

There's a pair of apostrophes bracketing the filename. Normally they are
not
necessary but may be required if the filename includes certain characters,
no harm to include the apostrophes even if not required.

Regards,
Peter T

"imelda1ab" wrote in message

...



Something I thought would be so simple has turned into a nightmare. I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. So, I'm going for Plan II and it has to be dummy proof for the
users. No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

Sub Workbook_Open()
Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
Workbooks("index.xls").Activate
End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
Sub ShowMySheetOnly()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
End If
Next Wks
End Sub

Thank you to any and all who offer their insight/suggestions, etc.- Hide
quoted text -

- Show quoted text -

Awesome, that worked perfectly! I wish hiding the sheets in the 'read-
only' wb's open event was an option, but for various reasons relating
to the environment it's being used/accessed I can't. Nothing is ever
easy.

You've helped me so much, I can't even begin to express my gratitude.
If you have time to further assist, I have two additional questions:

I have a Read-Only statement on close in my Index.xls doc (If
ThisWorkbook.ReadOnly = True Then ThisWorkbook.Saved = False) that
works if I browse to Index.xls and open read-only; however, launching
it using the macro above I am prompted with the Save Changes dialog.
Is this possible? What/where do I put the code if it's even a
possibility?

Which leads me to my next question: when I close the "Read Only"
document, am I able to force the UserIndex.xls doc to close and not
prompt to save changes? Even a timed event would be acceptable.

--------------------------------------------------

Not sure why the entire post above the dashed line is not "quoted", strange!

Anyway, glad the Run statement worked, it was a bit of a guess.

Concerning your two new questions, I don't follow if you are controlling
Index.xls from within itself or from some other workbook. Also, if a file
has been opened as Read-Only I don't see why the Save dialog would ever
appear when it comes to closing it. However, see if any of the following
help for your scenario

First set a reference to your file, eg

Dim wb as Workbook
Set wb = Application.Workbooks("Index.xls")

wb.Close False
' or
wb.Saved = True
wb.Close

I doubt you'll want the following but just in case -

Application.DisplayAlerts = False
' code
' reset alerts
Application.DisplayAlerts = True

Regards,
Peter T

On Mar 7, 10:38*am, "Peter T" <peter_t@discussions wrote:
"imelda1ab" wrote in message

...
On Mar 6, 6:20 pm, "Peter T" <peter_t@discussions wrote:





I thought I was following what you want but got lost somewhere along the
line.

Why not hide the requisite sheets in the 'read-only' wb's open event.
Anyway, if you want to run a macro in another workbook you can use the Run
method, eg

Dim sMacro As String
sMacro = "'FileIndex.xls'!ShowMySheetOnly"

Application.Run sMacro

There's a pair of apostrophes bracketing the filename. Normally they are
not
necessary but may be required if the filename includes certain characters,
no harm to include the apostrophes even if not required.

Regards,
Peter T

"imelda1ab" wrote in message

...

Something I thought would be so simple has turned into a nightmare. I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. So, I'm going for Plan II and it has to be dummy proof for the
users. No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

Sub Workbook_Open()
Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
Workbooks("index.xls").Activate
End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
Sub ShowMySheetOnly()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
End If
Next Wks
End Sub

Thank you to any and all who offer their insight/suggestions, etc.- Hide
quoted text -

- Show quoted text -

Awesome, that worked perfectly! *I wish hiding the sheets in the 'read-
only' wb's open event was an option, but for various reasons relating
to the environment it's being used/accessed I can't. *Nothing is ever
easy.

You've helped me so much, I can't even begin to express my gratitude.
If you have time to further assist, I have two additional questions:

I have a Read-Only statement on close in my Index.xls doc (If
ThisWorkbook.ReadOnly = True Then ThisWorkbook.Saved = False) that
works if I browse to Index.xls and open read-only; however, launching
it using the macro above I am prompted with the Save Changes dialog.
Is this possible? *What/where do I put the code if it's even a
possibility?

Which leads me to my next question: when I close the "Read Only"
document, am I able to force the UserIndex.xls doc to close and not
prompt to save changes? *Even a timed event would be acceptable.

--------------------------------------------------

Not sure why the entire post above the dashed line is not "quoted", strange!

Anyway, glad the Run statement worked, it was a bit of a guess.

Concerning your two new questions, I don't follow if you are controlling
Index.xls from within itself or from some other workbook. Also, if a file
has been opened as Read-Only I don't see why the Save dialog would ever
appear when it comes to closing it. However, see if any of the following
help for your scenario

First set a reference to your file, eg

Dim wb as Workbook
Set wb = Application.Workbooks("Index.xls")

wb.Close False
' or
wb.Saved = True
wb.Close

I doubt you'll want the following but just in case -

Application.DisplayAlerts = False
' code
' reset alerts
Application.DisplayAlerts = True

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Trying to control FileIndex.xls from UserIndex.xls. Maybe there's a
better route to get where I want, I just don't know enough to ask the
right questions.

I'd like a user to open FileIndex.xls (I'll call Doc1-Controlling) and
unbeknownst to them UserIndex.xls (I'll call Doc2-ReadOnly) is
actually the file that opens, Read-Only, with only the one worksheet
visible. When they close UserIndex.xls (Doc2), I don't want them
prompted with Save and I want Doc1 to close automatically (either when
Doc2 is closed or close as soon as Doc2 opens, whichever is easier -
if at all possible. Does that make any sense at all?

"imelda1ab" wrote in message
...
On Mar 7, 10:38 am, "Peter T" <peter_t@discussions wrote:
"imelda1ab" wrote in message

...
On Mar 6, 6:20 pm, "Peter T" <peter_t@discussions wrote:





I thought I was following what you want but got lost somewhere along the
line.

Why not hide the requisite sheets in the 'read-only' wb's open event.
Anyway, if you want to run a macro in another workbook you can use the
Run
method, eg

Dim sMacro As String
sMacro = "'FileIndex.xls'!ShowMySheetOnly"

Application.Run sMacro

There's a pair of apostrophes bracketing the filename. Normally they are
not
necessary but may be required if the filename includes certain
characters,
no harm to include the apostrophes even if not required.

Regards,
Peter T

"imelda1ab" wrote in message

...

Something I thought would be so simple has turned into a nightmare. I
need to open a workbook named index.xls Read Only and then hide 25 of
the 26 worksheets. Yes, I tried to create a new workbook and paste
link of the one worksheet I need, but the workbook is heavy laden with
formulae, formatting, etc. so it crashes everytime I attempt to
paste. So, I'm going for Plan II and it has to be dummy proof for the
users. No protection; no passwords; not based on userlogin.

My question is, is there a way to open UserIndex.xls with it's
Workbook_Open code and somehow "call" the macro ShowMySheetOnly to run
in the Read Only? The macros work perfectly separately, but I cannot
figure out how to combine them so the user only has to open the one
document?

What I have for the two docs:

UserIndex.xls opens FileIndex.xls read only:

Sub Workbook_Open()
Workbooks.Open Filename:="C:\medcenter\index.xls",
ReadOnly:=True
Workbooks("index.xls").Activate
End Sub

Hide all worksheets but Sheet1 in FileIndex.xls:
Sub ShowMySheetOnly()
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Wks.Name < "Sheet1" Then
If Wks.Visible = xlSheetVisible Then Wks.Visible =
xlSheetHidden
End If
Next Wks
End Sub

Thank you to any and all who offer their insight/suggestions, etc.-
Hide
quoted text -

- Show quoted text -

Awesome, that worked perfectly! I wish hiding the sheets in the 'read-
only' wb's open event was an option, but for various reasons relating
to the environment it's being used/accessed I can't. Nothing is ever
easy.

You've helped me so much, I can't even begin to express my gratitude.
If you have time to further assist, I have two additional questions:

I have a Read-Only statement on close in my Index.xls doc (If
ThisWorkbook.ReadOnly = True Then ThisWorkbook.Saved = False) that
works if I browse to Index.xls and open read-only; however, launching
it using the macro above I am prompted with the Save Changes dialog.
Is this possible? What/where do I put the code if it's even a
possibility?

Which leads me to my next question: when I close the "Read Only"
document, am I able to force the UserIndex.xls doc to close and not
prompt to save changes? Even a timed event would be acceptable.

--------------------------------------------------

Not sure why the entire post above the dashed line is not "quoted",
strange!

Anyway, glad the Run statement worked, it was a bit of a guess.

Concerning your two new questions, I don't follow if you are controlling
Index.xls from within itself or from some other workbook. Also, if a file
has been opened as Read-Only I don't see why the Save dialog would ever
appear when it comes to closing it. However, see if any of the following
help for your scenario

First set a reference to your file, eg

Dim wb as Workbook
Set wb = Application.Workbooks("Index.xls")

wb.Close False
' or
wb.Saved = True
wb.Close

I doubt you'll want the following but just in case -

Application.DisplayAlerts = False
' code
' reset alerts
Application.DisplayAlerts = True

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Trying to control FileIndex.xls from UserIndex.xls. Maybe there's a
better route to get where I want, I just don't know enough to ask the
right questions.

I'd like a user to open FileIndex.xls (I'll call Doc1-Controlling) and
unbeknownst to them UserIndex.xls (I'll call Doc2-ReadOnly) is
actually the file that opens, Read-Only, with only the one worksheet
visible. When they close UserIndex.xls (Doc2), I don't want them
prompted with Save and I want Doc1 to close automatically (either when
Doc2 is closed or close as soon as Doc2 opens, whichever is easier -
if at all possible. Does that make any sense at all?

------------------------------------------

I sort of follow except I can't make sense of this

"I'd like a user to open FileIndex.xls (I'll call Doc1-Controlling) and
unbeknownst to them UserIndex.xls (I'll call Doc2-ReadOnly) is
actually the file that opens, Read-Only, with only the one worksheet
visible."

I assume part of that is already solved with application.Run

As for the close bit, I think you can still use some of what I suggested
last time

In the file that controls the other file (you're getting me at it now!), in
the close event in it's ThisWorkbook module Private Sub
Workbook_BeforeClose(Cancel As Boolean), or following in a normal module

Sub auto_close()
Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks("the-other-file.xls")
On Error GoTo errExit:

If Not wb Is Nothing Then
wb.Close False ' close without saving
End If

errExit:
End Sub

wb.Close False should close the other file without displaying the save
prompt irrespective of it's ReadOnly orSaved status.

Small thing to be aware of, under some circumstances it's possible for a
user to cancel either the wb close or Excel quit. That means any close
events will have fired, ie your other file may have been inadvertently
closed. There are workarounds if essential.

Regards,
Peter T