Macro Amendment.

[Mattlynn via OfficeKB.com]

Hi - i have taken over a survey spreadsheet in a new job.
Basically the users type their issues into a spreadsheet, click on the
relevent postbox image which has a macro assigned. It takes their message and
adds it to another spreadhseet for collation and summaries to be made.
I would like to change the macro so i can register the email that it came
from. Is this possible?
Please help if you can.
I have pasted the macro below, can you please add the additional line of code
if possible.
Many Many Thanks
Matt

Sub Post_To_Department()
'
'
' Re-Coded 17-09-09 by
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("B5").Copy
Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2",
Password:="A123456=b"
Sheets("Department").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = "=NOW()"
ActiveCell.Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Select
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("MoanZone.xls").Activate
Range("B5").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.Show

End Sub

 

[Luke M]

Recording a user email address would be tricky, as that would require XL to
access your email server registry and match a user name to the email. If the
macro records the Windows user name, would that be sufficient?

Sub Post_To_Department()
'
'
' Re-Coded 17-09-09 by
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("B5").Copy
Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2",
Password:="A123456=b"
Sheets("Department").Select
Range("A1").Select
Selection.End(xlDown).Select

'Modified this line for efficiency
ActiveCell.Offset(1, 0).Value = Now

ActiveCell.Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Code added here
'Name added to column C
ActiveCell.Offset(0, 1).Value = Environ("UserName")

ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("MoanZone.xls").Activate
Range("B5").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.Show

End Sub

 

[Mattlynn via OfficeKB.com]

Hey Luke
That sounds great. I am going to test based upon your suggestion.
Many Thanks for taking the time to help out
Regards
Matt

Recording a user email address would be tricky, as that would require XL to
access your email server registry and match a user name to the email. If the
macro records the Windows user name, would that be sufficient?

Sub Post_To_Department()
'
'
' Re-Coded 17-09-09 by
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("B5").Copy
Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2",
Password:="A123456=b"
Sheets("Department").Select
Range("A1").Select
Selection.End(xlDown).Select

'Modified this line for efficiency
ActiveCell.Offset(1, 0).Value = Now

ActiveCell.Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Code added here
'Name added to column C
ActiveCell.Offset(0, 1).Value = Environ("UserName")

ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("MoanZone.xls").Activate
Range("B5").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.Show

End Sub

Hi - i have taken over a survey spreadsheet in a new job.
Basically the users type their issues into a spreadsheet, click on the

[quoted text clipped - 43 lines]

End Sub

 

[Mattlynn via OfficeKB.com]

Luke it worked like a treat - i had to have a play with offset, and
eventually found that 0,2 is what was correct. Thanks ever so much mate

One more thing - when i lock/protect the spreadsheet users can still access
thye macro. Anyway of preventing this??

Thanks

Recording a user email address would be tricky, as that would require XL to
access your email server registry and match a user name to the email. If the
macro records the Windows user name, would that be sufficient?

Sub Post_To_Department()
'
'
' Re-Coded 17-09-09 by
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("B5").Copy
Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2",
Password:="A123456=b"
Sheets("Department").Select
Range("A1").Select
Selection.End(xlDown).Select

'Modified this line for efficiency
ActiveCell.Offset(1, 0).Value = Now

ActiveCell.Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Code added here
'Name added to column C
ActiveCell.Offset(0, 1).Value = Environ("UserName")

ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("MoanZone.xls").Activate
Range("B5").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.Show

End Sub

Hi - i have taken over a survey spreadsheet in a new job.
Basically the users type their issues into a spreadsheet, click on the

[quoted text clipped - 43 lines]

End Sub

 

[Mattlynn via OfficeKB.com]

Hey all - found a way to do it. Drag tool bar off, and remove tOOLS button.
That will do for me unless there ius an actual way to lock
Thanks
Matt

Luke it worked like a treat - i had to have a play with offset, and
eventually found that 0,2 is what was correct. Thanks ever so much mate

One more thing - when i lock/protect the spreadsheet users can still access
thye macro. Anyway of preventing this??

Thanks

Recording a user email address would be tricky, as that would require XL to
access your email server registry and match a user name to the email. If the

[quoted text clipped - 44 lines]