Results 1 to 22 of 22

Userform & Dynamic Controls & Class Module

Threaded View

sarndt01 Userform & Dynamic Controls &... 09-08-2014, 05:22 PM
Leith Ross re: Userform & Dynamic... 09-08-2014, 06:19 PM
mikerickson re: Userform & Dynamic... 09-08-2014, 09:52 PM
sarndt01 re: Userform & Dynamic... 09-09-2014, 12:36 AM
mikerickson re: Userform & Dynamic... 09-09-2014, 03:19 AM
sarndt01 re: Userform & Dynamic... 09-09-2014, 11:08 AM
sarndt01 Re: Userform & Dynamic... 09-10-2014, 01:44 PM
mikerickson Re: Userform & Dynamic... 09-10-2014, 02:23 PM
sarndt01 Re: Userform & Dynamic... 09-10-2014, 03:39 PM
sarndt01 Re: Userform & Dynamic... 09-12-2014, 04:26 PM
sarndt01 Re: Userform & Dynamic... 09-12-2014, 07:28 PM
mikerickson Re: Userform & Dynamic... 09-13-2014, 01:14 PM
sarndt01 Re: Userform & Dynamic... 09-13-2014, 11:22 PM
mikerickson Re: Userform & Dynamic... 09-14-2014, 01:55 AM
sarndt01 Re: Userform & Dynamic... 09-14-2014, 08:27 AM
mikerickson Re: Userform & Dynamic... 09-14-2014, 02:18 PM
sarndt01 Re: Userform & Dynamic... 09-14-2014, 10:57 PM
mikerickson Re: Userform & Dynamic... 09-15-2014, 09:37 AM
sarndt01 Re: Userform & Dynamic... 09-15-2014, 10:27 AM
mikerickson Re: Userform & Dynamic... 09-15-2014, 03:19 PM
sarndt01 Re: Userform & Dynamic... 09-15-2014, 07:41 PM
sarndt01 Re: Userform & Dynamic... 09-18-2014, 01:53 PM
  1. #1
    Registered User
    Join Date
    09-06-2013
    Location
    Portland Oregon
    MS-Off Ver
    Excel 2010
    Posts
    23

    Userform & Dynamic Controls & Class Module

    Have the following code:

    Userform: UserForm1

    Dim Coll_txtBoxItems As Collection
    
    Private Sub UserForm_Initialize()
    
        Dim txtBoxQuantity As MSForms.TextBox
        Dim txtBoxUnitPrice As MSForms.TextBox
    
       Set Coll_txtBoxItems = New Collection
    
        For i = 0 To 4
            Set txtBoxQuantity = Me.Controls.Add("Forms.TextBox.1", "txtBoxQuantity" & i + 1, True)
            With txtBoxQuantity
                .Top = 620 + (i * 22)
                .Width = 72
                .Height = 18
                .Left = 6
                .ZOrder (0)
                .SpecialEffect = fmSpecialEffectFlat
                .TextAlign = fmTextAlignRight
                .BackColor = &HFFFF&
                .BorderStyle = fmBorderStyleSingle
                .Value = "0.00"
            End With
            Set clsObject = New clsObjHandler
            Set clsObject.SetTextBox = txtBoxQuantity
            Coll_txtBoxItems.Add clsObject
    
            Set txtBoxUnitPrice = Me.Controls.Add("Forms.TextBox.1", "txtBoxUnitPrice" & i + 1, True)
            With txtBoxUnitPrice
                .Top = 620 + (i * 22)
                .Width = 78
                .Height = 18
                .Left = 558
                .ZOrder (0)
                .SpecialEffect = fmSpecialEffectFlat
                .TextAlign = fmTextAlignRight
                .BackColor = &HFFFF&
                .BorderStyle = fmBorderStyleSingle
                .Value = "$0.00"
            End With
            Set clsObject = New clsObjHandler
            Set clsObject.SetTextBox = txtBoxUnitPrice
            Coll_txtBoxItems.Add clsObject
            
     End Sub
    Class: clsObjHandler

    Option Explicit
    
    Private WithEvents pTxtBox As MSForms.TextBox
    
    Public Property Get txtBox() As MSForms.TextBox
        
        Set txtBox = pTxtBox
        
    End Property
    
    Public Property Set SetTextBox(ByRef txtBox As MSForms.TextBox)
    
        Set pTxtBox = txtBox
    
    End Property
    
    Private Sub ptxtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
        Dim i As Integer
        Dim ctrl_i As Integer
        Dim ctrl As Control
        Dim ctrl_name As String
        
        Dim Quantity As Double
        Dim UnitPrice As Currency
           
        If KeyCode = 13 Then
        
            ctrl_name = Mid(pTxtBox.Name, 1, Len(pTxtBox.Name) - 1)
            If ctrl_name = "txtBoxQuantity" Then
                pTxtBox.Value = Format(CDbl(pTxtBox.Value), "0.00")
                Quantity = pTxtBox.Value
                ctrl_i = Mid(pTxtBox.Name, Len(pTxtBox.Name), 1)
                For i = 0 To UserForm1.Controls.Count - 1
                    Set ctrl = UserForm1.Controls.Item(i)
                    If ctrl.Name = "txtBoxUnitPrice" & ctrl_i Then
                        UnitPrice = ctrl
                        Exit Sub
                    End If
                Next i
        End If
    
    End Sub
    Have 5 lines of

    Quantity Unit Price Item Price

    Name of textbox fields on each line are:

    txtBoxQuantity1 txtBoxUnitPrice1 txtBoxItemPrice1
    txtBoxQuantity2 txtBoxUnitPrice2 txtBoxItemPrice2
    txtBoxQuantity3 txtBoxUnitPrice3 txtBoxItemPrice3
    txtBoxQuantity4 txtBoxUnitPrice4 txtBoxItemPrice4
    txtBoxQuantity5 txtBoxUnitPrice5 txtBoxItemPrice5

    Am using Keydown Event to determine when the user hits the Return key so that the Quantity value isn't used until the full value has been entered. Can't figure out how to get UnitPrice from the Control Collection so that I can then multiple the Quantity * Unit Price for the Row to calculate Item Price for the Row.

    Thanks
    Last edited by Leith Ross; 09-08-2014 at 06:08 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Class Module for handling events of dynamically created controls
    By jagman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2018, 01:14 PM
  2. Replies: 0
    Last Post: 03-08-2014, 08:02 AM
  3. Replies: 1
    Last Post: 10-12-2012, 04:36 AM
  4. (Closed)Dynamic Userform Controls With Event Handlers
    By stuart010 in forum Excel General
    Replies: 0
    Last Post: 04-30-2012, 11:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1