Announcement

Collapse
No announcement yet.

Dependant combo boxes / Text Boxes Calculations

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dependant combo boxes / Text Boxes Calculations



    I have two issues I need help with dependent combo boxes and calculating pricing. Any ideas would be appreciated thanks
    1) I have two combo boxes one Search and one VendorParts, I'm trying to make VendorParts dependent on the Search combo box to show
    only the parts available for each Vendor.
    The search combo box has the Vendors and populates the Vendor ID text box and the Discount text box. The VendorParts combo box
    has the available parts and fills all of the other text boxes with pricing.
    2) I have a series of text boxes to calculate sale price that are dependent on the VenderParts combo box. When I "Update" any of text boxes and use my cmdCalculate button the sale price is incorrect
    Thank You and here is the code I'm using for the both the vendor and vendorProducts

    Code:
    Private Sub UserForm_Activate()
    
        cmdNew.Enabled = True
        cmdUpdate.Enabled = False
        cmdSave.Enabled = False
        cmdClear.Enabled = False
        cmdDelete.Enabled = False
        cmdReports.Enabled = True
        cmdCalculate.Enabled = False
        cmdCalculate.Enabled = False
              
    LoadDataList
    LoadDataList2
    
    End Sub
    
    Private Function LoadDataList() As Boolean
        Dim ws As Worksheet
        Dim Rng As Range
        Dim C As Range
        Dim Data As Object
        Dim k As Variant
        Dim sRange As String
        Dim iLastrow As Long
          
        Set Data = CreateObject("System.Collections.ArrayList")
      
    'Get Records definition
        Set ws = ThisWorkbook.Worksheets("VendorProducts")
      
        iLastRowUsed = ws.Columns("B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If iLastRowUsed < 2 Then
          iLastRowUsed = 2
        End If
      
        sRange = "B2:B" & iLastRowUsed
        Set Rng = ws.Range(sRange)
      
    'clear combobox
        cboSearch.Clear
    
    'load array
        For Each C In Rng.Cells
            If C.Value <> "" Then
                Data.Add C.Value
            End If
        Next
      
    'Sort array
        Data.Sort
      
        For Each k In Data
          
                cboSearch.AddItem k
        Next
      
        Set Rng = Nothing
        Set ws = Nothing
        Set Data = Nothing
    
    End Function
    
    Private Sub cboSearch_AfterSave()
    'Every change of the dropdown get the value
        Dim findString As String
        Dim iRow As Integer
        Dim Rng As Range
        Dim ws As Worksheet
      
        Set ws = Worksheets("VendorProducts")
        findString = cboSearch.Text
        lblRowID = ""
        If findString <> "" Then
            With Sheets("VendorProducts").Range("B:B")
                Set Rng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    'store found row in lblRowID, a hidden field on form
                    lblRowID = Rng.Row
                End If
            End With
        End If
    
        If lblRowID <> "" Then
    'load values on form
    iRow = Val(lblRowID)
    
        Me.txtVendorID.Value = ws.Cells(iRow, 1).Value
        Me.txtVendor.Value = ws.Cells(iRow, 2).Value
        Me.txtDiscount.Value = ws.Cells(iRow, 6).Value
              
        cmdNew.Enabled = True
        cmdUpdate.Enabled = True
        cmdSave.Enabled = False
        cmdClear.Enabled = True
        cmdDelete.Enabled = True
        cmdReports.Enabled = True
    
    Set ws = Nothing
    
    End If
    
    End Sub
    
    Private Sub cboSearch_DropButtonClick()
    'Every change of the dropdown get the value
        Dim findString As String
        Dim iRow As Integer
        Dim Rng As Range
        Dim ws As Worksheet
          
        Set ws = Worksheets("VendorProducts")
        findString = cboSearch.Text
        lblRowID = ""
        If findString <> "" Then
            With Sheets("VendorProducts").Range("B:B")
                Set Rng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    'store found row in lblRowID, a hidden field on form
                    lblRowID = Rng.Row
                End If
            End With
        End If
    
        If lblRowID <> "" Then
    'load values on form
    iRow = Val(lblRowID)
    
        Me.txtVendorID.Value = ws.Cells(iRow, 1).Value
        Me.txtVendor.Value = ws.Cells(iRow, 2).Value
        Me.txtDiscount.Value = ws.Cells(iRow, 6).Value
      
        cmdNew.Enabled = True
        cmdUpdate.Enabled = True
        cmdSave.Enabled = False
        cmdClear.Enabled = True
        cmdDelete.Enabled = True
        cmdReports.Enabled = True
    
    End If
    
    End Sub
    
    Private Function LoadDataList2() As Boolean
        Dim ws As Worksheet
        Dim Rng As Range
        Dim C As Range
        Dim oData As Object
        Dim k As Variant
        Dim sRange As String
        Dim iLastrow As Long
          
        Set oData = CreateObject("System.Collections.ArrayList")
      
    'Get Records definition
        Set ws = ThisWorkbook.Worksheets("VendorProducts")
      
        iLastRowUsed = ws.Columns("D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If iLastRowUsed < 2 Then
          iLastRowUsed = 2
        End If
      
        sRange = "D2:D" & iLastRowUsed
        Set Rng = ws.Range(sRange)
      
    'clear combobox
        cboVendorPart.Clear
    
    'load array
        For Each C In Rng.Cells
            If C.Value <> "" Then
                oData.Add C.Value
            End If
        Next
      
    'Sort array
        oData.Sort
      
        For Each k In oData
          
                cboVendorPart.AddItem k
        Next
      
        Set Rng = Nothing
        Set ws = Nothing
        Set oData = Nothing
    
    End Function
    
    Private Sub cboVendorPart_AfterSave()
    'Every change of the dropdown get the value
        Dim findString As String
        Dim iRow As Integer
        Dim Rng As Range
        Dim ws As Worksheet
      
        Set ws = Worksheets("VendorProducts")
        findString = cboSearch.Text
        lblRowID = ""
        If findString <> "" Then
            With Sheets("VendorProducts").Range("D:D")
                Set Rng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    'store found row in lblRowID, a hidden field on form
                    lblRowID = Rng.Row
                End If
            End With
        End If
    
        If lblRowID <> "" Then
    'load values on form
    iRow = Val(lblRowID)
    
        Me.txtPartNo.Value = ws.Cells(iRow, 3).Value
        Me.txtListPrice.Value = ws.Cells(iRow, 5).Value
        Me.txtCost.Value = ws.Cells(iRow, 7).Value
        Me.txtMarkup.Value = ws.Cells(iRow, 8).Value
        Me.txtPrice.Value = ws.Cells(iRow, 9).Value
        Me.txtLaborRate.Value = ws.Cells(iRow, 10).Value
        Me.txtLaborHours.Value = ws.Cells(iRow, 11).Value
        Me.txtLaborPrice.Value = ws.Cells(iRow, 12).Value
        Me.txtShipping.Value = ws.Cells(iRow, 13).Value
        Me.txtSalePrice.Value = ws.Cells(iRow, 14).Value
          
        cmdNew.Enabled = True
        cmdUpdate.Enabled = True
        cmdSave.Enabled = False
        cmdClear.Enabled = True
        cmdDelete.Enabled = True
        cmdReports.Enabled = True
    
    Set ws = Nothing
    
    End If
    
    End Sub
    
    Private Sub cboVendorPart_DropButtonClick()
    'Every change of the dropdown get the value
        Dim findString As String
        Dim iRow As Integer
        Dim Rng As Range
        Dim ws As Worksheet
          
        Set ws = Worksheets("VendorProducts")
        findString = cboVendorPart.Text
        lblRowID = ""
        If findString <> "" Then
            With Sheets("VendorProducts").Range("D:D")
                Set Rng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    'store found row in lblRowID, a hidden field on form
                    lblRowID = Rng.Row
                End If
            End With
        End If
    
        If lblRowID <> "" Then
    'load values on form
    iRow = Val(lblRowID)
    
        Me.txtPartNo.Value = ws.Cells(iRow, 3).Value
        Me.txtListPrice.Value = ws.Cells(iRow, 5).Value
        Me.txtCost.Value = ws.Cells(iRow, 7).Value
        Me.txtMarkup.Value = ws.Cells(iRow, 8).Value
        Me.txtPrice.Value = ws.Cells(iRow, 9).Value
        Me.txtLaborRate.Value = ws.Cells(iRow, 10).Value
        Me.txtLaborHours.Value = ws.Cells(iRow, 11).Value
        Me.txtLaborPrice.Value = ws.Cells(iRow, 12).Value
        Me.txtShipping.Value = ws.Cells(iRow, 13).Value
        Me.txtSalePrice.Value = ws.Cells(iRow, 14).Value
      
        cmdNew.Enabled = True
        cmdUpdate.Enabled = True
        cmdSave.Enabled = False
        cmdClear.Enabled = True
        cmdDelete.Enabled = True
        cmdReports.Enabled = True
    
    End If
    
    End Sub


    Here is the Calculations code

    Code:
    Private Sub cmdCalculate_Click()
    
        Me.txtCost.Value = (Me.txtListPrice.Value - Me.txtListPrice.Value * Me.txtDiscount.Value)
        Me.txtPrice.Value = (Me.txtCost.Value * (1 + Me.txtMarkup.Value))
        Me.txtLaborPrice.Value = (Me.txtLaborRate.Value * Me.txtLaborHours.Value)
        Me.txtSalePrice.Value = (Me.txtLaborPrice.Value + Me.txtShipping.Value + Me.txtPrice.Value)
          
    End Sub
    Vendors.xlsm
    Last edited by wstring; May 13th, 2019, 21:14.
Working...
X