Inserting Textbox After Last Row w/ Data

[maize]

The question I have involves the placement of inserting a textbox. For example, rather than specifying a place in the spreadsheet by 'points' via the left and/or top property of the AddTextBox method, is it possible to place the textbox a row or two below the last row with data?

I have this written to find the last row w/ data, which may not be totally correct

Function LastRowIndex(ByVal w as Worksheet, ByVal col as Variant) as Long
Dim r as Range
Set r = Application.Intersect(w.UsedRange, w.Columns(col))
If Not r is Nothing Then
    Set r = r.cells(r.Cells.count)

    If IsEmpty(r.Value) Then
       LastRowIndex = r.End(xlUp).row
    Else 
       LastRowIndex = r.row
    End If
End If
End Function

Is there anyway to follow up that function to include adding a textbox after it rather than specifying a 'static' location?

Thanks!

 

[rudfaden]

Tjek this link
http://www.ozgrid.com/forum/showthread.php?t=56150&page=1

 

[maize]

Tjek this link
http://www.ozgrid.com/forum/showthread.php?t=56150&page=1

Unless I'm not understanding it fully, this didn't help me out a whole lot. I tried implementing it into my textbox code:

ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, Left:=125, Range(LastRowIndex).Top, Width:=500, Height:=100).TextFrame.Characters.Text = "My Text Here."
With Selection.Characters(Start:=1, Length:=216).Font
    .Name = "Arial"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = false
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
End With
Range("I15").Select
End Sub

The problem may be in my LastRowIndex function (see in my original post)...?

 

[rudfaden]

I am no expert in shapes, however I see two potentiol probpems with your code. First, you do not call the functions for LastRowIndex

Second you work with a selection, but you do not select anything.

 

[maize]

Even after getting those problems corrected, it's still not working correctly. I keep getting a 'Named Parameter Expected' error.

There's got to be a way to find the last row of data and insert a textbox after it.

 

[Noz2k]

If you know which column your last row of data was in can you use something like this to make the first empty row the activecell.

Range("A1").Select
 
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
 
 
Loop Until IsEmpty(ActiveCell) = True
 
*Your code to insert textbox

 

[maize]

If you know which column your last row of data was in can you use something like this to make the first empty row the activecell.

Range("A1").Select
 
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
 
 
Loop Until IsEmpty(ActiveCell) = True
 
*Your code to insert textbox

I don't think this will work in my situation due to having 2 blank rows between each 'set' or block of rows to make it easier for the user to read, will it?

Is there anyway to search for the first instance of 3 blank rows rather than the first empty row?

 

[Noz2k]

No I don't think it will, sorry.

I think you could do that yes. I think in terms of the logic you would have to take the row number of the activecell at the end of running that code. Run the code 3 times (each time taking the row number) and then check whether the last instance of the row number is >3 above the first instance of the row number.

I'm pretty new to VBA myself though, so unfortunately, can't help with the coding that, sorry

 

[maize]

Understandable. Thanks anyway for trying to help. It's greatly appreciated. I'll keep researching and trying different things. I didn't imagine I'd be having trouble figuring this one out... :S