VBA Code to put value of activeX textbox into a cell on another sheet

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!

forum.ozgrid.com/index.php?attachment/68766/

Re: VBA Code to put value of activeX textbox into a cell on another sheet

Ok I'm trying a different tactic...
I've added a couple of helper cells/formulas to determine the NEW cell address that I want to use for the date I've typed into my textbox.
[table="width: 500, class: grid"]

L

M

17

Name

UDF to find name of employee selected from slicer

18

Semester

Shows semester selected from a drop down (data validation list) in cell B5

19

Cell Address

Formula looks up the cell address of the name in cell M17 (=ADDRESS(MATCH(M17,Table9[LFM Name],0)+ROW(Table9[[#Headers],[LFM Name]]),COLUMN(Table9[[#Headers],[LFM Name]]),4))

20

Row #

Formula removes the column heading from the cell address in cell M19 so I'm left with the row # (=RIGHT(M19,LEN(M19)-1))

21

Number of columns to move over

Looks up the semester in cell M18 and returns the # of columns to move over based on data in another table (=VLOOKUP(M18,Table18,2,FALSE))

22

New Column

Looks up the # of columns to move over and returns what the NEW column should be based on data in another table (=VLOOKUP(M21,Table12,2,FALSE))

23

New Cell Address

Takes the new column identified in cell M22 & adds the row # from cell M20 to give us the NEW cell address for the date in my textbox (=M22&M20)

24

I wasn't sure if there would be an issue pointing to a cell address that was the value of a formula (cell M23), so I've amended my code to copy the cell address in M23 and paste the value into cell M24 so I can use that

[/table]
So NOW, my code is:

Sub enter_eval_dates()
    Dim mycelladdress As Variant
    mycelladdress = Sheets("Update Eval Dates").Range("M24").Value
    
    Range("M23").Select
    Selection.Copy
    Range("M24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("EVALS").Range(mycelladdress).Value = TextBox1.Value
    
    ActiveWorkbook.SlicerCaches("Slicer_LFM_Name").ClearManualFilter
    
    Range("M24").Select
    Selection.ClearContents
    
    ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
    
    MsgBox "Date Entered"
End Sub

But it's STILL not working! I'm BEYOND stumped on this!