1

I have inherited a set of Excel files from my predecessor. The files consist of a single form with many sheets with sections that have been completed by other users. The input from users consists of data entered into cells and text boxes. There is a lot of data and different sheets with text boxes that I would like to summarise into a format I can work with easily.

I am not very familiar with macros in Excel, but I fudged together a macro that I can run on an empty, open Excel doc, that:

  1. asks for which file I want to open
  2. gets the 'Data' sheet and copies and pastes it into open active workbook
  3. closes the file I grabbed the data from

This covers all of the data that was entered into the cells.

I also want to populate some of the cells in the new workbook with the text from the text boxes. So in the end I have a summary of all of the data on a single sheet for each user. However, I am having issues finding any solutions online for how to gather the information from a text box successfully.

I have tried solutions based on suggestions from this thread, but I receive the error Run-time error '438': Object doesn't support this property or method : VBA/Macro code to get the value of textbox

Sub ffs()
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim strFile As String
    With Application.FileDialog(1) ' msoFileDialogOpen
        .Filters.Clear
        .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
    .InitialFileName = "D:\Macro testing area"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbCritical
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    Set wshT = ActiveSheet
    Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
    Set wshS = wbkS.Worksheets("Data")
    Set wshS1 = wbkS.Worksheets("Section 1")
    wshT.Range("A1:L88").Value = wshS.Range("A1:L88").Value
    ' Optional: close source workbook
    wbkS.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

Ideally I was hoping to add some additional code underneath this line of code, before the file is closed:

wshT.Range("A1:L88").Value = wshS.Range("A1:L88").Value

I would like to add the text box values from wbkS.Worksheets("Section 1") into specified cells. But my attempts have been unsuccessful, such as:

wshT.Range("J3").Value = wshS1.TextBox1_1.Value

I assume I am making a logical flaw in how Macros work, but I am having trouble finding a solution. Any advice would be greatly appreciated.

gfitz
  • 11
  • 2

0 Answers0