I'm really stumped on this one!
I have an activeX control text box in my sheet in which the user will enter the evaluation date for the employee they chose from the slicer on the left. I want the user to be able to click the button below the textbox and have that date entered in the appropriate cell on the EVALS sheet.
In order to find the correct cell for the date to be entered in, I've added some helper formulas below my button:
Name = a UDF to enter the name of the employee selected from the slicer
Semester = the semester chosen from the data validation drop down in cell B5
Cell address = finds the cell address on the EVALS sheet of the name of the employee (which came from the UDF) in cell M17
Number of Columns to Move Over = looks up the number of columns to move over from column G in Table18 on the DATA sheet
Next I assigned the macro to my button with the following code which should, ideally, find the cell address of the employee chosen in the slicer and move the appropriate number of columns over from that cell address and enter the value of the date entered into the textbox, and then clear the slicer filter so the user can start over to enter the next evaluation date.
Sub enter_eval_dates() Dim mycelladdress As Variant Dim movecolumns As Integer mycelladdress = Sheets("Update Eval Dates").Range("M19").Value movecolumns = Sheets("Update Eval Dates").Range("M20").Value Sheets("EVALS").Range(mycelladdress).Offset(0, movecolumns).Value = TextBox1.Value ActiveWorkbook.SlicerCaches("Slicer_LFM_Name").ClearManualFilter ActiveSheet.OLEObjects("TextBox1").Object.Value = "" MsgBox "Date Entered" End Sub
Unfortunately, I can't get this to work.... I'm sure I'm missing something! If anyone can offer any help, I'd GREATLY appreciate it!!!
Here's a version of my workbook with fictitious names of course!