Floating Command button

[Probyn]

Is there a way to make a command button stay in place in Excel sheet
as I scroll around. I would like to achieve this without using the menu
bar or placing the button above a a Freeze Pane.

Thanks

[Ardus Petus]

This will move the commandbutton with activecell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.CommandButton1
.Top = ActiveCell.Top
.Left = ActiveCell.Left + 2 * ActiveCell.Width
End With
End Sub

HTH
--
AP

"Probyn" <[email protected]> a écrit dans le message de news:
[email protected]...
> Is there a way to make a command button stay in place in Excel sheet
> as I scroll around. I would like to achieve this without using the menu
> bar or placing the button above a a Freeze Pane.
>
> Thanks
>

[Probyn]

Thanks Ardus.

This routine move the command button to 2 place of the selected cell.
Is there a way to keep the Command Button in place as I scroll around
the sheel.

Thanks.


Ardus Petus wrote:
> This will move the commandbutton with activecell
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> With Me.CommandButton1
> .Top =3D ActiveCell.Top
> .Left =3D ActiveCell.Left + 2 * ActiveCell.Width
> End With
> End Sub
>
> HTH
> --
> AP
>
> "Probyn" <[email protected]> a =E9crit dans le message de news:
> [email protected]...
> > Is there a way to make a command button stay in place in Excel sheet
> > as I scroll around. I would like to achieve this without using the menu
> > bar or placing the button above a a Freeze Pane.
> >
> > Thanks
> >

[Probyn]

Looking at your code It would seems that if code the Command button to
move opposited to and in response to the sheet scrolling that should
work. Any idea how to write the code to respond to the scrolling
event. Thanks




Ardus Petus wrote:
> This will move the commandbutton with activecell
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> With Me.CommandButton1
> .Top =3D ActiveCell.Top
> .Left =3D ActiveCell.Left + 2 * ActiveCell.Width
> End With
> End Sub
>
> HTH
> --
> AP
>
> "Probyn" <[email protected]> a =E9crit dans le message de news:
> [email protected]...
> > Is there a way to make a command button stay in place in Excel sheet
> > as I scroll around. I would like to achieve this without using the menu
> > bar or placing the button above a a Freeze Pane.
> >
> > Thanks
> >

[Dave Peterson]

I don't think you'll find a scrolling event.

Probyn wrote:
>
> Looking at your code It would seems that if code the Command button to
> move opposited to and in response to the sheet scrolling that should
> work. Any idea how to write the code to respond to the scrolling
> event. Thanks
>
> Ardus Petus wrote:
> > This will move the commandbutton with activecell
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > With Me.CommandButton1
> > .Top = ActiveCell.Top
> > .Left = ActiveCell.Left + 2 * ActiveCell.Width
> > End With
> > End Sub
> >
> > HTH
> > --
> > AP
> >
> > "Probyn" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> > > Is there a way to make a command button stay in place in Excel sheet
> > > as I scroll around. I would like to achieve this without using the menu
> > > bar or placing the button above a a Freeze Pane.
> > >
> > > Thanks
> > >

--

Dave Peterson

[Miyahn]

"Probyn" wrote in message news:[email protected]
> Is there a way to make a command button stay in place in Excel sheet
> as I scroll around. I would like to achieve this without using the menu
> bar or placing the button above a a Freeze Pane.

The following is an example.
If you are using Excel2003, you will receive an alert when opening the book.
To avoid this alert, you must change security setting by ORK.

Option Explicit
Sub Auto_Close()
On Error Resume Next
Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
On Error GoTo 0
End Sub
Sub Auto_Open()
SetTimer
End Sub
'
Private Sub SetTimer()
Dim aObject As Object, Found As Boolean
With Worksheets(1)
For Each aObject In .OLEObjects
If aObject.Name = "DHTMLEdit1" Then Found = True
Next aObject
If Not Found Then
On Error Resume Next
.OLEObjects.Add "DHTMLEdit.DHTMLEdit.1"
On Error GoTo 0
End If
If .Buttons.Count = 0 Then .Buttons.Add 0, 0, 75, 25
End With
Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
End Sub
'
Private Sub StartTimer()
Dim Src(20) As String
Src(0) = "<script Language = VBS>"
Src(1) = "Dim tId, Target, PX, PY"
Src(2) = "Sub MoveTimer()"
Src(3) = " On Error Resume Next"
Src(4) = " With Target.Parent.Application"
Src(5) = " P = Target.Parent.Columns(.ActiveWindow.ScrollColumn).Left"
Src(6) = " If P <> PX Then Target.Left = P: PX = P"
Src(7) = " P = Target.Parent.Rows(.ActiveWindow.ScrollRow).Top"
Src(8) = " If P <> PY Then Target.Top = P: PY = P"
Src(9) = " End With"
Src(10) = " On Error GoTo 0"
Src(11) = "End Sub"
Src(12) = "Sub StartTimer(Arg)"
Src(13) = " Set Target = Arg: If tId <> 0 Then StopTimer"
Src(14) = " tId = Window.setInterval(""MoveTimer"", 100)"
Src(15) = "End Sub"
Src(16) = "Sub StopTimer()"
Src(17) = " Set Target = Nothing: Window.clearInterval tId"
Src(18) = " tId = 0"
Src(19) = "End Sub"
Src(20) = "</script>"
With Worksheets(1).DHTMLEdit1
.Width = 0: .Height = 0: .BrowseMode = True
.DocumentHTML = Join(Src, vbCrLf)
Do While .Busy: DoEvents: Loop
.DOM.Script.StartTimer Worksheets(1).Buttons(1)
End With
End Sub

--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Probyn]

Thanks Dave.

Dave Peterson wrote:
> I don't think you'll find a scrolling event.
>
> Probyn wrote:
> >
> > Looking at your code It would seems that if code the Command button to
> > move opposited to and in response to the sheet scrolling that should
> > work. Any idea how to write the code to respond to the scrolling
> > event. Thanks
> >
> > Ardus Petus wrote:
> > > This will move the commandbutton with activecell
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > With Me.CommandButton1
> > > .Top =3D ActiveCell.Top
> > > .Left =3D ActiveCell.Left + 2 * ActiveCell.Width
> > > End With
> > > End Sub
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "Probyn" <[email protected]> a =E9crit dans le message de news:
> > > [email protected]...
> > > > Is there a way to make a command button stay in place in Excel she=
et
> > > > as I scroll around. I would like to achieve this without using the =
menu
> > > > bar or placing the button above a a Freeze Pane.
> > > >
> > > > Thanks
> > > >
>=20
> --=20
>=20
> Dave Peterson

[Probyn]

Arigato-gozaimasu Miyahn-san.

I find that this code generates the command button but how do I attach
my macro to this button and how do I make more than one button.

Regards

Miyahn wrote:
> "Probyn" wrote in message news:[email protected]
> > Is there a way to make a command button stay in place in Excel sheet
> > as I scroll around. I would like to achieve this without using the menu
> > bar or placing the button above a a Freeze Pane.
>
> The following is an example.
> If you are using Excel2003, you will receive an alert when opening the book.
> To avoid this alert, you must change security setting by ORK.
>
> Option Explicit
> Sub Auto_Close()
> On Error Resume Next
> Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
> On Error GoTo 0
> End Sub
> Sub Auto_Open()
> SetTimer
> End Sub
> '
> Private Sub SetTimer()
> Dim aObject As Object, Found As Boolean
> With Worksheets(1)
> For Each aObject In .OLEObjects
> If aObject.Name = "DHTMLEdit1" Then Found = True
> Next aObject
> If Not Found Then
> On Error Resume Next
> .OLEObjects.Add "DHTMLEdit.DHTMLEdit.1"
> On Error GoTo 0
> End If
> If .Buttons.Count = 0 Then .Buttons.Add 0, 0, 75, 25
> End With
> Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
> End Sub
> '
> Private Sub StartTimer()
> Dim Src(20) As String
> Src(0) = "<script Language = VBS>"
> Src(1) = "Dim tId, Target, PX, PY"
> Src(2) = "Sub MoveTimer()"
> Src(3) = " On Error Resume Next"
> Src(4) = " With Target.Parent.Application"
> Src(5) = " P = Target.Parent.Columns(.ActiveWindow.ScrollColumn).Left"
> Src(6) = " If P <> PX Then Target.Left = P: PX = P"
> Src(7) = " P = Target.Parent.Rows(.ActiveWindow.ScrollRow).Top"
> Src(8) = " If P <> PY Then Target.Top = P: PY = P"
> Src(9) = " End With"
> Src(10) = " On Error GoTo 0"
> Src(11) = "End Sub"
> Src(12) = "Sub StartTimer(Arg)"
> Src(13) = " Set Target = Arg: If tId <> 0 Then StopTimer"
> Src(14) = " tId = Window.setInterval(""MoveTimer"", 100)"
> Src(15) = "End Sub"
> Src(16) = "Sub StopTimer()"
> Src(17) = " Set Target = Nothing: Window.clearInterval tId"
> Src(18) = " tId = 0"
> Src(19) = "End Sub"
> Src(20) = "</script>"
> With Worksheets(1).DHTMLEdit1
> .Width = 0: .Height = 0: .BrowseMode = True
> .DocumentHTML = Join(Src, vbCrLf)
> Do While .Busy: DoEvents: Loop
> .DOM.Script.StartTimer Worksheets(1).Buttons(1)
> End With
> End Sub
>
> --
> Miyahn (Masataka Miyashita) JPN
> Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
> https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Miyahn]

"Probyn" wrote in message news:[email protected]
> I find that this code generates the command button but how do I attach
> my macro to this button and how do I make more than one button.

My example is for demonstration, so create a DHTMLEdit object and
one command button object dynamicaly.
You can add these objects by manual operation.
If the book already has these objects, necessary code is,

Option Explicit
Sub Auto_Close()
Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
End Sub
'
Sub Auto_Open()
Dim Src(20) As String
Src(0) = "<script Language = VBS>"
Src(1) = "Dim tId, Target, PX, PY"
Src(2) = "Sub MoveTimer()"
Src(3) = " On Error Resume Next"
Src(4) = " With Target.Parent.Application"
Src(5) = " P = Target.Parent.Columns(.ActiveWindow.ScrollColumn).Left"
Src(6) = " If P <> PX Then Target.Left = P: PX = P"
Src(7) = " P = Target.Parent.Rows(.ActiveWindow.ScrollRow).Top"
Src(8) = " If P <> PY Then Target.Top = P: PY = P"
Src(9) = " End With"
Src(10) = " On Error GoTo 0"
Src(11) = "End Sub"
Src(12) = "Sub StartTimer(Arg)"
Src(13) = " Set Target = Arg: If tId <> 0 Then StopTimer"
Src(14) = " tId = Window.setInterval(""MoveTimer"", 100)"
Src(15) = "End Sub"
Src(16) = "Sub StopTimer()"
Src(17) = " Set Target = Nothing: Window.clearInterval tId"
Src(18) = " tId = 0"
Src(19) = "End Sub"
Src(20) = "</script>"
With Worksheets(1).DHTMLEdit1
.DocumentHTML = Join(Src, vbCrLf)
Do While .Busy: DoEvents: Loop
.DOM.Script.StartTimer Worksheets(1).Buttons(1)
End With
End Sub

To attach your macro to the button, right click the button -> [Attach Macro]
(I am not sure expression of menu item in english version.)

For more than one button, modify DHTMLEdit1.DocumentHTML's script to
Accept collection of objects
Set appropriate position for each object
and pass buttons collection to the script.

--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Probyn]

Thanks again Miyahn,

I did not use Scripting language before so this is a little difficult
for me. Your first set of codes I attached to the work sheet and it
created the command button dynamically. However when I right-clicked
the 'Assigned Macro' option was not selectable. Your second set of
codes I do not understand if should be in the worksheet, a module or
attached to the command button. Also, how does the code reference the
command button? Let's assume I have two command buttons name
TestButton1 and TestButton2 in TestWorksheet1 please demonstrate the
code for this.

Much thanks.





Miyahn wrote:
> "Probyn" wrote in message news:[email protected]
> > I find that this code generates the command button but how do I attach
> > my macro to this button and how do I make more than one button.
>
> My example is for demonstration, so create a DHTMLEdit object and
> one command button object dynamicaly.
> You can add these objects by manual operation.
> If the book already has these objects, necessary code is,
>
> Option Explicit
> Sub Auto_Close()
> Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
> End Sub
> '
> Sub Auto_Open()
> Dim Src(20) As String
> Src(0) = "<script Language = VBS>"
> Src(1) = "Dim tId, Target, PX, PY"
> Src(2) = "Sub MoveTimer()"
> Src(3) = " On Error Resume Next"
> Src(4) = " With Target.Parent.Application"
> Src(5) = " P = Target.Parent.Columns(.ActiveWindow.ScrollColumn).Left"
> Src(6) = " If P <> PX Then Target.Left = P: PX = P"
> Src(7) = " P = Target.Parent.Rows(.ActiveWindow.ScrollRow).Top"
> Src(8) = " If P <> PY Then Target.Top = P: PY = P"
> Src(9) = " End With"
> Src(10) = " On Error GoTo 0"
> Src(11) = "End Sub"
> Src(12) = "Sub StartTimer(Arg)"
> Src(13) = " Set Target = Arg: If tId <> 0 Then StopTimer"
> Src(14) = " tId = Window.setInterval(""MoveTimer"", 100)"
> Src(15) = "End Sub"
> Src(16) = "Sub StopTimer()"
> Src(17) = " Set Target = Nothing: Window.clearInterval tId"
> Src(18) = " tId = 0"
> Src(19) = "End Sub"
> Src(20) = "</script>"
> With Worksheets(1).DHTMLEdit1
> .DocumentHTML = Join(Src, vbCrLf)
> Do While .Busy: DoEvents: Loop
> .DOM.Script.StartTimer Worksheets(1).Buttons(1)
> End With
> End Sub
>
> To attach your macro to the button, right click the button -> [Attach Macro]
> (I am not sure expression of menu item in english version.)
>
> For more than one button, modify DHTMLEdit1.DocumentHTML's script to
> Accept collection of objects
> Set appropriate position for each object
> and pass buttons collection to the script.
>
> --
> Miyahn (Masataka Miyashita) JPN
> Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
> https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Miyahn]

"Probyn" wrote in message news:[email protected]
> Thanks again Miyahn,
>
> I did not use Scripting language before so this is a little difficult
> for me. Your first set of codes I attached to the work sheet and it
> created the command button dynamically. However when I right-clicked
> the 'Assigned Macro' option was not selectable.

VBS is very similar to VBA.
Right-click on the point where mouse pointer changes to cross-arrows.

> Your second set of
> codes I do not understand if should be in the worksheet, a module or
> attached to the command button.

Paste into the standard module.

>Also, how does the code reference the command button?

This line
> .DOM.Script.StartTimer Worksheets(1).Buttons(1)
is passing the button object as a parameter to the script.

--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
https://mvp.support.microsoft.com/pr...4-83d372c269b4
If you can read Japanese,
Miyahn's Archive: http://homepage2.nifty.com/miyahn/

[Probyn]

Miyahn thanks for your time. I am now able to assign a macro to the
automatically generated button. I am unable to work with the manual
code. Would you please add the code to your program below to make say
4 command buttons. This migh help me to understand the code a little
more. Thanks.


Option Explicit
Sub Auto_Close()
On Error Resume Next
Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
On Error GoTo 0
End Sub
Sub Auto_Open()
SetTimer
End Sub
'
Private Sub SetTimer()
Dim aObject As Object, Found As Boolean
With Worksheets(1)
For Each aObject In .OLEObjects
If aObject.Name = "DHTMLEdit1" Then Found = True
Next aObject
If Not Found Then
On Error Resume Next
.OLEObjects.Add "DHTMLEdit.DHTMLEdit.1"
On Error GoTo 0
End If
If .Buttons.Count = 0 Then .Buttons.Add 0, 0, 75, 25
End With
Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
End Sub
'
Private Sub StartTimer()
Dim Src(20) As String
Src(0) = "<script Language = VBS>"
Src(1) = "Dim tId, Target, PX, PY"
Src(2) = "Sub MoveTimer()"
Src(3) = " On Error Resume Next"
Src(4) = " With Target.Parent.Application"
Src(5) = " P =
Target.Parent.Columns(.ActiveWindow.ScrollColumn).Left"
Src(6) = " If P <> PX Then Target.Left = P: PX = P"
Src(7) = " P = Target.Parent.Rows(.ActiveWindow.ScrollRow).Top"
Src(8) = " If P <> PY Then Target.Top = P: PY = P"
Src(9) = " End With"
Src(10) = " On Error GoTo 0"
Src(11) = "End Sub"
Src(12) = "Sub StartTimer(Arg)"
Src(13) = " Set Target = Arg: If tId <> 0 Then StopTimer"
Src(14) = " tId = Window.setInterval(""MoveTimer"", 100)"
Src(15) = "End Sub"
Src(16) = "Sub StopTimer()"
Src(17) = " Set Target = Nothing: Window.clearInterval tId"
Src(18) = " tId = 0"
Src(19) = "End Sub"
Src(20) = "</script>"
With Worksheets(1).DHTMLEdit1
.Width = 0: .Height = 0: .BrowseMode = True
.DocumentHTML = Join(Src, vbCrLf)
Do While .Busy: DoEvents: Loop
.DOM.Script.StartTimer Worksheets(1).Buttons(1)
End With
End Sub

[Miyahn]

"Probyn" wrote in message news:[email protected]
> Miyahn thanks for your time. I am now able to assign a macro to the
> automatically generated button. I am unable to work with the manual
> code.

Do you mean that you can use only the macro created by 'Macro Recording'?

Well, the update version is here.

Option Explicit
Const MaxN = 4, HGap = 100, VOfs = 50, BW = 75, BH = 25
Const BCap = "TestButton", ModuleName = "Module1"
Sub Auto_Close()
On Error Resume Next
Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
On Error GoTo 0
End Sub
Sub Auto_Open()
SetTimer
End Sub
Private Sub SetTimer()
Dim aObject As Object, Found As Boolean, I As Long
With Worksheets(1)
For Each aObject In .OLEObjects
If aObject.Name = "DHTMLEdit1" Then Found = True
Next aObject
If Not Found Then
On Error Resume Next
.OLEObjects.Add "DHTMLEdit.DHTMLEdit.1"
On Error GoTo 0
End If
If .Buttons.Count = 0 Then
For I = 1 To MaxN
.Buttons.Add(HGap * I, VOfs, BW, BH).Caption = BCap & CStr(I)
Next
End If
End With
Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
End Sub
Private Sub StartTimer()
Dim Buf As String
With Application.VBE.ActiveVBProject.VBComponents(ModuleName).Codemodule
Buf = .Lines(1, .CountOfLines)
End With
With CreateObject("VBScript.RegExp")
.Pattern = "' <script language=vbs>\r\n([\s\S]+)' </script>"
Buf = .Execute(Buf)(0)
End With
Buf = Replace(Buf, "HGap", CStr(HGap)): Buf = Replace(Buf, "VOfs", CStr(VOfs))
With Worksheets(1).DHTMLEdit1
.Width = 0: .Height = 0: .BrowseMode = True
.DocumentHTML = Replace(Buf, "'", "")
Do While .Busy: DoEvents: Loop
.DOM.Script.StartTimer Worksheets(1).Buttons
End With
End Sub
' <script language=vbs>
' Dim tId, cTarget, PX, PY
' Sub MoveTimer()
' Dim P, IsScrolled, I
' On Error Resume Next
' With cTarget.Parent.Application
' P = cTarget.Parent.Columns(.ActiveWindow.ScrollColumn).Left
' IsScrolled = (P <> PX): PX = P
' P = cTarget.Parent.Rows(.ActiveWindow.ScrollRow).Top
' IsScrolled = IsScrolled Or (P <> PY): PY = P
' End With
' If IsScrolled = False Then Exit Sub
' For I = 1 To cTarget.Count
' cTarget(I).Left = PX + HGap * I
' cTarget(I).Top = PY + VOfs
' Next
' On Error GoTo 0
' End Sub
' Sub StartTimer(Arg)
' Set cTarget = Arg: If tId <> 0 Then StopTimer
' tId = Window.setInterval("MoveTimer", 100)
' End Sub
' Sub StopTimer()
' Set cTarget = Nothing: Window.clearInterval tId: tId = 0
' End Sub
' </script>

--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Probyn]

Thanks again Miyahn. There is one problem with the new code. I get the
following error when I run the program:

Run-time error '1004'
Progrmmatic access to Visual Basic Project is not trusted.

The error is associated with the following line

With
Application.VBE.ActiveVBProject.VBComponents(ModuleName).Codemodules.

Also, does changing MaxN only determine the number of buttons.



Miyahn wrote:
> "Probyn" wrote in message news:[email protected]
> > Miyahn thanks for your time. I am now able to assign a macro to the
> > automatically generated button. I am unable to work with the manual
> > code.
>
> Do you mean that you can use only the macro created by 'Macro Recording'?

When I tried to use the manual code you sent (that is the code that
does not dynamically create the command button). I created two command
buttons using the Control Tool Box. I named the controls, Button1 and
Commandbutton1. But I could not figure out how to connect the code and
those command buttons and so the float did not work.

Thanks again.





>
> Well, the update version is here.
>
> Option Explicit
> Const MaxN = 4, HGap = 100, VOfs = 50, BW = 75, BH = 25
> Const BCap = "TestButton", ModuleName = "Module1"
> Sub Auto_Close()
> On Error Resume Next
> Worksheets(1).DHTMLEdit1.DOM.Script.StopTimer
> On Error GoTo 0
> End Sub
> Sub Auto_Open()
> SetTimer
> End Sub
> Private Sub SetTimer()
> Dim aObject As Object, Found As Boolean, I As Long
> With Worksheets(1)
> For Each aObject In .OLEObjects
> If aObject.Name = "DHTMLEdit1" Then Found = True
> Next aObject
> If Not Found Then
> On Error Resume Next
> .OLEObjects.Add "DHTMLEdit.DHTMLEdit.1"
> On Error GoTo 0
> End If
> If .Buttons.Count = 0 Then
> For I = 1 To MaxN
> .Buttons.Add(HGap * I, VOfs, BW, BH).Caption = BCap & CStr(I)
> Next
> End If
> End With
> Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
> End Sub
> Private Sub StartTimer()
> Dim Buf As String
> With Application.VBE.ActiveVBProject.VBComponents(ModuleName).Codemodule
> Buf = .Lines(1, .CountOfLines)
> End With
> With CreateObject("VBScript.RegExp")
> .Pattern = "' <script language=vbs>\r\n([\s\S]+)' </script>"
> Buf = .Execute(Buf)(0)
> End With
> Buf = Replace(Buf, "HGap", CStr(HGap)): Buf = Replace(Buf, "VOfs", CStr(VOfs))
> With Worksheets(1).DHTMLEdit1
> .Width = 0: .Height = 0: .BrowseMode = True
> .DocumentHTML = Replace(Buf, "'", "")
> Do While .Busy: DoEvents: Loop
> .DOM.Script.StartTimer Worksheets(1).Buttons
> End With
> End Sub
> ' <script language=vbs>
> ' Dim tId, cTarget, PX, PY
> ' Sub MoveTimer()
> ' Dim P, IsScrolled, I
> ' On Error Resume Next
> ' With cTarget.Parent.Application
> ' P = cTarget.Parent.Columns(.ActiveWindow.ScrollColumn).Left
> ' IsScrolled = (P <> PX): PX = P
> ' P = cTarget.Parent.Rows(.ActiveWindow.ScrollRow).Top
> ' IsScrolled = IsScrolled Or (P <> PY): PY = P
> ' End With
> ' If IsScrolled = False Then Exit Sub
> ' For I = 1 To cTarget.Count
> ' cTarget(I).Left = PX + HGap * I
> ' cTarget(I).Top = PY + VOfs
> ' Next
> ' On Error GoTo 0
> ' End Sub
> ' Sub StartTimer(Arg)
> ' Set cTarget = Arg: If tId <> 0 Then StopTimer
> ' tId = Window.setInterval("MoveTimer", 100)
> ' End Sub
> ' Sub StopTimer()
> ' Set cTarget = Nothing: Window.clearInterval tId: tId = 0
> ' End Sub
> ' </script>
>
> --
> Miyahn (Masataka Miyashita) JPN
> Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
> https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Miyahn]

"Probyn" wrote in message news:[email protected]
> Thanks again Miyahn. There is one problem with the new code. I get the
> following error when I run the program:
>
> Run-time error '1004'
> Progrmmatic access to Visual Basic Project is not trusted.

See this KB's article.(I assume that your Excel's version is 2002)
http://support.microsoft.com/kb/282033/en-us

Change security option at your own risk.

--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
https://mvp.support.microsoft.com/pr...4-83d372c269b4

[Probyn]

Arigato-gozaimasu Miyahn-san.

It is now working fine. I will now experiment with some parameter
modify different settings.

Thank you.





Miyahn wrote:
> "Probyn" wrote in message news:[email protected]
> > Thanks again Miyahn. There is one problem with the new code. I get the
> > following error when I run the program:
> >
> > Run-time error '1004'
> > Progrmmatic access to Visual Basic Project is not trusted.
>
> See this KB's article.(I assume that your Excel's version is 2002)
> http://support.microsoft.com/kb/282033/en-us
>
> Change security option at your own risk.
>
> --
> Miyahn (Masataka Miyashita) JPN
> Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
> https://mvp.support.microsoft.com/pr...4-83d372c269b4

[DaveVoss]

I found the simplest solution to be to create an extra row at the top of the worksheet and put command buttons there. Include that row in Freeze Panes and the buttons are always there.

[tuscan]

I had also been looking for a way how to float a button, unfortunately the Excel VBA help says very little about buttons.
My main problem was, how do I get the properties of a button. The examples I found here and elsewhere ("copy this code
and it works" unfortunately did not work for me because apparently there were some important declarations left out.
Eventually I understood that buttons are objects depending of worksheets (sorry if my explanation does not use the
correct terms, I am not a VBA expert, just self-teaching) and you can find out about them by looking at the properties
of your active sheet with the debugger.

So there is a property "Buttons" of the ActiveSheet and it has as many numbered "Item"s as there are buttons.

Please Login or Register  to view this content.

is the number of buttons.

Please Login or Register  to view this content.

is button nr.1 and so on

Please Login or Register  to view this content.

is the button text.

I also learned (what has not been explained by anybody who posted examples) that apparently every time you do something in
a worksheet this will call a subroutine named "Worksheet_SelectionChange()" if it exists.
When I copied some of the examples which used objects like "commandbutton1", nothing happened because "commandbutton1"
was undefined. So using the above, I created the following code, which works (with one exception):

Please Login or Register  to view this content.

This code also takes care of what happens when your reference cell ("ActiveCell") is too close to the right border
of the active window. It will then place the button on the left side of the active cell.
"bw" is the width of the active window.
This code works well as far as the floating button is concerned and does not require any further declarations,
everything is related to the active window and the active worksheet.

My only problem is that the button will not hold the macro that I have assigned to it. In other words, I can
assign a macro or subroutine to the button, but when I click the button, nothing happens and if I check if the macro
is still assigned, the field is empty. I am not sure whether this has to do with my code or whether it is an
unrelated issue.