No event is triggered upon pressing tab

angsuman

New Member
Joined
Aug 19, 2015
Messages
30
Hello All,


I have one userform on which I have a multi pahe. Each tab of multi page has 4 text boxes in column format . Each column having two text boxes. At the bottom of the user form, there are three command buttons (Next Page, Cancel and Close). All controls are added to a collection and collection belongs to a custom class. The custom class will take different action based on event generated by collection and control type. One of the action is to add another row of text boxes when user presses tab key from last avaialble text box under second column. Upon reaching near the command box, if user presses tab again, command buttons are deleted, scroll bar is added to multipage and new command buttons are added with proper alignement.


However I experienced a strange behaviour. Tab key event is triggered without any issue until it is required to delete the command buttons and create them again with proper alignement. Once command buttons are deleted, tab key event is not triggered even after pressing tab from last text box. Hence I cannot add new text boxes to multi page.


Here is the declaration part in user form:
Code:
Option Explicit
Dim colHoliday As Collection
Dim clsHolidayObject As clsHolidayCalendar

Here is the declaration part in class clsHolidayCalendar:
Code:
Option Explicit
Public WithEvents tbxCal        As MSForms.TextBox
Public WithEvents cmdCal        As MSForms.CommandButton
Public WithEvents multiPageCal  As MSForms.MultiPage
Public WithEvents lblCal        As MSForms.Label
Dim colHoliday                  As Collection
Dim clsHolidayObject            As clsHolidayCalendar



Here is the code while controls are added to collection:
Code:
Set colHoliday = New Collection


Set TxbCtrl = HolCalMulPg.Pages(pageIndex).Controls.Add("Forms.TextBox.1", "Page" & pageIndex & "TextBox" & TbxIdx)


With TxbCtrl
   .Text = TxbText
   .Font = "Arial"
   .TextAlign = fmTextAlignLeft
   .Top = TxbTpMrgn
   .Left = LblLeftMrgn
   .Height = TxbHght
   .Width = TxbWdth
   .TabIndex = TbxIdx
   .Enabled = True
End With


Set clsHolidayObject = New clsHolidayCalendar
Set clsHolidayObject.tbxCal = TxbCtrl
colHoliday.Add clsHolidayObject

Following two sub will add and delete the text boxes and command buttons. All these subs are triggered upon pressing tab key at the last avaialble text box. Third sub is AddToCollection which loop through all controls in user form and add them to collection again. These subs are present in class clsHolidayCalendar:


Code:
Private Sub AddNewTextBox()
   Dim TxbCtrl          As MSForms.TextBox
   Dim ctrl             As MSForms.Control
   Dim pageIdx          As Long
   Dim i                As Long
   
   pageIdx = Letters_Out(FocusedPage)
   
   Set colHoliday = New Collection
   
   For Each ctrl In HolidayCalendarUserform.Controls
       If TypeOf ctrl Is MSForms.MultiPage Then
       
          TxbIndex = TxbIndex + 1
       
          Set TxbCtrl = ctrl.Pages(pageIdx).Controls.Add("Forms.TextBox.1", "Page" & pageIdx & "TextBox" & TxbIndex)
           
          With TxbCtrl
             .Text = "Enter/Select A Date"
             .Font = "Arial"
             .TextAlign = fmTextAlignLeft
             .Top = TxbTpMrgn
             .Left = LeftMrgn1
             .Height = TxbHght
             .Width = TxbWdth1
             .TabIndex = TxbIndex
             .Enabled = True
          End With
          
          TxbIndex = TxbIndex + 1
           
          Set TxbCtrl = ctrl.Pages(pageIdx).Controls.Add("Forms.TextBox.1", "Page" & pageIdx & "TextBox" & TxbIndex)
           
          With TxbCtrl
             .Text = "Enter Holiday Description"
             .Font = "Arial"
             .TextAlign = fmTextAlignLeft
             .Top = TxbTpMrgn
             .Left = LeftMrgn2
             .Height = TxbHght
             .Width = TxbWdth2
             .TabIndex = TxbIndex
             .Enabled = True
          End With
          
       End If
       Exit For
   Next


End Sub


Private Sub ShiftInstLabelCommanBtn()
   Dim ctrl         As MSForms.Control
   Dim LblCtrl      As MSForms.Label
   Dim CmdBtnCtrl   As MSForms.CommandButton


      
   For Each ctrl In HolidayCalendarUserform.Controls
      If TypeOf ctrl Is MSForms.MultiPage Then
         Set LblCtrl = ctrl.Pages(pageIndex).Controls.Add("Forms.Label.1", "Page" & pageIndex & "Label" & LblCount - 1)
         
         With LblCtrl
            .Caption = "Press Tab Key To Add More Records"
            .Font = "Arial"
            .Font.Bold = True
            .TextAlign = fmTextAlignLeft
            .Top = InstLblTopMrgn
            .Left = LeftMrgn1
            .Height = LblHght
            .AutoSize = True
            .WordWrap = False
            .BackStyle = fmBackStyleTransparent
         End With
            
         With ctrl
            .Height = MulPgHt
            .Width = MulPgWd
            If MulScHt > MulPgHt Then
               .Pages(pageIndex).KeepScrollBarsVisible = 1
               .Pages(pageIndex).ScrollBars = 2
               .Pages(pageIndex).ScrollHeight = MulScHt
            End If
            
            If pageIndex Mod 2 = 0 Then
               .Pages(pageIndex).Picture = PicForm.Image2.Picture
               .Pages(pageIndex).PictureSizeMode = 1
            Else
               .Pages(pageIndex).Picture = PicForm.Image3.Picture
               .Pages(pageIndex).PictureSizeMode = 1
            End If
         End With
         
         Set CmdBtnCtrl = ctrl.Pages(pageIndex).Controls.Add("Forms.CommandButton.1")
        
         GapBtnCmdBtns = (UFormWd - (CmdBtnWdth * CmdBtnCount)) / CmdBtnCount - 1
         CmdBtnLeftMrgn = LeftMrgn1
         
         CmdBtnCtrl.Height = CmdBtnHght
         CmdBtnCtrl.Width = CmdBtnWdth
         CmdBtnCtrl.Top = CmdBtnTpMrgn
         CmdBtnCtrl.Left = CmdBtnLeftMrgn
         CmdBtnCtrl.Font.name = "Arial"
         CmdBtnCtrl.Font.Size = 8
         CmdBtnCtrl.Enabled = True
         If pageIndex > 0 Then
            CmdBtnCtrl.Caption = "Prev Page"
         Else
            CmdBtnCtrl.Caption = "Next Page"
         End If
         CmdBtnCtrl.Font.Bold = True
         CmdBtnCtrl.name = "Page" & pageIndex & "Button" & CmdBtnCtrl.Caption
        
         CmdBtnLeftMrgn = CmdBtnLeftMrgn + CmdBtnCtrl.Width + GapBtnCmdBtns
        
         Set CmdBtnCtrl = ctrl.Pages(pageIndex).Controls.Add("Forms.CommandButton.1")
        
         CmdBtnCtrl.Height = CmdBtnHght
         CmdBtnCtrl.Width = CmdBtnWdth
         CmdBtnCtrl.Top = CmdBtnTpMrgn
         CmdBtnCtrl.Left = CmdBtnLeftMrgn
         CmdBtnCtrl.Font.name = "Arial"
         CmdBtnCtrl.Font.Size = 8
         CmdBtnCtrl.Enabled = True
         CmdBtnCtrl.Caption = "Cancel"
         CmdBtnCtrl.Font.Bold = True
         CmdBtnCtrl.name = "Page" & pageIndex & "Button" & CmdBtnCtrl.Caption
        
        
         CmdBtnLeftMrgn = CmdBtnLeftMrgn + CmdBtnCtrl.Width + GapBtnCmdBtns
        
         Set CmdBtnCtrl = ctrl.Pages(pageIndex).Controls.Add("Forms.CommandButton.1")
        
         CmdBtnCtrl.Height = CmdBtnHght
         CmdBtnCtrl.Width = CmdBtnWdth
         CmdBtnCtrl.Top = CmdBtnTpMrgn
         CmdBtnCtrl.Left = CmdBtnLeftMrgn
         CmdBtnCtrl.Font.name = "Arial"
         CmdBtnCtrl.Font.Size = 8
         CmdBtnCtrl.Enabled = True
         CmdBtnCtrl.Caption = "Close"
         CmdBtnCtrl.Font.Bold = True
         CmdBtnCtrl.name = "Page" & pageIndex & "Button" & CmdBtnCtrl.Caption
        
         Set CmdBtnCtrl = ctrl.Pages(pageIndex).Controls.Add("Forms.CommandButton.1")
        
         CmdBtnCtrl.Height = 20
         CmdBtnCtrl.Width = 20
         CmdBtnCtrl.Top = TopMrgn / 2
         CmdBtnCtrl.Left = MulPgWd - RghtMrgn - 10
         CmdBtnCtrl.Enabled = True
         CmdBtnCtrl.Picture = PicForm.Image1.Picture
         CmdBtnCtrl.Caption = "Calendar"
         CmdBtnCtrl.name = "Page" & pageIndex & "Button" & CmdBtnCtrl.Caption


      End If
      Exit For
   Next


End Sub


Private Sub AddToCollection()
  Dim ctrl         As Control
  
  Set colHoliday = Nothing
  
  For Each ctrl In HolidayCalendarUserform.Controls
      
      If TypeOf ctrl Is MSForms.MultiPage Then
         Set colHoliday = New Collection
         Set clsHolidayObject = New clsHolidayCalendar
         Set clsHolidayObject.multiPageCal = ctrl
         colHoliday.Add clsHolidayObject
      End If
      
      If TypeOf ctrl Is MSForms.Label Then
         Set colHoliday = New Collection
         Set clsHolidayObject = New clsHolidayCalendar
         Set clsHolidayObject.lblCal = ctrl
         colHoliday.Add clsHolidayObject
      End If
      
      If TypeOf ctrl Is MSForms.TextBox Then
         Set colHoliday = New Collection
         Set clsHolidayObject = New clsHolidayCalendar
         Set clsHolidayObject.tbxCal = ctrl
         colHoliday.Add clsHolidayObject
      End If
      
      If TypeOf ctrl Is MSForms.CommandButton Then
         Set colHoliday = New Collection
         Set clsHolidayObject = New clsHolidayCalendar
         Set clsHolidayObject.cmdCal = ctrl
         colHoliday.Add clsHolidayObject
      End If
      
  Next


End Sub

Please help and suggest how to resolve this problem.


Thanks
Angsuman
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,551
Instead of deleting and re-creating the CommandButton, have you tried just moving the existing one?
 

angsuman

New Member
Joined
Aug 19, 2015
Messages
30
Hello mikerickson,

Thank you for your reply. I did not try moving the command button and will check it now. Hopefully that will resolve click command button event. However for text box I have no option but to add new text boxes as user needs more text boxes. The tab key event is triggered for text boxes which is not happening when new text boxes are added.

Thanks
Angsuman
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,551
It sounds like your userform has a ton of moving parts. I haven't looked at the code much, but the newly created TextBox is also instansised as on of your custom controls (I assume).

Is there a limit to the number of textboxes that a user would need? If so, creating the textboxes at design time and hiding the unneeded ones is more robust that creating them at run-time.
 

angsuman

New Member
Joined
Aug 19, 2015
Messages
30
Unfortunately, I do not have any idea about number of text boxes required. Hence I allow user to create new row by pressing tab key.
 

angsuman

New Member
Joined
Aug 19, 2015
Messages
30
Hello mikerickson,

Can you please check following part:

Code:
Set colHoliday = New Collection

   For Each ctrl In HolidayCalendarUserform.Controls
       If TypeOf ctrl Is MSForms.MultiPage Then
       
          TxbIndex = TxbIndex + 1
       
          Set TxbCtrl = ctrl.Pages(pageIdx).Controls.Add("Forms.TextBox.1", "Page" & pageIdx & "TextBox" & TxbIndex)
           
          With TxbCtrl
             .Text = "Enter/Select A Date"
             .Font = "Arial"
             .TextAlign = fmTextAlignLeft
             .Top = TxbTpMrgn
             .Left = LeftMrgn1
             .Height = TxbHght
             .Width = TxbWdth1
             .TabIndex = TxbIndex
             .Enabled = True
          End With
          
          Set clsHolidayObject = New clsHolidayCalendar
          Set clsHolidayObject.tbxCal = TxbCtrl
          colHoliday.Add clsHolidayObject
          
          TxbIndex = TxbIndex + 1
           
          Set TxbCtrl = ctrl.Pages(pageIdx).Controls.Add("Forms.TextBox.1", "Page" & pageIdx & "TextBox" & TxbIndex)
           
          With TxbCtrl
             .Text = "Enter Holiday Description"
             .Font = "Arial"
             .TextAlign = fmTextAlignLeft
             .Top = TxbTpMrgn
             .Left = LeftMrgn2
             .Height = TxbHght
             .Width = TxbWdth2
             .TabIndex = TxbIndex
             .Enabled = True
          End With
         
          Set clsHolidayObject = New clsHolidayCalendar
          Set clsHolidayObject.tbxCal = TxbCtrl
          colHoliday.Add clsHolidayObject
         
       End If
       Exit For
   Next
Do we set collection object to a new collection or continue with the same collection? If I remove this line, I get error while adding new object to collection

Thanks
Angsuman
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,551
You need to instansize a new collection when the userform first opens. Thereafter, you do not want a New Collection.

I'm not sure where the posted code is, but I would put this line in the Userform_Intialize event, and remove it from every other routine.
Code:
Set colHoliday = New Collection
 

angsuman

New Member
Joined
Aug 19, 2015
Messages
30
The code which adds new text boxes are in the class and in this case is in clsHolidayCalendar. I have followed your instruction but without any luck.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,551
Does the add-textbox code also instnanize a new clsHolidayObject object and set the newly created textbox to it's .tbxCal property?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,124
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top