Formatting selected cells with a subform Attempt 3. I have been trying to come u
ID: 3561979 • Letter: F
Question
Formatting selected cells with a subform
Attempt 3.
I have been trying to come up with a workable solution to a project and hope that this could be the easiest way to achieve my goal.
I have a spreadsheet which contains employee data in columns A, B and C after that the columns are set out like a calendar for a specific month. I would then block colour days where that employee is carrying out a certain activity. Due to the amount of time it takes to select the cells, select the correct colour and then adding into that errors with selecting the wrong colour my idea is to select the required cells and then select a Command Button (Add) this will bring up a Userform (Add Activity). It will give me a drop-down menu of the available activities.
When an activity is selected, the highlighted cells on the spreadsheet are blocked in the correct colour. I would a letter to be added into these cells also, which would correspond to the first letter of the activity (This is purely so I can work out Stats of who is doing what at a certain time).
I have the SpreadSheet and Userform but not the coding to achieve the goal.
Please help
Explanation / Answer
First, I would consider using Conditional Formatting (CF) to all of the cells that you want to be shaded based on activities. This would allow you to not only use the UserForm, but to make entries/changes "on the fly" and have the colors appear.
You'd use the CF option "format only cells that contain" and for each entry you would choose "is equal to" and enter a letter corresponding to the activity (as C, H, M/P, S). That takes care of the consistency of your color scheme.
Now, as for the userform, you could put this code in for the [OK] button's code, assuming that the combo box's name is ComboBox1 (change as needed) and that the [OK] button is named CommandButton1
To use it, you'd first select the cells to have the entries placed into, then bring up your userform, make a selection in the Combo Box and click the OK to finalize it and remove the UserForm from view:
Private Sub CommandButton1_Click()
Select Case ComboBox1.Text
Case Is = "Course"
Selection.Value = "C" ' put C into selected cells
Case Is = "Holiday"
Selection.Value = "H" ' put H into selected cells
Case Is = "Maternity/Paternity" ' check spelling
Selection.Value = "M/P" ' put M/P into selected cells
Case Is = "Sick"
Selection.Value = "S" ' put S into selected cells
Case Else
'ignore empty or other entry
End Select
Me.Hide
End Sub