How to align command button with ActiveCell
Requirement:
Any thoughts how to align a commandbutton to the left of an active cell?
The Commandbutton inserts a row above the activecell, but as the sheet is many rows downs, the user would need to scroll up to click the commend button.
Freezing windows is not an option due the the makeup of the sheet.
The user is currently trying the following but its not ideal as the command button could still be better aligned:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error GoTo 0
With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
CommandButton2.Top = .Top + 200
CommandButton2.Left = .Left + 10
End With
End Sub
Solution:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With Me.OLEObjects("CommandButton1")
.Top = Target.Top
.Left = Target.Left - .Width
End With
Application.ScreenUpdating = True
End Sub
To move more to the left:
Code:
.Left = Target.Left = .Left + 10 - .Width
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro button to put date in selected cell within specific column |
| How to create a button to run formula on every sheet in workbook |
| How to create a macro assigned to the submit button on the "interface" sheet |
| How to unprotect command button |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
