HELP: version control issue

[sam]

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

 

[joel]

You could use the workbook full filename to make sure it is run fro
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 b
able to run the macro from another location. Yo uprobably need t
protect this portion of the macro becaue somebody who knows VBA coul
then modifiy the check.

 

[JLatham]

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.

 

[joel]

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

 

[Peter T]

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

 

[Jeff]

Hi Sam,
I have taken a more direct approach to your problem. Eliminate any
alternative to save the file except for the one you’ve 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]

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

 

[sam]

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

 

[sam]

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

 

[joel]

You can put the code really any place. For example you can put it i
the workbook open command and automatically close the workbook if th
file is in the wrong location. You can put it in the code where th
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 i
the FullName doesn't equal some known value

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

 

[Peter T]

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]

Hey Joel, One more question.

When I launch the form from itranet and do the "MsgBox(ThisForm.FullName)"
I get the fullname in the popup window which includes the file address too,
But that address is for the Temperory Internet Files folder and not any
network drive or the intranet where the form is posted.

Would it work everytime if I match the fullname with this address( Temperory
Internet Files) ?

For eg:

IF ThisWorkbook.FullName <> "C:\Documents\Local Settings\Temporary Internet
Files\Content.IE5\CLSEB4L\Form1.1.xls" Then
MsgBox("Please Launch the form from intranet")
ThisWorkbook.Close SaveChanges:=False

End if

Hope I made it clear

Thanks i advance

 

[joel]

Since a local file wouldn't open iin the temporary Internet file i
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

 

[sam]

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]

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

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


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