ListBox Docking

[poleary2000]

I have a UserForm that I would like to user to see at all times. Is there anyway to write some code that will make the UserForm Dockable?? Meaning, it attaches itself to Excel [kind of like the Locals window in the VBA editor].

Any help would be appreciated.

[Jay Petrulis]

On 2002-04-03 13:12, poleary2000 wrote:
I have a UserForm that I would like to user to see at all times. Is there anyway to write some code that will make the UserForm Dockable?? Meaning, it attaches itself to Excel [kind of like the Locals window in the VBA editor].

Any help would be appreciated.

Hi Patrick,

My guess is no, although if you have Excel XP you can make a modeless UserForm (Stephen Bullen's website http://www.BMSltd.co.uk has an example of a modeless userform in Excel 97/2000. (not 100% sure of the address here)

What you can do is create a custom toolbar and either dock it or keep it floating over the sheet. An add-in may be another possiblity.

Please provide more details about your userform and somebody is sure to take a stab at it.

Regards,
Jay

[poleary2000]

I am using a modeless user form. It contains a list box that is constantly being refreshed after a cell in the worksheet is changed. I was hoping to be able to dock it so the user could always see the form and still work in Excel. It is currently working this way, however the for is floating and obstructs the users view.

[Dave Hawley]

Hi Patrick


I don't believe you could "dock" a userform. Why can't they simply drag it out the way?

[Ivan F Moala]

other options include;
1) Having Min/max buttons if you need to place the for in the task bar while they work
2) making the for Ajustable ie. similar to a
true VB form that is able to be resized.

Let me know if this option is satifactory.

[poleary2000]

The users can move the user form out of the way. I would rather not have them do that however.

Min / Max buttons is a viable option. How do I put those on a UserForm?

I am using Excel 2002 and the UserForm I have created is Modeless. If someone from Excel 2000 / 97 uses the file, will it be modeless for them also?

[Dave Hawley]

Hi Patrick


Excel 2000 supports Modless UserForms, but 97 doesn't.

To shrink/expand try a simple ToggleButton

Private Sub ToggleButton1_Click()

If ToggleButton1 = True Then
    ToggleButton1.Caption = "Expand"
       Me.Height = Me.Height * 0.2
Else
    ToggleButton1.Caption = "Shrink"
         Me.Height = 240
End If
   
End Sub

[poleary2000]

The toggle button is a great idea. I will try it out. Thanks!

[poleary2000]

You mentioned Excel 97 does not support modeless forms. What would happen if an Excel 97 user would attempt to use my file that does have a modeless form?

Thanks for all the help everyone has given me.

[Dave Hawley]

Hi Patrick

This would be a problem according to the Help.

Note: In Microsoft Office 97, if a UserForm is set to display as modeless, it causes a run-time error; Office 97 UserForms are always modal.


See: "Show Method" in the Help.