Buttons should float in the Worksheet

[Excel-ent]

Dear Experts,

I have buttons (macros) on top of my worksheets. The sheet might contain more than 1000 rows.

Now, what I want is that the buttons should remain constant on top (floating) horizontally and vertically even when the sheet is scrolled down or right etc.

An example would be Windows | Freeze Panes. But I need an alternative.

Any suggestions for the captioned would be appreciated.

Best Regards,

S h a n

 

[zilpher]

Shan

You can't really make a commandbutton (forms or otherwise) float, it needs a container, either a worksheet or a userform.

You could put your buttons on a userform and show it with it's modal property set to false. This will allow you to select cells and move about the form, whilst the userform is displayed.

To do that, create a form, put your buttons and their code on it, then select the form, and in it's properties window, find the ShowModal property. It is set to true by default, if you run the form now, it will take focus and not allow you to select any area other than on the form. Set the showmodal property to false and you can still use the worksheets etc, whilst the form is displayed.

ShowModal is also an optional property of the show method. UserForm1.Show is really Userform1.Show True. You can force it to be non modal by using Userform1.Show False.

You would either show your form on workbook open and unload it on workbook close, or maybe you only want it on certain sheets:

Private Sub Worksheet_Activate()
UserForm1.Show False
End Sub

Private Sub Worksheet_Deactivate()
UserForm1.Hide
End Sub

HTH

 

[Excel-ent]

" NOW This is What I was looking 4 "

You r an absolute miracle Zilpher.



S h a n

 

[HalfAce]

Hi Shan,
I like zilpher's solution, especially for multiple buttons like you're talking about. Here, though, is an example of a "floating" command button. It will not float with the scrolling of the screen because as zilpher stated, it needs a container. In this case it's the worksheet. It uses the worksheet_selection event to make the button follow the active cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 3 Then Exit Sub
With ActiveSheet.Shapes("CommandButton1")
.Top = [A1]
.Left = [A1]
.Top = Target.Offset(-2).Top
.Left = Target.Offset(, -2).Left
End With
End Sub

I saw this once from (I think) either TommyGun or maybe Tom Urtis, I don't remember which.

 

[Excel-ent]

Hi HalfAce,

undoubtedly, even this solution is pretty fine. However, I could assign it on 1 Commandbutton name Commandbutton1 only. When i try to assign it on 2nd Button i.e. Commandbutton2 on the same Worksheet , A debug error appears.

Could you tell me if it's possible to assign it on more than 1 button, how to assign & what might need to be changed in the code if i am assigning it on Commandbutton2.

& could i please know what Containers mean as Zilpher stated.
-------------------------------------------------------------------------------------
can't really make a commandbutton (forms or otherwise) float, it needs a container

Best Regards,

S h a n

 

[zilpher]

I did consider moving buttons with the selectionchange and HalfAces code is great, but it didn't work with the mouse wheel (obviously), was difficult with more than one button, and annoyed me in the way the button jumped all over the place. I seem to remember that I had a problem with screen resolution when I tried it years ago, but that might be another issue. The userform just seemed easier to me, and I'm all for that

Not sure why you are getting an error, it's probably the way the code has been implemented in your workbook

What I meant by a container is that a commandbutton is a control, and therefore needs to be in a window (it's container, also known as it's parent), a userform is a window as is a worksheet. I'm not explaining this very well am I?

HTH

 

[HalfAce]

Good morning,
zilpher, I think you're explaining it quite well.
Shan, if you wanted to use this for more than one button you'd need to write an If statement using different offsets for each button, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column < 3 Or Target.Row < 3 Then Exit Sub
With ActiveSheet.Shapes("CommandButton1")
.Top = [A1]
.Left = [A1]
.Top = Target.Offset(-4).Top
.Left = Target.Offset(, -2).Left
End With

If Target.Column < 3 Or Target.Row < 3 Then Exit Sub
With ActiveSheet.Shapes("CommandButton2")
.Top = [A1]
.Left = [A1]
.Top = Target.Offset(-2).Top
.Left = Target.Offset(, -2).Left
End With

End Sub

However you will end up with the (potential) annoyances zilpher mentioned above. If I were going to use it at all for this situation, I would use it for one button that calls up the userform zilpher suggested. That way (at least while the userform is up) you'll have the use of the scroll wheel, etc.

 

[Excel-ent]

Good Day,

I agree with HalfAce that Zilpher's explaining Style is 2 good.

Though, i had already implemented Zilpher's Code in my work
I have saved HalfAce Code for any future requirement, meanwhile, as recommended, i would go for Zilpher's suggestion for this solution.

Thanks Very Much...

Best Regards,

S h a n

 

[littleme]

The heading of this thread caught my eye, cuz it seems to be exactly what I ve been looking for;HOWEVER, I am not very familiar with the language... You could almost have been speaking farsi for all I could understand. Being a newbie, I get the jist of things but not really.

What is a userform? Is it the same thing as a combo box?.... Can i use zilphers code above to make a combo box always be visible at the top left corner, even if this forner is part of a frozen pane? or.... seems to be I have the exact problem as Excel-ent did, only I dont understand the explanations fully....

Would be grateful for help


nina

 

[HalfAce]

Hi Nina,
I'm not exactly sure what you're trying to get, but I'll try & help anyway.
First of all, no, a userform is not the same thing as a combobox. A combobox is one of the controls you can put in a userform, or it can be planted directly on the worksheet. By the nature of your question, I'll assume you're not using a userform and your combobox is on the sheet itself.

If you just want the combobox to always be in the upper left corner of your screen, and you're using the freeze panes feature, you can simply move the combobox up into the frozen pane. (ie, above & to the left of the intersecting black "freeze panes" lines.) Now, no matter where you scroll to, the combobox should remain right where it is.

If however you want the combobox to float around the screen in relation to the selected cell(s) then you can use this in the Sheet code module. (Right click the tab for the sheet of interest, choose View code, copy and paste the routine of your choice into the sheet module - [the white area on the right]. Press AltQ to get back to your excel sheet.)

This first one is if you're using a combobox from the Forms toolbar...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 3 Then Exit Sub
With ActiveSheet.Shapes("Drop Down 1")
.Top = Target.Offset(-2).Top
.Left = Target.Offset(, -2).Left
End With
End Sub

This second one is if you're using an ActiveX combobox from the Control Toolbox toolbar...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 3 Or Target.Row < 3 Then Exit Sub
With ActiveSheet.Shapes("ComboBox1") 
.Top = Target.Offset(-2).Top
.Left = Target.Offset(, -2).Left
End With
End Sub

What this will do is, if you make a selection in any column to the right of Col. B and any row below Row 2, it will always keep the top left corner of your combobox 2 rows above and 2 columns to the left of the cell you've selected. It won't do anything if you select a cell in columns A or B or in rows 1 or 2.
(These were adapted from code provided by (I think) Tom Urtis a year or so ago for use with a commandbutton)

The last one here will keep the combobox in the upper left corner of your screen, no matter where you select, and without using freeze panes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ScrollRw As Long
Dim ScrollCol As Integer
ScrollRw = ActiveWindow.ScrollRow
ScrollCol = ActiveWindow.ScrollColumn
With ActiveSheet.Shapes("ComboBox1")
    .Top = Cells(ScrollRw, ScrollCol).Top
    .Left = Cells(ScrollRw, ScrollCol).Left
End With
End Sub

Is one of these what you were looking for?
Dan

 

[littleme]

thanks for the quick reply...im impressed

Tried the last code you posted, but keep getting a syntax error, and the line that begins with .Top = cells highlighted....

second question.... If I put the combo box in the part that I freeze when i do freeze panes, it wont work and unchangeable. Is there some setting to change or....

would be happy for help