为列中的每一行创建TextBox [英] Create TextBox for every row in a column

问题描述

我试图找到一个VBA代码，它将遍历表的特定列中的行，并为每个迭代行创建一个TextBox，并将其设置为该单元格的值。

来自此处的代码插入文本框并设置公式确实可以创建一个文本框，但是我需要修改它，以便遍历整个列。

我试图做一个这样的循环：

  Sub addTextBox（）
 
 Dim newshp As Shape 
 Dim newtb As TextBox 
 Dim i As Long 
 
对于i = 1到范围（F& Rows.Count）.End（xlUp）.Row 
 
设置newshp = ActiveSheet .Shapes.addTextBox _ 
（msoTextOrientationHorizo​​ntal，100，100，200，80）
设置newtb = newshp.OLEFormat.Object 
 newtb.Formula =Cells.Item（i，6） 
 
 Next i 
 
 End Sub 
  

所以， newtb.Formula =Cells .Item（i，6） - 这是我有问题的地方显然 .Formula 只能指静态单元格。

我真的很感谢任何关于如何使这个循环正确。

解决方案

Formula 应该是一个Excel公式。因此，您需要将Cells.Item（i，6）更改为= F&我。

  Sub addTextBox（）
 
 Dim newshp As Shape 
 Dim newtb As TextBox 
 Dim i As Long 
 
 For i = 1 To Range（F& Rows.Count）.End（xlUp）.Row 
 
设置newshp = ActiveSheet.Shapes.addTextBox _ 
（msoTextOrientationHorizo​​ntal，100，100，200，80）
设置newtb = newshp.OLEFormat.Object 
 newtb.Formula == F& i 
 
下一个i 
 
 End Sub 
  

这将解决您的直接问题，但是您将需要找出您想要的文本框放置在何处 - 在将它们全部放在彼此之间。

I am trying to find a VBA code which would iterate through rows in a specific column of a table and create a TextBox for each iterated row and set it equal to the value of that cell.

The code from here Insert a TextBox and set the formula does work to create one text box, but I need to modify it so that it iterates through the entire column.

I tried to make a loop like this:

Sub addTextBox()

Dim newshp As Shape
Dim newtb As TextBox
Dim i As Long

For i = 1 To Range("F" & Rows.Count).End(xlUp).Row  

Set newshp = ActiveSheet.Shapes.addTextBox _
    (msoTextOrientationHorizontal, 100, 100, 200, 80)
Set newtb = newshp.OLEFormat.Object
newtb.Formula = "Cells.Item(i, 6)"

Next i

End Sub

So, newtb.Formula = "Cells.Item(i, 6)" - this is where I have the problem; apparently .Formula can only refer to a static cell.

I would really appreciate any tips on how to make this looping correct.

解决方案

The Formula should be an Excel formula. So you need to change "Cells.Item(i, 6)" to "=F" & i.

Sub addTextBox()

    Dim newshp As Shape
    Dim newtb As TextBox
    Dim i As Long

    For i = 1 To Range("F" & Rows.Count).End(xlUp).Row

        Set newshp = ActiveSheet.Shapes.addTextBox _
            (msoTextOrientationHorizontal, 100, 100, 200, 80)
        Set newtb = newshp.OLEFormat.Object
        newtb.Formula = "=F" & i

    Next i

End Sub

This will fix your immediate problem, but you will then need to work out where you want the TextBoxes placed - at the moment you are placing them all on top of each other.

这篇关于为列中的每一行创建TextBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！