Transfer activex textbox value to table on another sheet

[Mikeymike_W]

Hi,

I have a sheet called "CLP", there are several activex textboxes in this sheet.
I want to use VBA to transfer the values of each textbox into a specific named range on a table ("Data") that's on sheet "CLP Client Data"

So for example on sheet "CLP" there is a textbox named "TBDate", i want the value of this to be placed in the table "Data" under the named row "Date".

I have done something similar in other sheets but never from an activex textbox.
I have the code below but it throws up an error once it gets to the first line regarding the textboxes.

Thanks in advance for all your help,

Mike

Dim rw As Long    'next available row
Dim sPath As String, i As Long
Dim ws As Worksheet
        Set ws = Worksheets("CLP Client Data")
   
    Sheets("CLP Client Data").Select
  
    ActiveSheet.Range("A3").Select
   
      ws.ListObjects("Data").ListRows.Add
        rw = Split(ws.ListObjects("Data").DataBodyRange.Address, "$")(4)

      'put the text box values in this row
      With ws
     
      .Cells(rw, "Date").Value = TBDate.Value
      .Cells(rw, "ClientID").Value = TBName.Value

 

[NoSparks]

try

      .Cells(rw, "Date").Value = Sheets("CLP").OLEObjects("TBdate").Object.Value

 

[Mikeymike_W]

Hi Nosparks,

Thanks very much for the help, tried it out and it works a charm, thanks you.

Part of the problem was that i had it in a module too so have moved it into the sheet.

Thanks very much for your help,

Mike