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:
Here is the declaration part in class clsHolidayCalendar:
Here is the code while controls are added to collection:
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:
Please help and suggest how to resolve this problem.
Thanks
Angsuman
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