| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Validation
I currently have a survey that is used by people nationally to rate a
department. They need to answer 9 questions then click "Submit" which has a macro attached to save and hide their worksheet. How can I add into the macro, code to stop the macro from running and display an error box if someone tries to submit without having populated all the fields (eg: cell L8)? -- scheduler |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Validation
As ALWAYS, post YOUR code for comments.
-- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... I currently have a survey that is used by people nationally to rate a department. They need to answer 9 questions then click "Submit" which has a macro attached to save and hide their worksheet. How can I add into the macro, code to stop the macro from running and display an error box if someone tries to submit without having populated all the fields (eg: cell L8)? -- scheduler |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Validation
The existing code is pretty simple, it just copies the responses in cells
L*"L28 into a hidden sheet (for result compilation), hides the sheet that person was using and saves and closes the workbook. It is: Private Sub CommandButton1_Click() ActiveSheet.Select Range("L8:L28").Select Selection.Copy Sheets("Ratings Results").Visible = True Sheets("Ratings Results").Select Sheets("Ratings Results").Columns("F:F").Select Selection.Insert Shift:=xlToRight Sheets("Ratings Results").Range("A27:C27").Select Sheets("Ratings Results").Visible = False ActiveSheet.Select Range("E4:J4").Select ActiveWorkbook.Save ActiveWindow.SelectedSheets.Visible = False MsgBox "Thank you for completing the Customer Service Satisfaction Survey", vbOKOnly ActiveWorkbook.Save ActiveWorkbook.Close End Sub -- scheduler "Don Guillett" wrote: As ALWAYS, post YOUR code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... I currently have a survey that is used by people nationally to rate a department. They need to answer 9 questions then click "Submit" which has a macro attached to save and hide their worksheet. How can I add into the macro, code to stop the macro from running and display an error box if someone tries to submit without having populated all the fields (eg: cell L8)? -- scheduler |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Validation
Thanks Don,
It worked a charm (and made the whole code a lot smaller). Is there a way to get the macro to also check that if those cells (lets say K8) is populated but with say a 1, 2 or 3 rating, that another cell (say M8) must be also be populated (with a comment)? -- scheduler "Don Guillett" wrote: This will check to make sure 21 cells are not blank or "space barred". Then, it will do the original. It is NOT necessary to unhide the destination sheet or select it. Sub doitright() 'check range If Evaluate("SumProduct(--(Len(trim(L8:L28)) 0))") < 21 Then MsgBox "Please complete All items" Exit Sub End If 'do the copy insert Range("L8:L28").Copy Sheets("Ratings Results").Columns("F").Insert Application.CutCopyMode = False MsgBox "Thank you for completing the Customer" & _ "Service Satisfaction Survey", vbOKOnly End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... The existing code is pretty simple, it just copies the responses in cells L*"L28 into a hidden sheet (for result compilation), hides the sheet that person was using and saves and closes the workbook. It is: Private Sub CommandButton1_Click() ActiveSheet.Select Range("L8:L28").Select Selection.Copy Sheets("Ratings Results").Visible = True Sheets("Ratings Results").Select Sheets("Ratings Results").Columns("F:F").Select Selection.Insert Shift:=xlToRight Sheets("Ratings Results").Range("A27:C27").Select Sheets("Ratings Results").Visible = False ActiveSheet.Select Range("E4:J4").Select ActiveWorkbook.Save ActiveWindow.SelectedSheets.Visible = False MsgBox "Thank you for completing the Customer Service Satisfaction Survey", vbOKOnly ActiveWorkbook.Save ActiveWorkbook.Close End Sub -- scheduler "Don Guillett" wrote: As ALWAYS, post YOUR code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... I currently have a survey that is used by people nationally to rate a department. They need to answer 9 questions then click "Submit" which has a macro attached to save and hide their worksheet. How can I add into the macro, code to stop the macro from running and display an error box if someone tries to submit without having populated all the fields (eg: cell L8)? -- scheduler |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Validation
I can't tell wo seeing your file. If you like, send to my address below
along with these snippets on an inserted sheet along with clear explanations and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... Thanks Don, It worked a charm (and made the whole code a lot smaller). Is there a way to get the macro to also check that if those cells (lets say K8) is populated but with say a 1, 2 or 3 rating, that another cell (say M8) must be also be populated (with a comment)? -- scheduler "Don Guillett" wrote: This will check to make sure 21 cells are not blank or "space barred". Then, it will do the original. It is NOT necessary to unhide the destination sheet or select it. Sub doitright() 'check range If Evaluate("SumProduct(--(Len(trim(L8:L28)) 0))") < 21 Then MsgBox "Please complete All items" Exit Sub End If 'do the copy insert Range("L8:L28").Copy Sheets("Ratings Results").Columns("F").Insert Application.CutCopyMode = False MsgBox "Thank you for completing the Customer" & _ "Service Satisfaction Survey", vbOKOnly End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... The existing code is pretty simple, it just copies the responses in cells L*"L28 into a hidden sheet (for result compilation), hides the sheet that person was using and saves and closes the workbook. It is: Private Sub CommandButton1_Click() ActiveSheet.Select Range("L8:L28").Select Selection.Copy Sheets("Ratings Results").Visible = True Sheets("Ratings Results").Select Sheets("Ratings Results").Columns("F:F").Select Selection.Insert Shift:=xlToRight Sheets("Ratings Results").Range("A27:C27").Select Sheets("Ratings Results").Visible = False ActiveSheet.Select Range("E4:J4").Select ActiveWorkbook.Save ActiveWindow.SelectedSheets.Visible = False MsgBox "Thank you for completing the Customer Service Satisfaction Survey", vbOKOnly ActiveWorkbook.Save ActiveWorkbook.Close End Sub -- scheduler "Don Guillett" wrote: As ALWAYS, post YOUR code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "scheduler" wrote in message ... I currently have a survey that is used by people nationally to rate a department. They need to answer 9 questions then click "Submit" which has a macro attached to save and hide their worksheet. How can I add into the macro, code to stop the macro from running and display an error box if someone tries to submit without having populated all the fields (eg: cell L8)? -- scheduler |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation Cell Macro | Excel Discussion (Misc queries) | |||
| E-mail macro - Field Validation | Excel Discussion (Misc queries) | |||
| How can I get a validation to run in a macro on a shared workbook? | Excel Discussion (Misc queries) | |||
| Validation code for macro to run | Excel Discussion (Misc queries) | |||
| Validation!! Formula!! Macro?? | Excel Worksheet Functions | |||
Hybrid Mode
