Search code examples
excelvbarowsoffset

Create A New Row and Select A Cell

I am trying to give users the ability to add rows to a form, however I need them to do it programmatically so that the new row contains a checkbox with a macro already assigned to it.

My solution has been to insert a row with an InputBox, which works great.

Dim rowloc As String

rowloc = InputBox("Location to Insert Row", "What row do you want to insert 
the new row ABOVE?")

Rows(rowloc).Select
Selection.Insert Shift:=xlDown

But I am having a heck of a time going to column B in that new row and inserting the checkbox. I don't have issues with making a new button in VBA, it's just getting to the other cell that's the issue.

Rows(rowloc).Offset(0, 1).Select

doesn't work either. Any helpful hints or solutions?

Solution

  • Thw Rows(rowloc) is a Range that includes all the cells in the row rowloc. If you need to identify a cell in this Range (in this row) then, for example,

    Rows(rowloc).Cells(2).Select ' in column 2 ("B")

    or, equivalently,

    Rows(rowloc).Parent.Cells(rowloc,2).Select ' in column 2 ("B")

    or

    ActiveSheet.Cells(rowloc,2).Select ' in column 2 ("B")
     ' might not be the same worksheet though, depending on your setup

    etc.