Solved

excel vba position button to last empty row

Posted on 2011-09-05
6
328 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Mutsop
  • 3
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
 
 '~~> 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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Author Closing Comment

by:Mutsop
Comment Utility
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
 
LVL 3

Author Comment

by:Mutsop
Comment Utility
never mind found it, had to change Range to Cells :)
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Either cells(row, column)
Or
Range("a"& re)

Ie you have mixed the syntax requirements
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now