Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple users saving a form
I have created a form that has a macro to insert the user's name and date
when the user click on the control button. When the user click on the control button the form is saved so that the user cannot edit the fields and the form serves as the user's electronic signature. However the problem that I have is that the user will save the file using a different file name other than the original file name (all users who access the form in the company will do so). Once the first user who has provided his electronic signature saves using whatever name the person likes, he will email the form to the next approver who will recieve a run time error when he tries to click on the control button to provide his electron signature approval of the form. How can I allow the user to save the form as whatever file name they choose after they have completed the form and still keep my coding and electronic signature integrity and still allow the various users to rename the file and continue the work flow. Below is the macro that i have created: Windows("Form to request reward via Payroll.xls").Activate Worksheets("Payroll Check Rwd Form").Unprotect "goats" ActiveSheet.Shapes("Button 59").Select Selection.Characters.Text = "Next WTL Approval:" With Selection.Characters(Start:=1, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Range("C39").Value = UserName() Range("H39").Value = Date Worksheets("Payroll Check Rwd Form").Protect "goats" ActiveWorkbook.Save End With End Sub The file name is "From to request reward via Payroll". Once the user enters the data in the form, they will rename the file from the generic form name above and then the next approver receives the runtime error is there a way to modify the code that will still protect the fields and allow the user to rename the file? Any help that you could provide would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple users saving a form
maybe changing this
Windows("Form to request reward via Payroll.xls").Activate to ThisWorkbook.Activate might help? "pdaws" wrote in message ... I have created a form that has a macro to insert the user's name and date when the user click on the control button. When the user click on the control button the form is saved so that the user cannot edit the fields and the form serves as the user's electronic signature. However the problem that I have is that the user will save the file using a different file name other than the original file name (all users who access the form in the company will do so). Once the first user who has provided his electronic signature saves using whatever name the person likes, he will email the form to the next approver who will recieve a run time error when he tries to click on the control button to provide his electron signature approval of the form. How can I allow the user to save the form as whatever file name they choose after they have completed the form and still keep my coding and electronic signature integrity and still allow the various users to rename the file and continue the work flow. Below is the macro that i have created: Windows("Form to request reward via Payroll.xls").Activate Worksheets("Payroll Check Rwd Form").Unprotect "goats" ActiveSheet.Shapes("Button 59").Select Selection.Characters.Text = "Next WTL Approval:" With Selection.Characters(Start:=1, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Range("C39").Value = UserName() Range("H39").Value = Date Worksheets("Payroll Check Rwd Form").Protect "goats" ActiveWorkbook.Save End With End Sub The file name is "From to request reward via Payroll". Once the user enters the data in the form, they will rename the file from the generic form name above and then the next approver receives the runtime error is there a way to modify the code that will still protect the fields and allow the user to rename the file? Any help that you could provide would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple users saving a form
Thank you Patrick, this solved my problem and has helped tremendously :)
"Patrick Molloy" wrote: maybe changing this Windows("Form to request reward via Payroll.xls").Activate to ThisWorkbook.Activate might help? "pdaws" wrote in message ... I have created a form that has a macro to insert the user's name and date when the user click on the control button. When the user click on the control button the form is saved so that the user cannot edit the fields and the form serves as the user's electronic signature. However the problem that I have is that the user will save the file using a different file name other than the original file name (all users who access the form in the company will do so). Once the first user who has provided his electronic signature saves using whatever name the person likes, he will email the form to the next approver who will recieve a run time error when he tries to click on the control button to provide his electron signature approval of the form. How can I allow the user to save the form as whatever file name they choose after they have completed the form and still keep my coding and electronic signature integrity and still allow the various users to rename the file and continue the work flow. Below is the macro that i have created: Windows("Form to request reward via Payroll.xls").Activate Worksheets("Payroll Check Rwd Form").Unprotect "goats" ActiveSheet.Shapes("Button 59").Select Selection.Characters.Text = "Next WTL Approval:" With Selection.Characters(Start:=1, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Range("C39").Value = UserName() Range("H39").Value = Date Worksheets("Payroll Check Rwd Form").Protect "goats" ActiveWorkbook.Save End With End Sub The file name is "From to request reward via Payroll". Once the user enters the data in the form, they will rename the file from the generic form name above and then the next approver receives the runtime error is there a way to modify the code that will still protect the fields and allow the user to rename the file? Any help that you could provide would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple users saving a form
a pleasure. Thank you
"pdaws" wrote in message ... Thank you Patrick, this solved my problem and has helped tremendously :) "Patrick Molloy" wrote: maybe changing this Windows("Form to request reward via Payroll.xls").Activate to ThisWorkbook.Activate might help? "pdaws" wrote in message ... I have created a form that has a macro to insert the user's name and date when the user click on the control button. When the user click on the control button the form is saved so that the user cannot edit the fields and the form serves as the user's electronic signature. However the problem that I have is that the user will save the file using a different file name other than the original file name (all users who access the form in the company will do so). Once the first user who has provided his electronic signature saves using whatever name the person likes, he will email the form to the next approver who will recieve a run time error when he tries to click on the control button to provide his electron signature approval of the form. How can I allow the user to save the form as whatever file name they choose after they have completed the form and still keep my coding and electronic signature integrity and still allow the various users to rename the file and continue the work flow. Below is the macro that i have created: Windows("Form to request reward via Payroll.xls").Activate Worksheets("Payroll Check Rwd Form").Unprotect "goats" ActiveSheet.Shapes("Button 59").Select Selection.Characters.Text = "Next WTL Approval:" With Selection.Characters(Start:=1, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Range("C39").Value = UserName() Range("H39").Value = Date Worksheets("Payroll Check Rwd Form").Protect "goats" ActiveWorkbook.Save End With End Sub The file name is "From to request reward via Payroll". Once the user enters the data in the form, they will rename the file from the generic form name above and then the next approver receives the runtime error is there a way to modify the code that will still protect the fields and allow the user to rename the file? Any help that you could provide would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sharing excel sheet with multiple users, but other users cant see | Excel Discussion (Misc queries) | |||
multiple users saving to a workbook | Excel Programming | |||
Creating a form for multiple users | Excel Worksheet Functions | |||
Saving to users My Documents | Excel Programming | |||
Saving from form into multiple sheets | Excel Programming |