Open Workbook Read Only and hide select worksheets

[imelda1ab]

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.

 

[Peter T]

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]

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












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

 

[Peter T]

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










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

 

[imelda1ab]

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?

 

[Peter T]

Hide
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

 

[imelda1ab]

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- Hide quoted text -

- Show quoted text -

You are quite simply my hero. I'm working on Multi-Select List Boxes
next. Are you a pro at those too?

Again, thanks a million.

 

[Peter T]

Regards,
Peter T- Hide quoted text -

- Show quoted text -

You are quite simply my hero. I'm working on Multi-Select List Boxes
next. Are you a pro at those too?

Again, thanks a million.

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

Yep, 'pro' is my middle name. If I don't know it I'll just bluff it, as I
did with most of my answers in this thread !

Regards,
Peter T

 

[imelda1ab]

You are quite simply my hero. I'm working on Multi-Select List Boxes
next.  Are you a pro at those too?

Again, thanks a million.

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

Yep, 'pro' is my middle name. If I don't know it I'll just bluff it, as I
did with most of my answers in this thread !

Regards,
Peter T

Peter T, Peter T, Where You Be Peter T?
Okay, I have this bad boy working like a charm EXCEPT when I close
Doc2 I am still being prompted with the "Do you wish to save changes?"
dialog. If Doc2 is opened directly at the source as read-only and
then closed, but when Doc2 is launched via the macro in Doc1, I
continue to be prompted. Is there a way to avoid the Save prompt when
I'm opening Doc2 from Doc1?

Code from Doc1
Sub Workbook_Open()
Workbooks.Open Filename:="C:\mac2\Doc2.xls", ReadOnly:=True
Workbooks("Doc2.xls").Activate
Dim sMacro As String
sMacro = "'Doc2.xls'!ShowAffOnly"
Application.Run sMacro
ThisWorkbook.Close False
End Sub
--------------------------------------------
Code from Doc2:
Private Sub Before_Save(Cancel As Boolean)
If ThisWorkbook.ReadOnly = True Then
ThisWorkbook.Saved = True
Else
ThisWorkbook.Saved = False


End Sub

Private Sub Auto_Close(Cancel As Boolean)
If ThisWorkbook.ReadOnly = True Then
ThisWorkbook.Saved = True
Else
ThisWorkbook.Saved = False
End Sub

 

[Peter T]

You are quite simply my hero. I'm working on Multi-Select List Boxes
next. Are you a pro at those too?

Again, thanks a million.

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

Yep, 'pro' is my middle name. If I don't know it I'll just bluff it, as I
did with most of my answers in this thread !

Regards,
Peter T

Peter T, Peter T, Where You Be Peter T?
Okay, I have this bad boy working like a charm EXCEPT when I close
Doc2 I am still being prompted with the "Do you wish to save changes?"
dialog. If Doc2 is opened directly at the source as read-only and
then closed, but when Doc2 is launched via the macro in Doc1, I
continue to be prompted. Is there a way to avoid the Save prompt when
I'm opening Doc2 from Doc1?

Code from Doc1
Sub Workbook_Open()
Workbooks.Open Filename:="C:\mac2\Doc2.xls", ReadOnly:=True
Workbooks("Doc2.xls").Activate
Dim sMacro As String
sMacro = "'Doc2.xls'!ShowAffOnly"
Application.Run sMacro
ThisWorkbook.Close False
End Sub
--------------------------------------------
Code from Doc2:
Private Sub Before_Save(Cancel As Boolean)
If ThisWorkbook.ReadOnly = True Then
ThisWorkbook.Saved = True
Else
ThisWorkbook.Saved = False


End Sub

Private Sub Auto_Close(Cancel As Boolean)
If ThisWorkbook.ReadOnly = True Then
ThisWorkbook.Saved = True
Else
ThisWorkbook.Saved = False
End Sub

------------------------------------------------------
Reply starts here:

You'll get the save prompt when closing a file that has been flagged as
'dirty', ie after some change has occurred to the file. Do you know why your
Doc2 is thus flagged. Maybe it's after doing this

Workbooks.Open Filename:="C:\mac2\Doc2.xls", ReadOnly:=True
Workbooks("Doc2.xls").Activate

add this line immediately after

msgbox ActiveWorbook.Saved
If that's False it's 'dirty' and will trigger Save dialog unless the it's
Saved property is changed. So if .Saved returns False at this point, replace
the msgbox with
ActiveWorbook.Saved = True to remove the 'dirty' flag

If the file is getting flagged as dirty at some later stage maybe it's for
an obvious reason. Whatever, if you don't want the file to trigger the save
prompt on close there are two ways to prevent it -

- set the file's .Saved property True
(various examples of doing that throughout this thread)

- programmatically close it like this
Workbooks("Doc2.xls").Close False

Regards,
Peter T

 

[imelda1ab]

Peter T, Peter T, Where You Be Peter T?
Okay, I have this bad boy working like a charm EXCEPT when I close
Doc2 I am still being prompted with the "Do you wish to save changes?"
dialog.  If Doc2 is opened directly at the source as read-only and
then closed, but when Doc2 is launched via the macro in Doc1, I
continue to be prompted.  Is there a way to avoid the Save prompt when
I'm opening Doc2 from Doc1?

Code from Doc1
Sub Workbook_Open()
Workbooks.Open Filename:="C:\mac2\Doc2.xls", ReadOnly:=True
       Workbooks("Doc2.xls").Activate
Dim sMacro As String
sMacro = "'Doc2.xls'!ShowAffOnly"
Application.Run sMacro
 ThisWorkbook.Close False
       End Sub
--------------------------------------------
Code from Doc2:
Private Sub Before_Save(Cancel As Boolean)
    If ThisWorkbook.ReadOnly = True Then
    ThisWorkbook.Saved = True
    Else
    ThisWorkbook.Saved = False

End Sub

Private Sub Auto_Close(Cancel As Boolean)
    If ThisWorkbook.ReadOnly = True Then
    ThisWorkbook.Saved = True
    Else
    ThisWorkbook.Saved = False
End Sub

------------------------------------------------------
Reply starts here:

You'll get the save prompt when closing a file that has been flagged as
'dirty', ie after some change has occurred to the file. Do you know why your
Doc2 is thus flagged. Maybe it's after doing this

Workbooks.Open Filename:="C:\mac2\Doc2.xls", ReadOnly:=True
       Workbooks("Doc2.xls").Activate

add this line immediately after

msgbox ActiveWorbook.Saved
If that's False it's 'dirty' and will trigger Save dialog unless the it's
Saved property is changed. So if .Saved returns False at this point, replace
the msgbox with
ActiveWorbook.Saved = True to remove the 'dirty' flag

If the file is getting flagged as dirty at some later stage maybe it's for
an obvious reason. Whatever, if you don't want the file to trigger the save
prompt on close there are two ways to prevent it -

- set the file's .Saved property True
(various examples of doing that throughout this thread)

- programmatically close it like this
Workbooks("Doc2.xls").Close False

Regards,
Peter T- Hide quoted text -

- Show quoted text -

I don't know how much you earn doing whatever it is that you do, but
it's not enough. You're incredible. Thanks so much.