# move button for adjacent active cell from up to the  bottom



## leap out (Dec 14, 2022)

Hi

Find a way to shorten the time instead of going up and moving the button every time when standing on an active cell.
The button is in the column *I  *of the cell *I3 *and its name is* button 1*, and the active cell is in the column *B*. When moving from one row to another, the button must move so that it is in the same row of the active cell chosen from the column *B*.
For example, if I select the cell *B12*, then the button should be for the corresponding cell *I12,*if I select the cell *B2*, then the button should going up for the corresponding cell *I2*
is  there  any  way  to achieve it,please?


----------



## nemmi69 (Dec 14, 2022)

Based on button being called "button 1" the code goes in to that worksheets code page for "Worksheet_SelectionChange"


```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target.Offset(0, 1)
        Me.Shapes("button 1").Top = .Top
        Me.Shapes("button 1").Left = .Left + 2
    End With
End Sub
```


----------



## leap out (Dec 14, 2022)

almost work ! but  I need  fixing  problems when  I  select  the  cell from  column B will  move the  button beside in column B,

 almost  in column C  but  should keep  in  column I  because  the  button is  existed in column I .

also  shows error application defined error  in  the  first line  when  try  select  the  whole  row.

just  curiosity   if  I  have  multiple  buttons   how  can  I  put  in array  instead  of  repeat macro for each  button.


----------



## Jimmypop (Dec 14, 2022)

leap out said:


> almost work ! but  I need  fixing  problems when  I  select  the  cell from  column B will  move the  button beside in column B,
> 
> almost  in column C  but  should keep  in  column I  because  the  button is  existed in column I .
> 
> ...



Hi there

Maybe try


```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    With Target.Offset(0, 7)
        Me.Shapes("Button 1").Top = .Top
        Me.Shapes("Button 1").Left = .Left
    End With
End Sub
```

just  curiosity   if  I  have  multiple  buttons   how  can  I  put  in array  instead  of  repeat macro for each  button. *On this one I am not sure...maybe someone else has a suggestion?*


----------



## nemmi69 (Dec 14, 2022)

This will always keep it in "I"


```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target.Offset(0, 9 - Target.Column)
        Me.Shapes("Button 1").Top = .Top
        Me.Shapes("Button 1").Left = .Left + 2
    End With
End Sub
```


----------



## nemmi69 (Dec 14, 2022)

Will move 3 buttons in unison


```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NofB As Integer
    For NofB = 1 To 3
        With Target.Offset(2 - NofB, 9 - Target.Column)
            Me.Shapes("Button " & NofB).Top = .Top
            Me.Shapes("Button " & NofB).Left = .Left + 2
        End With
    Next
End Sub
```


----------



## leap out (Dec 15, 2022)

@Jimmypop 
thanks 
 about  the  error can  I  use  this ?

```
If Target.CountLarge > 1 Then Exit Sub
```
then  will gone  the error.


----------



## leap out (Dec 15, 2022)

@nemmi69 

thanks  for  two  versions .
sorry  I found  some  problems .
can  I  move  the  button   to  last  empty  column  doesn't  contain  the  topic header in row1  , because  the  button  wen  move  will cover  over  data  should  be  in  last empty  column to  see  how  can  I change  my  data 
every  time  I  will  add columns  with  headers   so  the  button  should  be far away from my data

for  instance  if  the  column  EX   contains  header  then  should  move  to  next  column  doesn't  contain header and  data in  column EY , so  need  change  this  line 
	
	
	
	
	
	



```
With Target.Offset(0, 9 - Target.Column)
```

as  to  the  array  works  as  expected


----------



## nemmi69 (Dec 15, 2022)

leap out said:


> @nemmi69
> 
> thanks  for  two  versions .
> sorry  I found  some  problems .
> ...


the 9 in the code is holding it to column "I" as you asked. To offset the row change the 0 and column the 9.


----------



## leap out (Dec 15, 2022)

> the 9 in the code is holding it to column "I" as you asked. To offset the row change the 0 and column the 9.


yes  but  I  explained my  problem 
so  I  look for  this  idea  how  add  inside  the  code  to  move to  last  empty  column  like  this 

```
With Target.Offset(0, Target.Columns.End(xlToLeft))
```
but  doesn't  work for me


----------



## leap out (Dec 14, 2022)

Hi

Find a way to shorten the time instead of going up and moving the button every time when standing on an active cell.
The button is in the column *I  *of the cell *I3 *and its name is* button 1*, and the active cell is in the column *B*. When moving from one row to another, the button must move so that it is in the same row of the active cell chosen from the column *B*.
For example, if I select the cell *B12*, then the button should be for the corresponding cell *I12,*if I select the cell *B2*, then the button should going up for the corresponding cell *I2*
is  there  any  way  to achieve it,please?


----------



## nemmi69 (Dec 15, 2022)

Try



```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastColFind As Long

    LastColFind = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    With Target
        Me.Shapes("Button 2").Top = .Top
        Me.Shapes("Button 2").Left = ActiveSheet.Cells(1, LastColFind + 1).Left + 2
    End With
End Sub
```


----------



## leap out (Dec 15, 2022)

fantastic !
this  is  exactly  what  I  want .
many  thanks  for  your  help


----------



## nemmi69 (Dec 15, 2022)

No problem :D


----------

