代码之家 › 专栏 › 技术社区 › Piecevcake

Excel VBA或公式将多个ActiveX文本框的值复制到相邻单元格

vba copy-paste textbox excel

Piecevcake · 技术社区 · 7 年前

我收到了一份工作簿,其中有很多ActiveX文本框,列中有我需要使用的值。有没有办法获取这些值并将其放在每个框位置右侧的列中?

它们被锁定并“随细胞移动”。它们在选择窗格中显示为“HTMLText nnn”。每个文本框中都有一个值。

我在Kutools上试过这个(谢谢他们, the page ),它看起来应该可以工作,但什么也没有发生(没有复制值,没有删除框):

Sub TextboxesToCell_Kutools()

    Dim xRg As Range
    Dim xRow As Long
    Dim xCol As Long
    Dim xTxtBox As TextBox
     
    Set xRg = Application.InputBox("Select a cell):", "Kutools for Excel", _
        ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
    xRow = xRg.Row
    xCol = xRg.Column
     
    For Each xTxtBox In ActiveSheet.TextBoxes
        Cells(xRow, xCol).Value = xTxtBox.text
        xTxtBox.Delete
        xRow = xRow + 1
    Next
     
End Sub

1 回复 | 直到 3 年前

Piecevcake 7 年前

找到了!

    Sub H___CopyFormsHTMLBoxToSameCell()
'https://windowssecrets.com/forums/showthread.php/169760-Text-box-value-from-a-web-page-copy-and-paste (modified)

Dim OLEObj As OLEObject 'from the control toolbox toolbar
Dim DestCell As Range
Dim wks As Worksheet
Set wks = ActiveSheet

With wks
    For Each OLEObj In .OLEObjects
        If TypeOf OLEObj.Object Is msforms.TextBox Then
            Set DestCell = OLEObj.TopLeftCell.Offset(0, 0)
            DestCell.Value = OLEObj.Object.Value
             OLEObj.Delete 'vp added
'    ActiveCell.Activate doesnt center cell!

        End If
    Next OLEObj
End With

MsgBox "Done. Home, find, selection pane check for other shapes; f5, objects, Ctrl-click unselect pictures, delete."

End Sub

推荐文章