Hi All,

I have an excel userform that is posted on my companys intranet. The issue I
am having is, users are saving this form to their hard drive and then using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup message
saying that "This is an older version that you are using, Please launch the
form from Intranet for newer version".

Thanks in advance

You could use the workbook full filename to make sure it is run from
the network drive

FName = ThisWorkbook.FullName

Thisworkbook is the workbook where the macro is being executed from.
So if you add a test to the macro checking the fullName they won't be
able to run the macro from another location. Yo uprobably need to
protect this portion of the macro becaue somebody who knows VBA could
then modifiy the check.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

Sam, I think Joel's idea is probably a good one.

But users are a perverse bunch, and may end up resenting the delay involved,
although Joel's approach could 'train' them properly in short order. I had
some experience with a similar situation years ago with the 'front end' to an
Access database.

Another approach you might take would be to put a shortcut to the file on
each one's desktop; the theory being that the ease of access through the
shortcut reduces the temptation to make a local copy. An alternative to that
is to provide a shortcut on the desktop that links to a little .bat file that
copies the file from the network drive to their local drive which will help,
to some small degree, assure that they have something resembling the latest
version on their system.

"sam" wrote:

Hi All,

I have an excel userform that is posted on my companys intranet. The issue I
am having is, users are saving this form to their hard drive and then using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup message
saying that "This is an older version that you are using, Please launch the
form from Intranet for newer version".

Thanks in advance

Another approach is to give the workbook access only by an
administrator. Then create a shortcut that anybody can access which has
administrative priviledge to run the workbook and has access by
everybody. People can copy the shortcut but won't be able to change the
workbook.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

Another idea, trap the SaveAs dialog

' in the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then
Cancel = True
MsgBox ThisWorkbook.Name & " can only be saved to - " & vbCr & _
ThisWorkbook.Path
End If

End Sub

Regards,
Peter T

"sam" wrote in message
...
Hi All,

I have an excel userform that is posted on my companys intranet. The issue
I
am having is, users are saving this form to their hard drive and then
using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as
they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup
message
saying that "This is an older version that you are using, Please launch
the
form from Intranet for newer version".

Thanks in advance

Hi Sam,
I have taken a more direct approach to your problem. Eliminate any
alternative to save the file except for the one youve provided. As provided
below. HTH.

Sub DisableSaveButton()
Dim SaveButton As CommandBarButton
Set SaveButton = CommandBars("Standard").Controls(3)

If SaveButton.Enabled = True Then
SaveButton.Enabled = False
End If
End Sub

Sub DisableFileSaveAs()
Dim FileMenu As Object
Dim Save As CommandBarButton
Dim SaveAs As CommandBarButton

' Assign the file menu to a variable
Set FileMenu = Application.CommandBars(1).Controls(1)

'assign the Save button to a variable
Set Save = FileMenu.Controls(4)

'Assign the SaveAs Button to variable
Set SaveAs = FileMenu.Controls(5)

'Leave File menu Enabled
FileMenu.Enabled = True

' Turn off Save button
Save.Enabled = True

'Turn off SaveAs Button
SaveAs.Enabled = True
End Sub


Sub SaveToIntranet()
Dim NetworkShare As String

NetworkShare = "\\SomethingGeneric\SomeFolder\aSubFolder\" ' Your Network
share here

ThisWorkbook.SaveAs NetworkShare + "\" + _
ThisWorkbook.Name, ThisWorkbook.FileFormat
End Sub


"sam" wrote:

Hi All,

I have an excel userform that is posted on my companys intranet. The issue I
am having is, users are saving this form to their hard drive and then using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup message
saying that "This is an older version that you are using, Please launch the
form from Intranet for newer version".

Thanks in advance

Hey Joel,

What do you mean by "use the workbook full filename?" where do I use that?
The issue here is that once I post the form on intranet, Users click the
link on intranet to launch the form and then they get options to "open, save,
cancle" and they click on save instead of opening it everytime.

So basically now they have a copy of the form on their local drives, how
would I use "FName = ThisWorkbook.FullName" to make them open the form
everytime from intranet?

Thanks in advance

"joel" wrote:


You could use the workbook full filename to make sure it is run from
the network drive

FName = ThisWorkbook.FullName

Thisworkbook is the workbook where the macro is being executed from.
So if you add a test to the macro checking the fullName they won't be
able to run the macro from another location. Yo uprobably need to
protect this portion of the macro becaue somebody who knows VBA could
then modifiy the check.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

.

Hey JLathman,

It would be hard to save shortcuts on every users machine as there are like
100's of users. But how do you even make the shortcut to work everytime we
have a new form? because the form has different name everytime we have a new
version.

Thanks for your help

"JLatham" wrote:

Sam, I think Joel's idea is probably a good one.

But users are a perverse bunch, and may end up resenting the delay involved,
although Joel's approach could 'train' them properly in short order. I had
some experience with a similar situation years ago with the 'front end' to an
Access database.

Another approach you might take would be to put a shortcut to the file on
each one's desktop; the theory being that the ease of access through the
shortcut reduces the temptation to make a local copy. An alternative to that
is to provide a shortcut on the desktop that links to a little .bat file that
copies the file from the network drive to their local drive which will help,
to some small degree, assure that they have something resembling the latest
version on their system.

"sam" wrote:

Hi All,

I have an excel userform that is posted on my companys intranet. The issue I
am having is, users are saving this form to their hard drive and then using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup message
saying that "This is an older version that you are using, Please launch the
form from Intranet for newer version".

Thanks in advance

Hey Peter,

I can disable the save once users have opened the workbook, But when they
click on intranet to launch the form they get an option to "open,save,cancel"
and they click save there. Is there any way to disable the save there?

Thanks in advance

"Peter T" wrote:

Another idea, trap the SaveAs dialog

' in the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then
Cancel = True
MsgBox ThisWorkbook.Name & " can only be saved to - " & vbCr & _
ThisWorkbook.Path
End If

End Sub

Regards,
Peter T

"sam" wrote in message
...
Hi All,

I have an excel userform that is posted on my companys intranet. The issue
I
am having is, users are saving this form to their hard drive and then
using
it(Instead of launching it everytime from intranet), SO basically when I
have a new version of form on intranet they still use the old version as
they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup
message
saying that "This is an older version that you are using, Please launch
the
form from Intranet for newer version".

Thanks in advance


.

You can put the code really any place. For example you can put it in
the workbook open command and automatically close the workbook if the
file is in the wrong location. You can put it in the code where the
form is opened.


If you add to your code this message box

msbbox(thisworkbook.Fullname)


Then you put a test in your macro to disble or close the workbook if
the FullName doesn't equal some known value

if thisworkbook.Fullname < "h:\Myfolder\book1.xls" then
thisworkbook.close savechanges :=false
end if


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

Impossible to answer your question without knowing more about that form you
are talking about.

Irrespective of that, did you try the suggestion, maybe it'll do what you
want. Though if the workbook is not even open at that stage of course it
won't work.

Regards,
Peter T


"sam" wrote in message
...
Hey Peter,

I can disable the save once users have opened the workbook, But when they
click on intranet to launch the form they get an option to
"open,save,cancel"
and they click save there. Is there any way to disable the save there?

Thanks in advance

"Peter T" wrote:

Another idea, trap the SaveAs dialog

' in the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then
Cancel = True
MsgBox ThisWorkbook.Name & " can only be saved to - " & vbCr & _
ThisWorkbook.Path
End If

End Sub

Regards,
Peter T

"sam" wrote in message
...
Hi All,

I have an excel userform that is posted on my companys intranet. The
issue
I
am having is, users are saving this form to their hard drive and then
using
it(Instead of launching it everytime from intranet), SO basically when
I
have a new version of form on intranet they still use the old version
as
they
have a habbit of saving it on their hard drive.

Is there a way to resove this? something like they would see a popup
message
saying that "This is an older version that you are using, Please launch
the
form from Intranet for newer version".

Thanks in advance


.

Since a local file wouldn't open iin the temporary Internet file it
would open as a regular workbook. Also not everybody will be using IE5.
Try this


if instr(thisworkbook.Fullname , _
"C:\Documents\Local Settings\Temporary Internet Files") 0 then


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

Hi Joel,

I forgot to mention about user id's in the address. The address is
"C:\Documents\sam112\Local Settings\Temporary Internet Files..." where sam112
is my userid. Here the path will be different for all the users, so for
tim112 the path will be "C:\Documents\tim112\Local Settings\Temporary
Internet Files..." and so on for other users.

how can we do it in this case?

Sorry for the confusion

"joel" wrote:


Since a local file wouldn't open iin the temporary Internet file it
would open as a regular workbook. Also not everybody will be using IE5.
Try this


if instr(thisworkbook.Fullname , _
"C:\Documents\Local Settings\Temporary Internet Files") 0 then


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help

.

When people are copying the file to a local folder are they copying the
XLS or a webpage? I'm assuming it is an XLS file they are copying and
not a webpage.

You can shorten the location you are looking for. This isn't very
sophisticated. A file opened from a non web location won't be put into
the temporary Internet Files folder.


if instr(thisworkbook.Fullname , _
"Local Settings\Temporary Internet Files") 0 then


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180187

Microsoft Office Help