Floating button

[ryan.fitzpatrick3]

Hi there,

This is probably an easy question. I have 5 buttons up top that I do
not want to move when I scroll. So I put a freeze pane on row 4 so
rows 1-4 do not move which works fine, but my sheet deals with columns
and not rows so much. I tried column freezepane but it freezes the
entire column, of course, and I do not need the entire column frozen
just cells A1:I3. Is there a way to keep the 5 buttons up top fixed in
a location whereas if I move side to side via columns the 5 buttons
will be on top; it will be the same from column 'a' to column 'zz'
Make sense?

Ryan

 

[Guest]

Put them on a Userform
Remember to set Showmodal to False in Properties window


"(e-mail address removed)" skrev:

 

[ryan.fitzpatrick3]

Put them on a Userform
Remember to set Showmodal to False in Properties window

"(e-mail address removed)" skrev:

How do I do that?

 

[Dave D-C]

[I don't see a response]
Incomplete instructions for a Userform:
In the IDE (Alt-F11) Insert a Userform.
Add Commandbuttons from the Controls Toolbox.
Somewhere, do: Userform1.Show 0
But I have XL97 which doesn't have non-modal anyhow.

I suggest a CommandBar:
Sub sub1()
Dim CB As CommandBar
On Error Resume Next
CommandBars("CB1").Delete
On Error GoTo 0 ' restore error processing
Set CB = CommandBars.Add("CB1", msoBarTop, False, True)
CB.Visible = True
Call m1CBAddBtn(msoButtonCaption, "Landscape", "m1Landscape", True,
CB)
Call m1CBAddBtn(msoButtonCaption, "Portrait", "m1Portrait", False,
CB)
Call m1CBAddBtn(-1, "", "", True, CB) ' make group divider
End Sub

Sub m1CBAddBtn(pStl&, pCap$, pAct$, pEnbld As Boolean, pCB As
CommandBar)
Dim CBC As CommandBarControl
If pStl = -1 Then
pCB.Controls(pCB.Controls.Count).BeginGroup = True
Exit Sub
End If
Set CBC = pCB.Controls.Add(msoControlButton)
CBC.Style = pStl
CBC.Caption = pCap
CBC.OnAction = pAct
CBC.Enabled = pEnbld
End Sub

Sub m1Landscape()
Beep
End Sub
Sub m1Portrait()
Beep
End Sub

 

[ryan.fitzpatrick3]

[I don't see a response]
Incomplete instructions for a Userform:
In the IDE (Alt-F11) Insert a Userform.
Add Commandbuttons from the Controls Toolbox.
Somewhere, do: Userform1.Show 0
But I have XL97 which doesn't have non-modal anyhow.

I suggest a CommandBar:
Sub sub1()
Dim CB As CommandBar
On Error Resume Next
CommandBars("CB1").Delete
On Error GoTo 0 ' restore error processing
Set CB = CommandBars.Add("CB1", msoBarTop, False, True)
CB.Visible = True
Call m1CBAddBtn(msoButtonCaption, "Landscape", "m1Landscape", True,
CB)
Call m1CBAddBtn(msoButtonCaption, "Portrait", "m1Portrait", False,
CB)
Call m1CBAddBtn(-1, "", "", True, CB) ' make group divider
End Sub

Sub m1CBAddBtn(pStl&, pCap$, pAct$, pEnbld As Boolean, pCB As
CommandBar)
Dim CBC As CommandBarControl
If pStl = -1 Then
pCB.Controls(pCB.Controls.Count).BeginGroup = True
Exit Sub
End If
Set CBC = pCB.Controls.Add(msoControlButton)
CBC.Style = pStl
CBC.Caption = pCap
CBC.OnAction = pAct
CBC.Enabled = pEnbld
End Sub

Sub m1Landscape()
Beep
End Sub
Sub m1Portrait()
Beep
End Sub

Ryan wrote:
How do I do that?

The code works good, but it's really not what I would like. Your code
eliminates all functionality with scrolling up/down/left /right. If my
button was in B2 I would like for it to always be in the screen, so if
I was in Z column the button would be to the right of Z in AA, the
button is always centered in the same row but moves columns when I
scroll over columns. I can send example via email if you would like.

Ryan

 

[Dave D-C]

Ryan,
I see what you want, but can't do it.
This will respond to a new selection,
but not scrolling.
(I assume you have a CommandButton1 somewhere)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim zCell As Range
Set zCell = Target.Offset(0, 1)
CommandButton1.Left = zCell.Left
CommandButton1.Top = zCell.Top
End Sub

Private Sub CommandButton1_Click()
Beep
End Sub

Incidentally, replace 'msoBarTop' (in yesterday's code)
with 'mosBarFloating' for a more 'in-your-face' ToolBar.

D-C Dave