Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

excel vba position button to last empty row

Posted on 2011-09-05
6
341 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
ID: 36484535
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 46

Expert Comment

by:Martin Liss
ID: 36484756
 
 '~~> 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
ID: 36485501
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
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.

 
LVL 3

Author Closing Comment

by:Mutsop
ID: 36486859
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
ID: 36486878
never mind found it, had to change Range to Cells :)
0
 
LVL 59

Expert Comment

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

Ie you have mixed the syntax requirements
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

792 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