Prompt for narrative on saving spreadsheet

[creoche]

Hello,

First post so please be kind!

I am trying to build an input box so that when you go to save you are prompted to provide a narrative of the changes that have been made to the file, and for these changes to be saved on the spreadsheet in blank cells. The spreadsheet already has vba which records the last person to save changes and the time (as set out below) but it would be good if this was replicated each time the document was saved, with the content of the narrative box in the third cell. I'm not sure if someone has come across this before or has a solution already in place. Any help given will be much appreciated. I am using Excel 2007.

Kind regards

current code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("mod_opr") = Application.UserName
Range("mod_tme") = Date & " " & Time
End Sub


Private Sub Workbook_Open()
MsgBox ("Last modified by " & Range("mod_opr") & vbCrLf & vbCrLf & "on " & Range("mod_tme"))
End Sub

[SamT]

Excel VBA's InputBox Function will work.

Range("mod_notes") = InputBox(Prompt:="Creoche, see help")

[david000]

The Knowledge Base here has a pretty close approximation of this type of thing, it's worth a look at the very least.

Private Sub Workbook_Open()
    Dim Rng As Range
    Application.ScreenUpdating = False
    Set Rng = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Rng.Value = Environ("username")
    Rng.Offset(0, 1).Value = Now()
    Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Rng As Range
    Application.ScreenUpdating = False
    Set Rng = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp)
    Rng.Offset(0, 1).Value = Now()
    Application.ScreenUpdating = True
End Sub

[creoche]

Thank you for this. I've had a play, blended the two and come up with the following which I think works quite well:

Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Audit").Range("B" & Rows.Count).End(xlUp)
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
Sheets("Audit").Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = InputBox(Prompt:="Please detail any changes made")
Rng.Offset(0, 1).Value = Now()
End Sub

If any body can see any problems, please let me know before I start pompously demonstrating this to people

[SamT]

The Open sub is perfect, But in the close sub, I would Set Rng to Column A, the time when it was opened, then use Offsets(0, 2) and (0, 3) For the close time and details.

BTW, don't turn on ScreenUpdating until the end of the sub.

[creoche]

I'm guessing these changes remove the issue I currently have which is that if there is an empty cell in the line above then the narrative goes into the first available cell not necessarily in the same line - I don't know how to make content in the InputBox mandatory but if this does the same thing then would be good.

I tried making these suggested changes but the data jumps around a bit into the wrong columns - any chance you could quickly point out the error?

Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Audit").Range("A" & Rows.Count).End(xlUp)
Rng.Offset(0, 2).Value = Now()
Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = InputBox(Prompt:="Please detail any changes made")
Rng.Offset(0, 3).Value = Now()
Application.ScreenUpdating = True
End Sub

Thank you again for your help

[snb]

See the attachment.
Open the file, save it and see what happens.

[SamT]

I thought you wanted each "Open Book" record on the same row like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim Rng As Range 
    Application.ScreenUpdating = False 

'Get "Book Open" record (Last used cell in "A")
    Set Rng = Sheets("Audit").Range("A" & Rows.Count).End(xlUp) 

'Record changes details ("C" in same Row as above)
    Rng.Offset(0, 2) = InputBox(Prompt:="Please detail any changes made") 

'Record "Book Close" time ("D" in same row)
    Rng.Offset(0, 3).Value = Now() 
    Application.ScreenUpdating = True 
End Sub

Without looking, I'll bet that snb has a perfect example for you, but my idea would be

'Record changes details ("C" in same Row as above)
   'Dim a variable above. I'll use X
     X= InputBox(Prompt:="Please detail any changes made") 
     'Len < random number between 10 and 20
     Do While Len(X) < Int(21 * Rnd(Now * (-10000)) + 10)
        'Keeps Smarty Users from just entering a single space and figuring how many chars are needed
        'Add here as desired: MsgBox to users
        X= InputBox(Prompt:="Please detail any changes made") 
    Loop
Rng.Offset(0, 2) = X