Float button on current row

V

vabtroni

New Member
Joined
Aug 1, 2017
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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"


Code: 
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
 
Upvote 0
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.

Code: 
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.
 
Upvote 0
Disregard my last post, then, we wrote at the same time.

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

Code: 
ActiveSheet.Shapes.Range("but_ENV_AVARIAS").Visible = True
 
Upvote 0
So did my script in post #7 not work for you???
You need to select a cell in column "I"
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
395
AndyEd
A
V
  • Question
Copy content to a selected row
2
Replies
10
Views
426
vabtroni
V
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
H
  • Question
Problems Inserting Rows
Replies
1
Views
349
My Aswer Is This
My Aswer Is This
K
  • Question
VBA Code Filter
Replies
1
Views
691
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,914
Messages
6,122,211
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top