Hello everyone,
Management has asked me to create a form on excel for not so tech-savvy people to fill out (in other words dumb-proof) so they don't accidentally mess it up and so it essentially holds their hands in the process so that they don't skip over required information that needs to be input. I've already protected my sheet and made specific cells editable.
I think a macro can help me out, but I'm not sure if macros can just run by themselves like conditional formatting does or if it needs to be manually ran for it to work. I'll explain what I want to occur so you can better understand.
This form has line items with four columns: General Inspection Items, Code, Inspection Comments and Repair Comments.
I want the macro to do three things if "X" is selected in the Code column:
1) Highlight the corresponding Repair Comments cell for that line item as red.
2) A message box to appear letting the user know that the Repair Comments cell needs to be filled in with a brief description of some kind.
3) Making the corresponding Repair Comments cell required to be filled out; making the file unable to be saved or printed until it is filled (this might be the most important of the three).
Other things it should be able to do is remove the red highlight if they meet the requirement (e.g. fill in the Repair Comments cell with a brief description) so that it returns to white and I want the message box to specify which line item to fill. For example, line item 9 is "Vandalism: Damage, graffiti, glass, trash, etc.", so if it said the number and name of the line item that would be great.
Step one is not so important because I can just use conditional formatting to do this (which I have) and it even removes the highlight if its filled, but I figure why not just have everything in the macro if I can.
I tried to make a macro for the message box and succeeded, but I don't know how I can specify line items. Do I need to specify the range for the line items and list out each line item within the range?
Note: I believe I created a loop macro, but I noticed that the macro only works if I manually run it. In my test I filled the Code cell with an X but I did not receive the message box. I then ran the macro and then received the message box. Is there a way to automate the message box without having to manually run the macro? I don't expect other users who will actually fill out this form to run the macro themselves. If not then creating the macro will be rendered useless and I'll just have to stick with only conditional formatting as a guide.
This is my macro so far:
Sub Repair_Comments()
Dim Code As Range
For Each Code In Range("$H$15:$I$32")
If Code.Value = "X" Then
MsgBox ("Please include a repair comment.")
End If
Next Code
End Sub
I have included an PNG attachment of the sheet so you can see it for yourself. Note that the cells have been merged together. B15:G15 makes General Inspection Item 9 Vandalism for example.
Any advice or tips will help. Thank you to anyone who takes the time to read and analyze the situation and gives a response. It will be greatly appreciated.