visual basic question

Discussion in 'Off-topic' started by rankhornjp, Jan 27, 2011.

  1. rankhornjp

    rankhornjp Active Member

    4,543
    4
    36
    I have a form that i created in word 2007, it is protected. When it is filled out we print it and then don't save it so that the next time it is opened it is blank. Sometimes people forget to click "no" when the "do you want to save" box pops up and then I have to take the time to go back and clear out the form boxes. How do I prevent this?

    I have tried:

    ActiveDocument.Saved = True

    ThisDocument.Saved = True

    activeDocument.close savechanges:=false

    The one in red will work with a test document, i.e. blank document, add the code, save, close. Open that document, type something and when you click close it won't ask if you want to save. BUT it won't work with my form.

    Nothing seems to work. Any ideas?

    Thanks.



    Code:
    Private Sub Document_Close()
    ActiveDocument.Saved = True
    End Sub
    
    Private Sub Document_Open()
    Dim xdApp       As Word.Application
    Dim xlApp       As Excel.Application
    Dim xlWorkbook  As Excel.Workbook
    Dim xlRange     As Excel.Range
    Dim sFile       As String
    Dim LastID      As Integer
    Dim NewID       As Integer
     
    'Set to the location of the Excel "database"
    sFile = "\\DUI_NAS\data\Redirect\DUI School Info\DUIContractNumber.xlsx"
     
    'Set all the variables for the necessary XL objects
    Set xlApp = New Excel.Application
    Set xlWorkbook = xlApp.Workbooks.Open(sFile)
     
    'The used range assumes just one column in the first worksheet
    Set xlRange = xlWorkbook.Worksheets(1).UsedRange
     
    'Use a built-in Excel function to get the max ID from the used range
    LastID = xlApp.WorksheetFunction.Max(xlRange)
     
    'You may want to come up with some crazy algorithm for
    'this, but I opted for the intense + 1
    NewID = LastID + 1
     
    'This will prevent the save dialog from prompting the user
    xlApp.DisplayAlerts = False
    
    'Add new value to textbox'
    TextBox1 = NewID
    TextBox11 = NewID
    'Add the new value to the Excel "database"
    xlRange.Cells(xlRange.Count + 1, 1).Value = NewID
     
    'Save and close
    Call xlWorkbook.Save
    Call xlWorkbook.Close
     
    'Clean Up
    xlApp.DisplayAlerts = True
    Call xlApp.Quit
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    Set xlRange = Nothing
    End Sub
    The extra code just keeps track of a number, in an Excel file, for the number of times the form has been opened. I use this number as a document number on the form when its printed.
     
  2. Fallschirmjäger

    Fallschirmjäger I watch the watchers

    12,835
    62
    48
    Not as familiar with VB as I would like, sadly. I would have thought the features in Word would have been enough to protect the document.


    But, is it possible to have a default value for the blanks in the original document, like having today's date automatically fill the Date_field? Could you use something like " " as the default value for the various fields so that each time the document was opened it would show the defaults?
     

  3. rankhornjp

    rankhornjp Active Member

    4,543
    4
    36
    I think I can, but that would be a LOT of code. If I can just get it to NOT save, that would be much better.
     
  4. NullMatrix

    NullMatrix Member

    712
    0
    16
    Could you set permissions on the file or parent folder so that no one but admins have the ability to modify the file?
     
  5. frankr

    frankr Active Member

    1,122
    0
    36
  6. Mr_Z

    Mr_Z Member

    535
    0
    16
    Look into an OnClosing or BeforeClose type event...I think that is what prompts them to save if there are changes. perhaps you good just override it to not even give them the option.

    That is how it tends to work in the Application Development with VS...not very familiar with VBA
     
  7. phantoms

    phantoms Senior Mumbler

    6,143
    168
    63
    Or you could make the file itself Read Only. This doesn't require Word at all. Right click the file, choose properties and click the Read-only attribute.
     
  8. rankhornjp

    rankhornjp Active Member

    4,543
    4
    36
    i need the user to be able to fill in the form, just not save.

    i will look into this monday
     
  9. Mr_Z

    Mr_Z Member

    535
    0
    16
    Did a little googling myself (this kind of stuff always interests me :) and found an idea that might apply..its for an excel workbook, but i am guessing same thing works for docs:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    End Sub

    to cancel their attempt to save it.


    Also:
    Application.DisplayAlerts = False

    might keep it from prompting them to save at all. maybe put that in the before close event, so that you will get any other alerts you want, but when they go to quit, it suppresses the "Do you want to save your changes"...assuming before close fires before the save message...not sure the order of event firing...you'd have to put it first in line of the stuff that fires. i imagine it is something like

    before quit (closes docs)
    before close (saves docs)
    before save (chance to cancel)
    save
    close
    quit
     
  10. phantoms

    phantoms Senior Mumbler

    6,143
    168
    63
    This is what making the file itself would do. You can open and modify it all you want, but when you try to save it will not. BTW, you can do this to any file on your PC no matter what program created it.
     
  11. frankr

    frankr Active Member

    1,122
    0
    36
    That is true in general.

    However, with a particular document the user could copy the read-only file to a new location with read-write permissions and then save it. Or the document could be emailed to someone else and then saved. Since the OP was trying to do this in VBA at first, it seems that he is looking to have an immutable document (at least as far as average users are concerned).