Generate a message to Save and Not Open when downloading a file

[Asi]

Hi

I have an excel file that I place on a web site and a lot of users download this file for a specific use.
I heard that a lot of problems occur because the users are not used to Save the file to their own disk before starting to edit it. They Simply press the Open button instead of the Save button when downloading the file.

I want to generate a message that will pop the first time that they open the file. The message will tell them "Please save the file to you own PC first and only then open it for edit".

However:
1- I don't want them to get this message every time they open the file.
2- I thought of inserting a dummy value in a specific cell when first opening the file just to indicate to myself that the file has already been opened once so the message will pop only in the 1st opening. The next opening will see this value and message will not pop. But I need to save the file for them in order to place this indication for the next opening.

Any other ideas how to overcome this problem ??

Thanks
Asi

[MaximS]

why not to:
1. Implement a pop-up on your website whith 2 behaviours:
A) link has been clicked before pop-up will not appear and file will be open
B) link hasn't been clicked before pop-up will show instruction

2. Put instruction above the link on your website

[Asi]

I don't think I fully understood your suggestion but:
1. There are already instructions on the wed site to save the file before working on it and users does not seem to follow the instructions.
2. I can't make any change on the web site. it's not my department and I don't think they will do that.
3. I got instructions from above to implement the change on the Excel file itself meaning - generate a message the first time it opens.

any help on how to do that in VBA ?

Thanks
Asi

[MaximS]

try that in your workbook:


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wbcounter As Long
'you can change range and worksheet to someting user will not use
'or want change i.e. some hidden sheet
wbcounter = ThisWorkbook.Worksheets(Sheet1).Range("IV1").Value
ThisWorkbook.Worksheets(Sheet1).Range("IV1").Value = wbcounter + 1
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim Err As String
Dim wbcounter As Long

'you can change range and worksheet to someting user will not use
'or want change i.e. some hidden sheet
wbcounter = ThisWorkbook.Worksheets(Sheet1).Range("IV1").Value
If wbcounter > 0 Then
Err = MsgBox("Please save the file on your PC", _
vbCritical + vbOKOnly, "Please save the file on your PC")
End If
End Sub

[Asi]

1. I think that if I open the file from the Web so when I do the Save in the WorkBook_BeforeClose event - the document is saved on the Web. I do not think it will work (permissions etc...)
I need to check where it is located before saving.

2. In the WorkBook_open event you specified

If wbcounter > 0 Then

Isn't it supose to be

If wbcounter = 0 Then

[MaximS]

of course you can change:

ThisWorkbook.Save

to

ThisWorkbook.SaveAs

with path of your choice and your are right wbcounter should be set to 0

[Asi]

Thanks ...
I used only the Workbook_BeforeClose event which generates a message asking the user to Save the file first if the file is unsaved and it's the 1st time.
It works OK...

However, this message pops when the file is closed.
I prefer to check via Worksheet_Change event on one of the sheets if the file is located on the web and if so then generate the message.

what I did was:

Private Sub Worksheet_Change(ByVal Target As Range)

If Left(ThisWorkbook.Name, 4) = "http" Or Left(ThisWorkbook.path, 4) = "http" Then
Response = MsgBox("Please Save the file on your own PC before you continue", _
vbCritical + vbOKOnly, "Please save file")

End If

End Sub


Will it work when Uploading the file on the web ?
Is there a way to find out if the file is on the Web ?
Is there a way to find out if the file is on your PC ?

Thanks