Floating button

Question

Hi,

I am making a complicated excel file which will have multiple sheets to work on. This file will be referred and edited by several persons. I am creating a guideline in word/notepad for this file to operate. I want to give the floating button in this file which will be visible in all the sheets and upon clicking the same, the said word/note pad file shall open. Please guide how to do this using VBA.

Please also inform if there is better way to create help file for excel.

Answer 1

A small userform displayed in non-modal form will float above all sheets, but it's going to remain over some cells, so the user may have to move it from time to time to get it out of the way.  Jan Karel Pieterse's suggestion is actually probably the best of the bunch.

If I were going to do this, I'd put a

UserForm1.Show False

statement in the Workbook_Activate() process and a

Unload UserForm1

statement in the Workbook_Deactivate() process

and I'd probably add code to prevent being able to close it with the red-x.

But it is bulkier than just a button.  Here's what such a userform might look like - and this is about as small as it gets, at least in width; you might be able to make it a little 'shorter'.

Here's the ThisWorkbook code for it:

Private Sub Workbook_Activate()

  UserForm1.Show False

End Sub

Private Sub Workbook_Deactivate()

  Unload UserForm1

End Sub

And here is the code for the userform itself (works with 32 and 64-bit version of Office/Excel)

#If VBA7 Then

Private Declare PtrSafe Function SetWindowLong _

                           Lib "user32" _

                               Alias "SetWindowLongA" ( _

                               ByVal hwnd As Long, _

                               ByVal nIndex As Long, _

                               ByVal dwNewLong As Long) _

                               As Long

 Private Declare PtrSafe Function FindWindow _

                           Lib "user32" _

                               Alias "FindWindowA" ( _

                               ByVal lpClassName As String, _

                               ByVal lpWindowName As String) _

                               As Long

#Else

Private Declare Function SetWindowLong _

                           Lib "user32" _

                               Alias "SetWindowLongA" ( _

                               ByVal hwnd As Long, _

                               ByVal nIndex As Long, _

                               ByVal dwNewLong As Long) _

                               As Long

 Private Declare Function FindWindow _

                           Lib "user32" _

                               Alias "FindWindowA" ( _

                               ByVal lpClassName As String, _

                               ByVal lpWindowName As String) _

                               As Long

#End If

Private Sub UserForm_Initialize()

  'removes the red-x from the userform

  SetWindowLong FindWindow(vbNullString, Me.Caption), -16, -2067791744

End Sub

Private Sub CommandButton1_Click()

'your code to open the help document here

  MsgBox "Click"

End Sub

Answer 2

A button will also be on a fixed location, so that would not help a lot. You could add ribbon customisation to the file so there is a special button on the ribbon. Look at www.rondebruin.nl for examples.

Answer 3

Yes, tried but as sheet moves the linked cell disappears. i can put it in freezed cells but then it would be like allowing data entry operator to fiddle with it. If there is a button he may not be able to as it will have program at the background. Anyways thanks a lot for the help.

Answer 4

Hi,

You could simply add a hyperlink to the help file (Insert, Hyperlink) on each worksheet.