Hi
I have a very tricky situation. I want to disble the save option of the workbook. So I wrote this cod

Private Sub Workbook_BeforeClose(Cancel as Boolean

Application.DisplayAlerts = Fals
ThisWorkbook.Saved = Tru
ThisWorkbook.Clos
Application.DisplayAlerts = true

End Su

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel as Boolean

Application.DisplayAlerts = Fals
Cancel = Tru
Application.DisplayAlerts = tru

End Su

Now the problem is the moment I write this code, it is activated before saving and this code is not saved at all. How do I save this code? My objective is that the user should not be able to save any changes in the workbook
TI
Shilps

Open it with macros disabled, then add your code and save?

hth,

Doug Glancy

"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window: Cancel =
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Another way is to use an environment variable rather than a file, and test
it with Environ.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window: Cancel =
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Good suggestion.

I actually thought to myself, "great idea, I can use environ for quickly
switching between test and prod for my development".

After a 5 minute search, I can find no easy way to write out environment
variables (other than hitting the SetEnvironmentVariable API - and I'm
having trouble with that too!)

Have you got some code to share?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Another way is to use an environment variable rather than a file, and test
it with Environ.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window: Cancel =
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of
the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated
before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Set up another macro called "SaveFile". In this macro set a boolean
variable called bByPass to True. Then have the SaveFile macro save your file
Then in the before save code, check and see if this variable is true. If
true, then the code in the before save is bypasseed. Then at the end of the
SaveFile macro, setbByPass back to false.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Hi, Shilps,

to save your "tricky code" you have to go into the development mode of
the visual basic editor (in Germany this mode is called
"Entwurfsmodus"). Then save your VBA-Code. (This is possible because
at the development mode your VBA-Code is not active although it is
written in your module.) Then leave this modus (by deactivating it),
write "Eureka!" at your sheet, feel great and fire your boss ... ;-)

Regards.


"Doug Glancy" wrote in message ...
Open it with macros disabled, then add your code and save?

hth,

Doug Glancy

"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in the
workbook.
TIA
Shilps

Hi Rob,

That is exactly what we use it for, dev and prod.

What problem exactly are you getting with the SetEnvironmentVariable API? Is
it that trying to read it with Environ in the same NT/XP session does return
a value? If that is the case, I think this is because Excel seems to load
the environment variables at start-up, and Environ gets the values locally.
So any changes that you make are not reflected in Environ. And as
SetEnvironmentVariable only sets the variable for the current session, this
stymies you.

You can get around it though by using GetEnvironmentVariable to read it as
this will read any variables in the current session. This code snippet
should demonstrate it, GetEnvironmentVariable returns the newly set value,
Environ doesn't

Option Explicit

Private Declare Function GetEnvironmentVariable Lib "kernel32" _
Alias "GetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpBuffer As String, _
ByVal nSize As Long) As Long

Private Declare Function SetEnvironmentVariable Lib "kernel32" _
Alias "SetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpValue As String) As Long

Sub xx()
SetEnvironmentVariable "Rob", "Nuzie!"
MsgBox Environ("Rob")
MsgBox GetEnvironmentVar("Rob")
End Sub


Function GetEnvironmentVar(Name As String) As String
GetEnvironmentVar = String(255, 0)
GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar)
GetEnvironmentVar = TrimNull(GetEnvironmentVar)
End Function

Private Function TrimNull(item As String)
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

If you want it for another session, you will need to write it to the
registry.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Good suggestion.

I actually thought to myself, "great idea, I can use environ for quickly
switching between test and prod for my development".

After a 5 minute search, I can find no easy way to write out environment
variables (other than hitting the SetEnvironmentVariable API - and I'm
having trouble with that too!)

Have you got some code to share?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Another way is to use an environment variable rather than a file, and
test
it with Environ.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in
message
...
Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window: Cancel
=
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option of
the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated
before
saving and this code is not saved at all. How do I save this code? My
objective is that the user should not be able to save any changes in
the
workbook.
TIA
Shilps

Yes, I was trying to use Environ to read variable set by
SetEnvironmentVariable and got into trouble.

It's interesting that the variable only lasts as long as Excel is running.

Thanks for the code - very useful, thanks.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Hi Rob,

That is exactly what we use it for, dev and prod.

What problem exactly are you getting with the SetEnvironmentVariable API?
Is
it that trying to read it with Environ in the same NT/XP session does
return
a value? If that is the case, I think this is because Excel seems to load
the environment variables at start-up, and Environ gets the values
locally.
So any changes that you make are not reflected in Environ. And as
SetEnvironmentVariable only sets the variable for the current session,
this
stymies you.

You can get around it though by using GetEnvironmentVariable to read it as
this will read any variables in the current session. This code snippet
should demonstrate it, GetEnvironmentVariable returns the newly set value,
Environ doesn't

Option Explicit

Private Declare Function GetEnvironmentVariable Lib "kernel32" _
Alias "GetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpBuffer As String, _
ByVal nSize As Long) As Long

Private Declare Function SetEnvironmentVariable Lib "kernel32" _
Alias "SetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpValue As String) As Long

Sub xx()
SetEnvironmentVariable "Rob", "Nuzie!"
MsgBox Environ("Rob")
MsgBox GetEnvironmentVar("Rob")
End Sub


Function GetEnvironmentVar(Name As String) As String
GetEnvironmentVar = String(255, 0)
GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar)
GetEnvironmentVar = TrimNull(GetEnvironmentVar)
End Function

Private Function TrimNull(item As String)
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

If you want it for another session, you will need to write it to the
registry.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Good suggestion.

I actually thought to myself, "great idea, I can use environ for quickly
switching between test and prod for my development".

After a 5 minute search, I can find no easy way to write out environment
variables (other than hitting the SetEnvironmentVariable API - and I'm
having trouble with that too!)

Have you got some code to share?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Another way is to use an environment variable rather than a file, and
test
it with Environ.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in
message
...
Two ways:

1.
Set a breakpoint at the End Sub then from the immediate window:
Cancel
=
False

2.
If you're doing this regularly, you may want an external flag.

For example
Instead of
Cancel = True
Try
If Dir("C:\DebugMode.txt") = "" Then Cancel = True

Then create DebugMode.txt whenever you plan to do some development.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shilps" wrote in message
...
Hi,
I have a very tricky situation. I want to disble the save option
of
the
workbook. So I wrote this code

Private Sub Workbook_BeforeClose(Cancel as Boolean)

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.DisplayAlerts = true

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)

Application.DisplayAlerts = False
Cancel = True
Application.DisplayAlerts = true

End Sub

Now the problem is the moment I write this code, it is activated
before
saving and this code is not saved at all. How do I save this code?
My
objective is that the user should not be able to save any changes in
the
workbook.
TIA
Shilps

Thanks everyone
I got a very simple solution- Wrote Application.EnableEvents=False and was able to save the code. Doug's suggestion was also easy but got this one n didn't try out his
Shilps