Grid Control

Question

OK - I've tried all the searches that I can think of to get either the FlexGrid control to work (apparently not possible in Excel 2013) or a replacement grid control that will give me some functionality such as  :

   - scrollable rows and columns - hence a bunch of textboxes/labels don't provide the answer

   - sort when clicking on the column header

   - action event when a cell is clicked or double-clicked

   - formatting of cells by column (number format, alignment, font, etc.) - hence a listbox doesn't provide the answer

I've created my own version as a form with a myriad of labels which get fed with the data as the captions.  I've used labels as I don't want to allow any editing of the data and I know there's a problem with textboxes and certain events not being available (EXIT for example)

I'm sure it's not the most elegant solution, but it works for the most part.

Two important questions :

Firstly, when using the labels as classes they'll work with a mouse click event or a mouse double-click event but stubbornly refuse to work when both are listed in the label class.  All I get is the click to fire.  The code works perfectly for each event as long as the other is not included.

Public WithEvents Lbl As MSForms.Label

Public Frm As UserForm

Private Sub Lbl_Click()

Call Current_Row_Colour(Lbl.Parent, False)

Select Case Val(Mid(Lbl.Name, 5, 2))

Case Is = gridFixRows

Call SortDisplay_Data(Lbl)

Case Is > gridFixRows

gridCurrentRow = Format(Val(Mid(Lbl.Name, 5, 2) - 1), "00")

End Select

Call Current_Row_Colour(Lbl.Parent, True)

End Sub

Private Sub Lbl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Call Run_Single(Lbl)

End Sub

Secondly, once I fix problem number one, how do I turn the form into a class I can use in more than one application ?

Answer 1

Is the Listview control an alternative ?

If so, how do I get is to load without the 'Library not registered' error ?

Answer 2

Sorry for the delay in responding

Thanks for the double click guidance

However, I've used your code (almost) exactly as listed, but my form stubbornly refuses to allow my code to run for the double-click when the click event code is present.

For my test I simply added a message box to show 'Single Click' or 'Double-Click' where you had  'your usual code'

Back to the drawing board for the time being.

Shame, as I've managed to fix 95% of my grid control, but this piece is driving me nuts.

Plan B is to add a set of 'fire' buttons to the left of each row of labels and fire the relevant code from there.

Not pretty, but effective

Answer 3

The issue for me is that the click event fires a set of functions that effectively prevent the double-click event from working.  On to plan B for that one.

Not so fast please. ;-)

At first to your question: Not really, I only want to mention, because you said

"I don't want to allow any editing of the data and I know there's a problem with textboxes"

and when you set Enabled=False, you can not edit a text box, that's all.

And a text box has a Dbl_Click event, but not a Click event... so you went into another trouble if you would use a text box instead.

Back to the labels: When the Click event fires, I would use Sleep and wait for "a moment" and then look if the Dbl_Click event was fired. So you can eliminate on of the events. See the code below as sample.

Andreas.

Dim WithEvents lbl As MSForms.Label

#If Win64 Then

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

#Else

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

#End If

Dim DblClickFired As Boolean

Private Sub lbl_Click()

  Sleep 200

  If Not DblClickFired Then

    'Your usual code here

  Else

    'We do nothing

    DblClickFired = False

  End If

End Sub

Private Sub lbl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

  DblClickFired = True

  'Your usual code here

End Sub

Answer 4

Andreas

Your point about the label events is a good one and at least explains why I'm getting the results I am.  The issue for me is that the click event fires a set of functions that effectively prevent the double-click event from working.  On to plan B for that one.

It sounds from your reply that I'm trying to take one standardising step too many with the form class issue.  Back to the drawing board for using it in multiple files

Is the enabled property bit a suggestion to use textboxes because the click and double-click events work differently to the same label events ?

If so, I'll give that a try.

Answer 5

when using the labels as classes they'll work with a mouse click event or a mouse double-click event but stubbornly refuse to work when both are listed in the label class.  All I get is the click to fire. 

I can not reproduce this behavior, try this:

Make a new file

Add a class module, name it "clsLbl" and add this code

Public WithEvents Lbl As MSForms.Label

Private Sub Lbl_Click()

  Lbl.Caption = "Lbl_Click " & Now

End Sub

Private Sub Lbl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

  Lbl.Caption = "Lbl_DblClick " & Now

End Sub

Add a userform, add a big label and add this code

Dim MyLabel As New clsLbl

Private Sub UserForm_Initialize()

  Set MyLabel.Lbl = Me.Label1

End Sub

Run the form. As you see the caption changes to Dbl_Click when you double click it, but you would also note that every double click fires the Click event too. That might be the reason of your confusion.

To the 2nd point, that is only possible with 2 tricks and not really handsome. The class module must be public, unfortunately in VBA you can set only "PublicNotCreatable", but that is okay.

Set a reference from the second file to the file with the class module. Now you are able to "see" the class from the other file, but you can not use NEW to create a class. For that you need "a handler" in the first file that does the work for you. Just this simple function in a regular module does it:

Function CreateClass() As clsLbl

  Set CreateClass = New clsLbl

End Function

When you copy the Userform to the second file (for testing purposes) you have to change the code to this:

Dim MyLabel As clsLbl

Private Sub UserForm_Initialize()

  Set MyLabel = CreateClass

  Set MyLabel.Lbl = Me.Label1

End Sub

BTW, do you know that a text box has a Enabled property?

Andreas.