Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
Rather than put a button on the worksheet, put a custom toolbar and put the
button on the toolbar. Toolbars float easily. -- Gary''s Student - gsnu2007k "Jack Sons" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
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_Snelst eMod TIA Jack. PS I'm still wondering why the original code doesn't work anymore. -------------------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) schreef in bericht ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
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 On Error GoTo 0 End Sub "Jack Sons" wrote: 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_Snelst eMod TIA Jack. PS I'm still wondering why the original code doesn't work anymore. -------------------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) schreef in bericht ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
One quickie is to Freeze Panes with the button above and left of the frozen
row/column. Gord Dibben MS Excel MVP On Mon, 5 Jan 2009 00:28:42 +0100, "Jack Sons" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
floating button
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_Snelst eMod TIA Jack. PS I'm still wondering why the original code doesn't work anymore. -------------------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) schreef in bericht ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Floating row? | Excel Discussion (Misc queries) | |||
Floating button | New Users to Excel | |||
Floating command button | Excel Discussion (Misc queries) | |||
Floating Key | Excel Discussion (Misc queries) | |||
"Floating" button | Excel Discussion (Misc queries) |