excel vba position button to last empty row

Hi,

Is there a way to have a button on a sheet be moved each time a row has been filled (specific column)?

So let's say a the button is around row 10, after some inputs in D10 the button moves down to row 11 and so on....

Is that possible?

Regards
LVL 3
MutsopAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris BottomleyConnect With a Mentor Commented:
For what it's worth the following is broadly similar to my first post but two differences:

1. Sets the row according to the last data in a specific column (A as presented) i.e the first version uses the last row with data at all irrespective if subsequently deleted.  This version targets the last row in the specific column.

2. Sets the left position of the button ... in columns B & F, (i.e. left of columns C & G)

Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long

'    rw = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    rw = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

    With Me.CommandButton1
        ' ActiveXbutton
        .Top = ActiveSheet.Rows(rw + 1).Top
        .Left = ActiveSheet.Columns("C").Left
    End With
    With Me.Shapes("Button 1")
         ' Form button
        .Top = ActiveSheet.Rows(rw + 1).Top
        .Left = ActiveSheet.Columns("G").Left
    End With
End Sub

Open in new window

0
 
Chris BottomleyCommented:
PLace the following in the code page for the sheet and then on edit to the sheet the button will move ... depending on activex or forms type buttons then use the appropriate name for your button. Right click the tab and select view code to display the editor.

Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long

    rw = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Me.CommandButton1.Top = ActiveSheet.Rows(rw + 1).Top 'ActiveXbutton
    Me.Shapes("Button 1").Top = ActiveSheet.Rows(rw + 1).Top ' Form button
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
 
 '~~> Starting Row
    i = 2
    '~~> Find the first empty Row
    Do Until Len(Trim(Range("A" & i))) = 0
        i = i + 1
    Loop

    
    CommandButton.Top = Rows(1).Height * i

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MutsopAuthor Commented:
This works like a charm :)

But one other thing, So I open a form to input data in a cell.
So i tried using:
Private Sub CommandButtonBtw_Click()
    Dim rw As Long
    rw = ActiveSheet.Range("D" & ActiveSheet.Rows.Count).End(xlUp).row
    
    ActiveSheet.Range(rw, "D").Value = TextBoxBtw.Value
    
    Me.Hide
End Sub

Open in new window


But it gives me a 1004 error on
ActiveSheet.Range(rw, "D").Value = TextBoxBtw.Value

Open in new window


Any ideas?
0
 
MutsopAuthor Commented:
never mind found it, had to change Range to Cells :)
0
 
Chris BottomleyCommented:
Either cells(row, column)
Or
Range("a"& re)

Ie you have mixed the syntax requirements
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.