Float button on current row

[vabtroni]

Greetings everyone.

I have a button called "but_ENV_AVARIAS" wich runs a macro, and I need it to float on the current selected row near column I (with a fixed horizontal position), except if:
- current row is above row 8
- current row is below row 30007
...meaning, if selected row is not between rows 8 and 30007, I would like that button to become invisible.

Is there any chance I could put it on VBA?

Best regards,
Vasco.

 

[My Aswer Is This]

Is this a shape your using as a button?
If not what type button is it a activex command button?

And you do know you can run a script by just double clicking on a cell if you want. Double click on I2 or I5 Or I7 and your script could run doing just that no need for a button. This only requires one small script. Works on all cells in a column depending on which cell you double click on.

 

[vabtroni]

I can explain a little further:

Freeze panes is not an option, because they are already frozen and with a bunch of buttons on it

The button I wish to float runs a macro that sends the values on the cells of the current row by email via Outlook, and it is already working on perfection. I would love that the button could float allways on current row, by the end of the data entry fields (column I is the first column available and therefore not used to entry data for the email). If the selected row is not on the range 8 to 30007, I would love that button to become invisible.

 

[vabtroni]

Is this a shape your using as a button?

It is not activex, I believe it is a shape, I mean, a simple command button.

 

[My Aswer Is This]

How did you put this button on your sheet?

It is not activex, I believe it is a shape, I mean, a simple command button.

 

[vabtroni]

How did you put this button on your sheet?

Developer tab > Insert > Form control > Button

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This only happens when you select some cell in column "I"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("I:I")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
If ActiveCell.Row < 8 Or ActiveCell.Row > 30007 Then
ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Visible = False
Else
ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Visible = True
End If
ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Top = ActiveCell.Top
ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Left = ActiveCell.Offset(, 3).Left
End If
End Sub

 

[vabtroni]

I found this code somewhere on the web, and it seems to work, but it only moves the button when I select a cell after scrolling up or down, and it places the button on a relative position to the 0,0 coordinates of the current view.

I'm putting this code below, as I believe it may help finding a proper solution to what I want.

I believe it may work regarding the horizontal alignment, it is just a matter of finding a proper value that places the button where I want it.

However, regarding the vertical position, I need the button to be allways in-line with the current selected row, regardlessly if I scroll the sheet or not.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cmdBtn As Shape
    
    Set cmdBtn = Me.Shapes("but_ENV_AVARIA")
    
    cmdBtn.Left = ActiveWindow.VisibleRange.Left + 0.6 * ActiveWindow.VisibleRange.Width
    cmdBtn.Top = ActiveWindow.VisibleRange.Top + 0.6 * ActiveWindow.VisibleRange.Height
    
End Sub

Looking at the way it works on the sheet, I believe it may work regarding the horizontal alignment, it is just a matter of finding a proper value that places the button where I want it.

However, regarding the vertical position, I need the button to be allways in-line with the current selected row, regardlessly if I scroll the sheet or not.

 

[vabtroni]

Disregard my last post, then, we wrote at the same time.

Already tried your code, and it returns Runtime error 1004, highlighting this line:

ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Visible = True

 

[My Aswer Is This]

So did my script in post #7 not work for you???
You need to select a cell in column "I"