No event is triggered upon pressing tab

A

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Instead of deleting and re-creating the CommandButton, have you tried just moving the existing one?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Does the add-textbox code also instnanize a new clsHolidayObject object and set the newly created textbox to it's .tbxCal property?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
116
6StringJazzer
6StringJazzer
A
  • Question Question
Cannot Align Text Box When Added Dynamically
Replies
1
Views
486
angsuman
A
Jaafar Tribak
  • Question Question
Generic Class for detecting Mouse Enter & Leave Events on Userform Controls.
2
Replies
18
Views
6K
Jaafar Tribak
Jaafar Tribak
A
  • Question Question
Change Event for Multiple Check Boxes
Replies
3
Views
1K
Domenic
D
Jaafar Tribak
  • Question Question
Changing the Font & Back Colors of individual Tabs\Pages in a MultiPage
Replies
4
Views
8K
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top