Move Command Buttons

  • Thread starter Thread starter jimicl
  • Start date Start date
J

jimicl

Hi Friends,

I asign a macro to a command button and i left this
button in G3, now i have a serie of data(over row 2000), but my button
is still in the same position and i have to get back to C3 to do my
macro, is there an option (or macro) to move my command button while my
datas are growing


Thanks i'd really aprecciate it


greeting from Chile..........

Jimicl
 
Jimicl,

On the commandbutton, right-click and choose Format Control from the menu.
On the Properties tab, select the Don 't move or size with cells option.

Where in Chile are you? My daughter is doing some voluntary work in
Patagonia in 2005.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Jimicl

Try a custom code-made floating toolbar. Here's a demo for something else
but I'm sure you'll get the picture. Paste this into a standard module and
run.

Option Explicit 'TOP of module

Sub AddCell()
Call Add2Combo(ActiveCell.Address)
End Sub

Sub Add2Combo(CellAdress As String)
On Error Resume Next
If Application.CommandBars("Messenger") Is Nothing Then Call CreateToolbar
On Error GoTo 0
CommandBars("Messenger").Controls(1).AddItem CellAdress
End Sub

Sub CreateToolbar()
Beep
Call KillIt(True)
Application.CommandBars.Add(Name:="Messenger").Visible = True

Application.CommandBars("Messenger").Controls.Add _
Type:=msoControlDropdown ', ID:="List"
With Application.CommandBars("Messenger").Controls(1)
.DropDownLines = .ListCount
.DropDownWidth = 250
.OnAction = _
ThisWorkbook.Name & "!GoThere"
.TooltipText = "Select a cell"
.Caption = "Cap"
End With

Application.CommandBars("Messenger").Controls.Add _
Type:=msoControlButton
With Application.CommandBars("Messenger").Controls(2)
.Caption = "Add cell"
.Style = msoButtonIconAndCaption
.FaceId = 1087
.OnAction = ThisWorkbook.Name & "!AddCell"
End With


Application.CommandBars("Messenger").Controls.Add _
Type:=msoControlButton
With Application.CommandBars("Messenger").Controls(3)
.Caption = "Kill me"
.Style = msoButtonIconAndCaption
.FaceId = 608
.OnAction = ThisWorkbook.Name & "!KillIt"
End With
Application.CommandBars("Messenger").Top = 150
Application.CommandBars("Messenger").Left = 150
End Sub

Sub GoThere()
Range(Application.CommandBars("Messenger").Controls(1).Text).Select
End Sub

Sub KillIt(Optional DontAsk As Boolean)
On Error Resume Next
If DontAsk = False Then
If MsgBox("This will remove the cell list forever. Continue ?", _
vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then Exit Sub
End If
Application.CommandBars("Messenger").Delete
End Sub
 
Back
Top