+ Reply to Thread
Results 1 to 3 of 3

Row reference relative to Button on Spreadsheet

  1. #1
    Registered User
    Join Date
    10-11-2004
    Posts
    5

    Row reference relative to Button on Spreadsheet

    A simple explanation (I hope) of what I am trying to achieve follows:

    I have a macro button that is placed in cell E1 for example that when pressed brings up a form with two text boxes. The information that is entered into these two text boxes is inserted into cell A1 and C1 respectively when OK is pressed.

    Now I need to be able to copy this row (including the macro button) to row 4. I want the macro button to behave the same way except I want it to be aware of it's position on the spreadsheet (ie. Row 4) and the values that are entered are placed in cells A4 and C4.

    Is there a way for the macro to pick up where in the spreadsheet the button was pressed so that the info entered into the form appears in the correct row?

    At the moment I create a 'new' macro each time changing the cell reference which creates more work and more code.

    Hope someone can help.

    Thanks

    Andy

  2. #2
    Norman Jones
    Guest

    Re: Row reference relative to Button on Spreadsheet

    Hi Andy,

    VBA provides two properties which are of use to you here, namely Caller and
    TopLeftCell.

    The caller property can be used to return the button which is clicked.

    The button's TopLeftCell property can be used to return the cell which lies
    under the upper left corner of the button.

    See VBA help for additional information on these useful properties.

    To copy the text from two text boxes into the two cells laterally adjacent
    to the button of interest, assigning a macro like the following to your
    initial button

    '===============>>
    Sub Tester()
    Dim TB1 As TextBox
    Dim TB2 As TextBox

    Set TB1 = ActiveSheet.TextBoxes("Text Box 1")
    Set TB2 = ActiveSheet.TextBoxes("Text Box 2")

    With ActiveSheet.Buttons(Application.Caller).TopLeftCell
    .Offset(0, -1).Value = TB1.Text
    .Offset(0, 1).Value = TB2.Text
    End With
    End Sub
    '<<===============

    ---
    Regards,
    Norman



    "AndyR" <[email protected]> wrote in
    message news:[email protected]...
    >
    > A simple explanation (I hope) of what I am trying to achieve follows:
    >
    > I have a macro button that is placed in cell E1 for example that when
    > pressed brings up a form with two text boxes. The information that is
    > entered into these two text boxes is inserted into cell A1 and C1
    > respectively when OK is pressed.
    >
    > Now I need to be able to copy this row (including the macro button) to
    > row 4. I want the macro button to behave the same way except I want it
    > to be aware of it's position on the spreadsheet (ie. Row 4) and the
    > values that are entered are placed in cells A4 and C4.
    >
    > Is there a way for the macro to pick up where in the spreadsheet the
    > button was pressed so that the info entered into the form appears in
    > the correct row?
    >
    > At the moment I create a 'new' macro each time changing the cell
    > reference which creates more work and more code.
    >
    > Hope someone can help.
    >
    > Thanks
    >
    > Andy
    >
    >
    > --
    > AndyR
    > ------------------------------------------------------------------------
    > AndyR's Profile:
    > http://www.excelforum.com/member.php...o&userid=15203
    > View this thread: http://www.excelforum.com/showthread...hreadid=474968
    >




  3. #3
    Andy
    Guest

    Re: Row reference relative to Button on Spreadsheet

    Norman, fantastic, the TopLeftCell property is perfect for what I need.
    Thanks

    Andy


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1