Dynamic Events to Pre-Populate Start Time

[hicksi]

Hi,
I believe that my problem can be solved by an Events Class, but...

The problem that I have is that I have a variable number of rooms (specified in an Excel sheet), with a Morning and Afternoon booking. By default, when someone allocates a room, it gets pre-filled with 08:00 or 13:00.
The screen will show a textbox for each of the unallocated rooms. So there could be up to 5*2*number-of-rooms. This I achieve by dynamically adding the textbox in the "correct" position on the Userform.

What I want to achieve is to have the user click in the required textbox, have it pre-fill with the default, and then also validate the value when exiting the textbox to be a valid time and within range.

Currently, pseudo-code is like:

For each Room
    Determine .Top value
    Display Room Label
    For each Day
        Determine .Left value
        For each Session
            Add offset to .Left
            If session-not-allocated then
                        Set objTxt = Frame.Controls.Add("Forms.TextBox.1")
                        With objTxt
                            .Top = iTop
                            .Left = iLeft
                            .Width = 46
                            .Tag = Identifiable Tag
                            .Visible = True
                        End With
            Endif
        Next
    Next
Next

SO... My question is how do I supply Events to each of these TextBoxes?

 

[hicksi]

It WORKS, it WORKS, it WORKS. Thank you.

So, as a test for "full feature" of my problem, I created a form with a single commandbutton, to which THIS code applies

Option Explicit

Dim TextBoxEvents   As Collection

Private Sub CommandButton1_Click()
Dim objTxt          As Control
Dim Evnt            As ClassTextBoxEvent
Dim i               As Long, j              As Long
    
    Set TextBoxEvents = New Collection
    For i = 1 To 5
        For j = 1 To 6
            If Rnd(Now()) < 0.2 Then
                Set objTxt = Me.Controls.Add("Forms.TextBox.1")
                With objTxt
                    .Top = 30 * i
                    .Left = 50 * j
                    .Width = 46
                    .Tag = i & ":" & j
                    .Visible = True
                    Set Evnt = New ClassTextBoxEvent
                    Set Evnt.MyTextBox = objTxt
                    TextBoxEvents.Add Evnt
                End With
            End If
        Next
    Next
    CommandButton1.Enabled = False
End Sub

And my WithEvent class (called ClassTextBoxEvent for clarity)

Option Explicit
Public WithEvents MyTextBox As MSForms.TextBox

'Private Sub MyTextbox_Change()
'    MsgBox MyTextBox.Tag & " changed."
'End Sub

Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If MyTextBox.Value = vbNullString Then MyTextBox.Value = Format(TimeValue(MyTextBox.Tag), "HH:MM")
End Sub

 

[EFANYoutube]

It WORKS, it WORKS, it WORKS. Thank you.

So, as a test for "full feature" of my problem, I created a form with a single commandbutton, to which THIS code applies

Option Explicit

Dim TextBoxEvents   As Collection

Private Sub CommandButton1_Click()
Dim objTxt          As Control
Dim Evnt            As ClassTextBoxEvent
Dim i               As Long, j              As Long
   
    Set TextBoxEvents = New Collection
    For i = 1 To 5
        For j = 1 To 6
            If Rnd(Now()) < 0.2 Then
                Set objTxt = Me.Controls.Add("Forms.TextBox.1")
                With objTxt
                    .Top = 30 * i
                    .Left = 50 * j
                    .Width = 46
                    .Tag = i & ":" & j
                    .Visible = True
                    Set Evnt = New ClassTextBoxEvent
                    Set Evnt.MyTextBox = objTxt
                    TextBoxEvents.Add Evnt
                End With
            End If
        Next
    Next
    CommandButton1.Enabled = False
End Sub

And my WithEvent class (called ClassTextBoxEvent for clarity)

Option Explicit
Public WithEvents MyTextBox As MSForms.TextBox

'Private Sub MyTextbox_Change()
'    MsgBox MyTextBox.Tag & " changed."
'End Sub

Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If MyTextBox.Value = vbNullString Then MyTextBox.Value = Format(TimeValue(MyTextBox.Tag), "HH:MM")
End Sub

That's awesome hicksi
Are you all good now? Or is there something else we can help with?

 

[hicksi]

That's awesome hicksi
Are you all good now? Or is there something else we can help with?

Using MOUSEDOWN did all that I wanted of it. Particularly as it meant you couldn't inadvertently tab to the cell and inadvertently add a time.

 

[hicksi]

SO. Where's the "Answered" button?

 

[EFANYoutube]

Should be beside the post with the code I replied with

 

[EFANYoutube]

@Fluff Might be able to sort it

 

[Fluff]

SO. Where's the "Answered" button?

It's the Tick Mark to the right of each post. Just select the post that helped you the most.

 

[hicksi]

@Fluff Might be able to sort it

Found it. Many thanks for your assistance.
Now for an add-on question... Since an instance of the event gets added to the collection for each textbox, does that mean I should make the code in there as small as possible, calling out to code in a module to do the work? Or am I just showing my 1980s coding practices, where smaller code meant faster processing?

 

[EFANYoutube]

No need to do that as each instance uses the same code and therefore is not actually duplicated
olny time you'l find issues is if you have say 100 textboxes and each has an enormous adodb.recordset or something massive in it