want to convert Fixed 'FORM CONTROL' type button to Floating 'FORM CONTROL' type button

[tarun@1964]

Hi, "ADMINISTRATORS",'Moderators' and 'Forum Members', Hope all of YOU are well.
I have a work book and contains with existing various type of 'FORM CONTROL' type commad button attaching with several VBA CODE in every spreadsheet. these are in fix position in the spreadsheet. i mean when i scroll any spreadsheet for up/down or left/right normally those buttons moves and i cann't see it. but i want those buttons would be float, i mean if i scroll any spreadsheet for up/down or left/right it would be in fixed position in spread sheet so that it would be always in visible position.for those existing Button will i get any VBA CODE assistance from this FORUM ?

With Sincerely
Tarun@1964

[StuartR]

You can do this with code for ActiveX controls, but not for Form Controls.
The easiest alternative would be to add your commands to the Quick Access Toolbar, or the ribbon.

[p45cal]

There is a clunky workaround. In the sheet concerned's code module, something along the lines of:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set myButtons = ActiveSheet.Buttons
Set vr = ActiveWindow.VisibleRange
myTop = vr.Top + 10
myLeft = vr.Left + 10

For Each butn In myButtons
  butn.Top = myTop
  butn.Left = myLeft
  myLeft = myLeft + 70
Next butn
End Sub

It only runs when the selection on the sheet is changed, not when the page is scrolled.
See attached.

Instead of putting the code in all the sheets sheet code-modules, you could put it only in the ThisWorkbook code-module:

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set myButtons = ActiveSheet.Buttons
Set vr = ActiveWindow.VisibleRange
myTop = vr.Top + 10
myLeft = vr.Left + 10

For Each butn In myButtons
  butn.Top = myTop
  butn.Left = myLeft
  myLeft = myLeft + 70
Next butn
End Sub

which will run the routine on any of the sheets in the workbook when the selection is changed on it. This may not be desirable! There are ways to exclude/include certain sheets.

[tarun@1964]

Respected p45cal Sir,
I have got VBA Code given by You. For this Many Many thanks to You. but i have 16 no's of spreadsheet in a work book and there are on almost in every page 'CONTROL FORM' Type existing BUTTON , using with several VBA Code. Then what should i do next.

Yours Faithfully
Tarun@1964

[SpeakEasy]

>It only runs when the selection on the sheet is changed

I'd be tempted to use a Timer

[p45cal]

but i have 16 no's of spreadsheet in a work book and there are on almost in every page 'CONTROL FORM' Type existing BUTTON , using with several VBA Code. Then what should i do next.

Again:

Instead of putting the code in all the sheets sheet code-modules, you could put it only in the ThisWorkbook code-module:

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set myButtons = ActiveSheet.Buttons
Set vr = ActiveWindow.VisibleRange
myTop = vr.Top + 10
myLeft = vr.Left + 10

For Each butn In myButtons
  butn.Top = myTop
  butn.Left = myLeft
  myLeft = myLeft + 70
Next butn
End Sub

which will run the routine on any of the sheets in the workbook when the selection is changed on it. This may not be desirable! There are ways to exclude/include certain sheets.

and

There are ways to exclude/include certain sheets.

You need to tell me the EXACT names of the sheets you want to exclude.
Probably best attach a workbook; it doesn't need data in it, just the sheets concerned, the buttons and information on which sheets you want excluded or included.

[tarun@1964]

Respected p45cal Sir,
as per YOUR instruction i am giving details Sheet names are as 'ITEM RECEIVED' Sheet,'ITEMLIST' Sheet,'SUPPLIER LIST' Sheet,'PURCHASE RET' Sheet,'AVIL ITEM' Sheet,'INVOICE' Sheet,'INVOICE LIST' Sheet,'SALE RET' Sheet,'PAY TO CRED' Sheet,'CRED LIST' Sheet,'CRED LEDG' Sheet,'DEBT LIST' Sheet,'DEBT LEDG' Sheet,'PAY FRM DEBT' Sheet,'CASH' Sheet,'TO BANK' Sheet. i need not any inclusion or exclusion of BUTTONS in any of sheet. actually in each sheet has VBA Code driven "CONTROL FORM" Type BUTTON in fixed position. i need only Floating position of existing VBA Code driven "CONTROL FORM" Type BUTTON.i mean in normal situation in excel sheet visible area is A1:U26 (Potrait / Landscape )and BUTTON SITUATION in 'T2'. when i scroll down / side the sheet BUTTON goes in invisible situation, but i want as per page scrolling, BUTTONS will be visible always.

Yours Sincerely
Tarun@1964

[p45cal]

In the Thisworkbook code-module:

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
InclusionList = Array("ITEM RECEIVED", "ITEMLIST", "SUPPLIER LIST", "PURCHASE RET", "AVIL ITEM", "INVOICE", "INVOICE LIST", "SALE RET", "PAY TO CRED", "CRED LIST", "CRED LEDG", "DEBT LIST", "DEBT LEDG", "PAY FRM DEBT", "CASH", "TO BANK")
If Not IsError(Application.Match(Sh.Name, InclusionList, 0)) Then
  Set myButtons = ActiveSheet.Buttons
  Set vr = ActiveWindow.VisibleRange
  myTop = vr.Top + 10
  myLeft = vr.Left + 10

  For Each butn In myButtons
    butn.Top = myTop
    butn.Left = myLeft
    myLeft = myLeft + 70
  Next butn
End If
End Sub

One more thing, scrolling by itself will not move the buttons; you have to select a cell for the code to be triggered.

Separately, if your buttons are at the top of the sheet, you could freeze the top rows of your sheet using freeze panes. Thise rows will always be visible, as well as your buttons, and they won't need to be moved.