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?

 

[EFANYoutube]

I would use this event on each one (this is triggered when every there is a mouse click down on the textbox

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

End Sub

Then put your code you wrote above on a public function and pass through the variables needed to conduct the steps in your code

 

[hicksi]

I would use this event on each one (this is triggered when every there is a mouse click down on the textbox

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

End Sub

Then put your code you wrote above on a public function and pass through the variables needed to conduct the steps in your code

That won't work. It won't compile. The problem is that there are NO textboxes on the form. They are added dynamically based on the amount of data. All-up, there can be up to 800 of them (20 rooms, 5 days, 2 sessions, on a 4-week rotation) on multiple tabs on the userform. There's no way that I want to add that many textboxes just so I can create 800 events.

 

[EFANYoutube]

Yeah I see what you mean.
I'm your code to add these. Textboxds dynamically, could you not also create this reference to some generic code.
If you provide the snippit of code you use to create a textbox. I'll see what I can do to show you what I mean

 

[EFANYoutube]

Sorry let me clean this up, ypu probably have some code that creates these textboxes, you can set the on mouse down event dynamically with this code. I just need a snippet of your code as a starting point

 

[hicksi]

Sorry let me clean this up, ypu probably have some code that creates these textboxes, you can set the on mouse down event dynamically with this code. I just need a snippet of your code as a starting point

 

[hicksi]

The pseudo-code above pretty-much explains. But, extremely simplistically, a WEEK can be coded as

For i = 1 to RoomCount
    For j = 1 to 10      'there are 10 sessions in a week
        If <Room-Session not pre-allocated> then
            Set objTxt = Frame.Controls.Add("Forms.TextBox.1")
            With objTxt
                .Top = i*30
                .Left = j*50
                .Width = 46
                .Tag = "R" & i & "S" & j   'The tag tells me which room and session the textbox applies to
                .Visible = True
            End With
        endif
    Next
Next

As far as I can tell from reading, I need to create a WITHEVENT Class and then attach that class (somehow) to the newly-created textbox.
I believe that the Mousedown event code needs to be created in that new class (though I think an ENTER - with an EXIT if necessary would serve me better).

If anyone can guide me how to create the WITHEVENT class and attach it to the textbox, I would be grateful.
For simplicity, on entering the textbox (or Mousedown), give it a value of 08:00. When I leave the textbox, turn it blue.

 

[hicksi]

jkpieterse, I have seen that you respond to other Class Event posts. Have you any thoughts on this?​

 

[EFANYoutube]

Hi Hicksi
I still can't get to what you want
The below shows a text box when someone types in it
I had to simplify your code even more as you didn't give me everything to run it
Issue is I can't get it to work with the enter and exit events or the mouse events

The class named TextboxEvent

Public WithEvents myTextbox As MSForms.TextBox

Private Sub myTextbox_Change()
    MsgBox myTextbox.Name & " changed."
End Sub

And creating the textbox

Private Sub UserForm_Click()
Set Textboxes = New Collection
Dim objTxt As Control
    Dim Evnt As TextboxEvent
    Set objTxt = Me.Controls.Add("Forms.TextBox.1")
    With objTxt
        .Top = 20
        .Left = 20
        .Width = 46
        .Tag = "Something"
        .Visible = True
        Set Evnt = New TextboxEvent
        Set Evnt.myTextbox = objTxt
        Textboxes.Add Evnt
    End With
End Sub

 

[hicksi]

Hi Hicksi
I still can't get to what you want
The below shows a text box when someone types in it
I had to simplify your code even more as you didn't give me everything to run it
Issue is I can't get it to work with the enter and exit events or the mouse events

The class named TextboxEvent

Public WithEvents myTextbox As MSForms.TextBox

Private Sub myTextbox_Change()
    MsgBox myTextbox.Name & " changed."
End Sub

And creating the textbox

Private Sub UserForm_Click()
Set Textboxes = New Collection
Dim objTxt As Control
    Dim Evnt As TextboxEvent
    Set objTxt = Me.Controls.Add("Forms.TextBox.1")
    With objTxt
        .Top = 20
        .Left = 20
        .Width = 46
        .Tag = "Something"
        .Visible = True
        Set Evnt = New TextboxEvent
        Set Evnt.myTextbox = objTxt
        Textboxes.Add Evnt
    End With
End Sub

OK. I think that we (mostly I) have been talking at crossed purposes here. I now see what you are talking about. I'll give it a go and see how it functions.