floating button

[Jack Sons]

Hi all,

On my worksheet I have a button of which the name is "historie". When
clicked it starts a certain macro.
The sheet is rather wide, col. A up to col. FH.
The button is now in a fixed position, in cell A1 but I want the button to
float so that when I scroll more than 16 columns to the right the button
will appear in the upper left corner of the then visible part of the
worksheet as soon as I select a cell in the that part of the worksheet.
From one of the helpful persons in this NG many years ago I got the code
below - which I put in the program module of the worksheet - and it worked
well. I want to get that effect again but the button remains fixed to its
original positition.
What is wrong? Thanks in advance for your help.

Jack Sons
The Netherlands



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
If ActiveCell.Column > 16 Then

Dim myShape As Shape

Set myShape = Me.Shapes("historie")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
'With Me.Cells(1, ActiveWindow.ScrollColumn)
myShape.Top = 30 '.Top
myShape.Left = .Left
End With
End If
End Sub

 

[Gary''s Student]

Rather than put a button on the worksheet, put a custom toolbar and put the
button on the toolbar. Toolbars float easily.

 

[JLatham]

Gary''s Student has offered a good suggestion. If you need help implementing
it, let us know. I have some code that would work for you to do this and
could post it here to help out with it. If you do ask for that help, it
would be a good thing if you would post the name of the macro that executes
now when you click on your 'histories' button. It needs to be in the code.

 

[Jack Sons]

Dear JLatham,

I have no experience with floating toolbars. In my case it will possibly be
a toolbar (that comes automatically with the worksheet) consisting of one
button?
Please send me the code you offered, the name of my macro is:

'LEERL08
debiteuren.xls'!HistOverzichtActueleCursist_SnelsteMod

TIA

Jack.

PS

I'm still wondering why the original code doesn't work anymore.
--------------------------------------------------------------------------------------------------------

 

[JLatham]

Not sure why the original isn't working - however, I can see where it might
not: It depends on a SelectionChange, which means selecting a new cell. If
you are simply scrolling to the right using the scroll bar, then the
selection doesn't change. But when you actually click on a cell in a
way-to-the-right column, it should work unless macros are turned off. Also,
clicking in a cell and using the arrow keys to scroll around should cause it
to work, again unless macros are disabled.

About that Sub Name. Right click on the button you have now and choose
Assign Macro and whatever it shows as now being used is the name we need. Is
that actually HistOverzichtActueleCursist_SnelsteMod ?? And is it in the
same workbook, or is 'LEERL08 debiteuren.xls' a different workbook?

Anyhow, here goes. You'll need to make a couple of changes to the code (one
at least, possibly two). Right at the outset you need to change "Sheet1" to
whatever the name of the sheet is that you want the toolbar to be available
on. You may have to change the name of the Macro called way on down in the
Sub CreateToolbar in the .OnAction line.

This code all goes into the ThisWorkbook code module. Copy and paste it
into that module and make changes as needed. Then you can delete all of your
current Worksheet_SelectionChange() code and the "histories" button also.
Naturally, make all of these changes to a copy of your workbook to make sure
it all works well and doesn't damage your workbook in some fashion.

If you are using Excel 2003 or earlier, easy way to get to the ThisWorkbook
code module is to right-click on the Excel icon just to the left of the word
File in the standard Excel menu bar and choose [View Code] from the list that
comes up. Here comes the code:

Option Explicit
Private Const tbName = "Histories"
'change this to the name of the sheet that
'you want the custom toolbar available on
Private Const tbSheetName = "Sheet1"
'
Private Sub Workbook_Activate()
'this handles the opening of the workbook, and
'returning to it if you have several open and
'have been working in another one
If ActiveSheet.Name = tbSheetName Then
CreateToolbar
End If
End Sub

Private Sub Workbook_Deactivate()
'this gets rid of the toolbar when you
'either activate another workbook or when
'you close this one
DestroyToolbar
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this works as you flip through the sheets
'in this workbook
If Sh.Name = tbSheetName Then
CreateToolbar
Else
DestroyToolbar
End If
End Sub

Private Sub CreateToolbar()
Dim Con, startbtn

'remove it if it already exists
DestroyToolbar ' has delete toolbar process in it
'deal with potential error that it doesn't get deleted
'or that there were multiple instances of it
On Error GoTo ToolBarError
Application.CommandBars.Add(Name:=tbName).Visible = True

Set Con = Application.CommandBars(tbName).Controls.Add _
(Type:=msoControlButton, ID:=2950)
Con.FaceId = 2950

Set startbtn = Application.CommandBars(tbName).Controls(1)
With startbtn
' ** Change this to the name of the Sub to be run
'if it is in another workbook, include the workbook
'name also.
.OnAction = "HistOverzichtActueleCursist_SnelsteMod"
.Caption = tbName
.Style = msoButtonCaption
End With

Exit Sub
ToolBarError:
'we really shouldn't ever see this message, but just in case
MsgBox "You have already created the toolbar for Histories." _
& vbCrLf & "Use: 'View|Toolbars' to activate it.", vbCritical
End Sub

Private Sub DestroyToolbar()
On Error Resume Next ' in case it doesn't exist
Application.CommandBars(tbName).Delete
If Err <> 0 Then
Err.Clear
End If

 

[Gord Dibben]

One quickie is to Freeze Panes with the button above and left of the frozen
row/column.


Gord Dibben MS Excel MVP

 

[Jack Sons]

Dear JLatham,

I have no experience with floating toolbars. In my case it will possibly be
a toolbar (that comes automatically with the worksheet) consisting of one
button?
Please send me the code you offered, the name of my macro is:

'LEERL08 debiteuren.xls'!HistOverzichtActueleCursist_SnelsteMod

TIA

Jack.

PS

I'm still wondering why the original code doesn't work anymore.
--------------------------------------------------------------------------------------------------------