extract data from worksheet textbox to another worksheet in same workbook

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
136
Hi there,

I have code that I have used succesfully in a userform to take the text from the userform text box and place it into a new row in a table within a sheet called "Readings".

I now have another sheet called "LIFE PURPOSE" and would like to transfer data from this into the same table in the "READINGS" sheet.
In the Life Purpose sheet I have Activex textboxes, i'm sure the code will still work fine with those however I also have standard worksheet textboxes and was wondering how to reference each of these textboxes so i can copy the data within each into a cell in a new line as the code does for the userform.... hope that makes sense.

Here is the code I have so far and thanks for your help,

Michael

VBA Code:
 Dim rw As Long    'next available row
 
 Dim ws As Worksheet
        Set ws = Worksheets("Readings")
   
    ActiveSheet.Range("A2").Select
    
      'get the next avialable row in Sheet1
      'rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      ws.ListObjects("Data1").ListRows.Add
        rw = Split(ws.ListObjects("Data1").DataBodyRange.Address, "$")(4)
 
      'put the text box values in this row
      With ws
      
      .Cells(rw, "A").Value = Me.TBLPName.Value
      .Cells(rw, "B").Value = Me.TBLPDate.Value
      .Cells(rw, "C").Value = Me.TBLPName.Value
      .Cells(rw, "D").Value = "Life Purpose"
      
      
      .Cells(rw, "Y").Value = TextBoxA.Text & "; " & ComboxareaA.Text
      .Cells(rw, "Z").Value = TBSenseA.Value
      .Cells(rw, "AA").Value = TBDiscoveryA.Value
      
      .Cells(rw, "AB").Value = TextBoxB.Text & "; " & ComboxareaB.Text
      .Cells(rw, "AC").Value = TBSenseB.Value
      .Cells(rw, "AD").Value = TBDiscoveryB.Value
      
      .Cells(rw, "AE").Value = TextBoxC.Text & "; " & ComboxareaC.Text
      .Cells(rw, "AF").Value = TBSenseC.Value
      .Cells(rw, "AG").Value = TBDiscoveryC.Value
      
      .Cells(rw, "AH").Value = TextBoxD.Text & "; " & ComboxareaD.Text
      .Cells(rw, "AI").Value = TBSenseD.Value
      .Cells(rw, "AJ").Value = TBDiscoveryD.Value
      
   End With
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,514
Office Version
  1. 2013
Platform
  1. Windows
The reason I use Active-X TextBox.
Rich (BB code):
With Sheets(1)
MsgBox .Shapes("TextBox 1").TextFrame.Characters.Text
End With
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
136
Hi JLGWhiz, thanks for your reply.

For some reason the activex textboxes were lagging even though theyre not connected to any code.

Can I ask why there is a msgbox prompt in the code?

I used the code you provided without the msgbox me.shapes.... worked perfectly.

Thanks again for all your help!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,514
Office Version
  1. 2013
Platform
  1. Windows
The message box is just a test tool. Glad it worked for you.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,113,743
Messages
5,543,950
Members
410,586
Latest member
acadavid86
Top