Arthlan

Hi,

Can I create a ListBox, display it on an Excel sheet and let my users drag it around That is, not drag a value from it, but drag the entire ListBox.

I'd appreciate any help, even a "yes, but it's really really hard."

Thanks




Re: Visual Studio Tools for Office Draggable ListBox - Excel

Dennis Wallentin

Art,

The following provide a solution to it which You can test for Your solution:

Private drag As Boolean = False

Private OffsetX As Integer

Private OffsetY As Integer

Private Sub ListBox1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListBox1.MouseDown

drag = True

OffsetX = e.X

OffsetY = e.Y

End Sub

Private Sub ListBox1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListBox1.MouseMove

If drag Then

ListBox1.Left = e.X + ListBox1.Left - OffsetX

ListBox1.Top = e.Y + ListBox1.Top - OffsetY

End If

End Sub

Private Sub ListBox1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListBox1.MouseUp

drag = False

End Sub






Re: Visual Studio Tools for Office Draggable ListBox - Excel

Arthlan

Dennis,

Thanks so much. It works perfectly as long as I put the ListBox on my sheet with the toolbox and the designer. If I add the ListBox programmitically I run into a problem -- no doubt due to my own ignorance. If I create the ListBox at run time, I can still move it using your code. However, there seems to be a box that contains the ListBox -- and I can only move it within that box. The box is the size of the ListBox. When I drag the ListBox, say by dragging the upper left hand corner down, the bottom of the ListBox disappears as it moves beyond the boundries of the box. Additionally, the box remains, obscuring the grid lines on the worksheet.

Is there something I should be aware of when creating a ListBox at run time

In any case, you've given me a solution that I can use and it will make a huge difference to the project I'm working on. Thanks again.






Re: Visual Studio Tools for Office Draggable ListBox - Excel

Dennis Wallentin

Art,

Can You give me the present code You use to add the control at runtime with.

Thanks






Re: Visual Studio Tools for Office Draggable ListBox - Excel

Arthlan

Public Class Sheet1

Dim WithEvents ListBox2 As New ListBox

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
Me.Controls.AddControl(ListBox2, 10, 10, 50, 100, "test")
End Sub
...
...
...

This is then followed by the code that you sent me.

End Class

The ListBox seems locked into a region defined by the 10,10,50,100 parameters. So if I drag it, the 10,10,50,100 region stays there and the ListBox moves within it, hiding portions of it since it's exactly the same size.






Re: Visual Studio Tools for Office Draggable ListBox - Excel

Dennis Wallentin

Art,

It looks like the ActiveX 'container' the control is in has some limitations when adding the control at runtime. One workaround You can consider is to simple add the control at design time and make it invisible when the workbook is loaded. Then in some event (I assume it possible...) You make it visible for the users.

I will dig into it deeper but at present the above may solve it.






Re: Visual Studio Tools for Office Draggable ListBox - Excel

Arthlan

Dennis,

Thanks so much for looking into this problem. I can do what you suggest, and make the controls visible when needed. This will get me what I need for now, so don't dig into it any deeper on my account. Again, thanks for the help you have given me.

Art