+ Reply to Thread
Results 1 to 5 of 5

Inserting Row Macro using Command Button (ActiveX) relative to Button

  1. #1
    Registered User
    Join Date
    10-28-2014
    Location
    Hickory, NC
    MS-Off Ver
    2013
    Posts
    4

    Cool Inserting Row Macro using Command Button (ActiveX) relative to Button

    Hello,

    I need help with the following Macro. When the button is clicked I need the code to set focus (make active cell) on the cell the button is on allowing rows to be inserted and with the button always setting focus on the same row as the button is on.

    My example only references Cell "F9" instead of always referencing the cell the button is on.

    Sub InsertCopyRowClearContents2()
    ActiveSheet.Range("F9").Select
    ActiveCell.Offset(1, 0).EntireRow.INSERT
    ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
    ActiveCell.Offset(0, 0).Select
    Selection.ClearContents
    ActiveSheet.Range("F9").Value = "Enter Below"
    ActiveSheet.Range("F10").Select
    End Sub

    Please provide the code if possible.

    Thanks, Marc Bailey
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    01-08-2016
    Location
    Cagayan De Oro City, Philippines
    MS-Off Ver
    2013
    Posts
    152

    Re: Inserting Row Macro using Command Button (ActiveX) relative to Button

    Hi

    You can get a cell reference to the cells using button.TopLeftCell and button.BottomRightCell

    i recommend using bottomrightcell and not topleftcell because if you filter rows (hide rows) that contain buttons all these hidden buttons will have the same cell reference as the first none filtered row below the hidden rows..... dont ask my why i have no idea , but if your cells are bigger than the buttons and your button are contained inside (not touching the cell borders) then bottomrightcell will work fine..... if they overlap borders you will have to use offset...

    what will happen is some random cell between the hidden cells and the visible one will be referenced.

    If you will never use a filter then topleftcell is fine to use

    Thanks
    Paul S.

  3. #3
    Registered User
    Join Date
    10-28-2014
    Location
    Hickory, NC
    MS-Off Ver
    2013
    Posts
    4

    Re: Inserting Row Macro using Command Button (ActiveX) relative to Button

    Paul,

    Thanks for the response. I am not familiar with VBA code. Code you show me how to incorporate the button.TopLeftCell in to my code so I can remove the Range("F9") and Range("F10").

    Sub InsertCopyRowClearContents2()
    ActiveSheet.Range("F9").Select
    ActiveCell.Offset(1, 0).EntireRow.INSERT
    ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
    ActiveCell.Offset(0, 0).Select
    Selection.ClearContents
    ActiveSheet.Range("F9").Value = "Enter Below"
    ActiveSheet.Range("F10").Select
    End Sub

    Thanks again,

    Marc

  4. #4
    Forum Contributor
    Join Date
    01-08-2016
    Location
    Cagayan De Oro City, Philippines
    MS-Off Ver
    2013
    Posts
    152

    Re: Inserting Row Macro using Command Button (ActiveX) relative to Button

    Sure its very simple

    Please Login or Register  to view this content.
    Just rename MyButton to the name of your button...

    Paul S.

  5. #5
    Registered User
    Join Date
    10-28-2014
    Location
    Hickory, NC
    MS-Off Ver
    2013
    Posts
    4

    Re: Inserting Row Macro using Command Button (ActiveX) relative to Button

    Hey Paul,

    Sorry for not getting back quicker - I've been traveling...

    I still can get it to work - getting an error "Object Required".

    Any suggestions. I appreciate your help.

    Thanks,
    Marc

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. ActiveX Command Button
    By kwrigh6 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-17-2015, 09:56 AM
  2. Run activex command button via macro
    By dhaval123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2013, 06:07 AM
  3. [SOLVED] ActiveX command button error on Mac!!!
    By devpp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2013, 03:09 AM
  4. Error: Referencing using activex command button
    By ExcelGal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2012, 01:52 PM
  5. [SOLVED] Editing a Command Button - ActiveX?
    By Jennster in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2012, 08:19 AM
  6. ActiveX Command Button Text Looks Bad
    By brownsl in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-07-2012, 07:35 AM
  7. [SOLVED] ActiveX command button
    By Dr.Schwartz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2005, 04:05 PM

Tags for this Thread

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