Re: [ExcelVBA] Is there a better way to populate textboxes. I'm using a combobox, vba &vlookup

 

Dear Dan

Your screen shot was removed by the moderators so this is difficult to imagine.

However your code is over-complicated by too much repetition. I have no idea if this works but the code below will help you to see what is going on.

Your Entry price Calculation block is incorrect.
The On Error Resume Next should be before the lines likely to fail, not at the start of the routine.

Maybe you should surround the Vlookups in an IfError function.
To be safe you should empty each textbox at the start of the routine. Google how to use the  the Controls collection to do this. 
By the way is there a good reason why you are using Textboxes rather than Labels ?
Lastly, the number of Textboxes you have on this form is for me at the limit at which I would consider trying to simulate the VB6 Controls Array object by either making a class for this, or else doing something 'ingenious' with the Textbox names so I could relate part of each name with the index of its associated column in the combo and in the Vlookup ranges. 
Something like this -   txtCorePB5.Value = .VLookup(vLookupValue, myCoreRange, 5, False) * txtCoreMultiplier.Value (spot the  difference)

Private Sub cboPricingInstruction_Change()
On Error Resume Next ' NOT HERE
Dim myCoreRange As Range, myEntryRange As Range
Dim vLookupValue As Variant

    Set myCoreRange = Worksheets("tblPriceListCorePart").Range("CorePrices")
    Set myEntryRange = Worksheets("tblPriceListEntryAdder").Range("EntryPrices")
    With cboPricingInstruction
        txtCoreComp.Value = .Column(1)
        txtCoreMultiplier.Value = .Column(2)
        txtAdapterConfig.Value = .Column(3)
        txtRequiredEntryAdder.Value = .Column(4)
        txtRequiredEnv.Value = .Column(5)
        txtCheckedBy.Value = IIf(Len(.Column(31)) > 0, "Checked by " & .Column(31), "Not Checked")
        txtCheckByDt.Value = Format(.Column(32), "MM/DD/YYYY")
        txtAppBy.Value = IIf(Len(.Column(20)) > 0, "Approved By " & .Column(20), "Not Approved")
        txtApprovedByDt.Value = Format(.Column(19), "MM/DD/YYYY")
        txtEnteredBy.Value = IIf(Len(.Column(22)) > 0, "Entered by " & .Column(22), "Notify supervisor.")
        txtEnteredByDt.Value = Format(.Column(26), "MM/DD/YYYY")
        txtRevDt.Value = IIf(Len(.Column(40)) > 0, .Column(40), "Notify supervisor.")
        txtComment.Value = .Column(21) & vbLf & .Column(27)
    End With
    
    'Core price calculation
    With WorksheetFunction
        vLookupValue = txtCoreComp.Value & txtAdapterConfig.Value & txtShellSize.Value
        txtCorePB1.Value = .VLookup(vLookupValue, myCoreRange, 5, False) * txtCoreMultiplier.Value
        txtCorePB2.Value = .VLookup(vLookupValue, myCoreRange, 6, False) * txtCoreMultiplier.Value
        txtCorePB3.Value = .VLookup(vLookupValue, myCoreRange, 7, False) * txtCoreMultiplier.Value
        txtCorePB4.Value = .VLookup(vLookupValue, myCoreRange, 8, False) * txtCoreMultiplier.Value
        txtCorePB5.Value = .VLookup(vLookupValue, myCoreRange, 9, False) * txtCoreMultiplier.Value
        txtCorePB6.Value = .VLookup(vLookupValue, myCoreRange, 10, False) * txtCoreMultiplier.Value
        txtCorePB7.Value = .VLookup(vLookupValue, myCoreRange, 11, False) * txtCoreMultiplier.Value
        txtCorePB8.Value = .VLookup(vLookupValue, myCoreRange, 12, False) * txtCoreMultiplier.Value
        txtCorePB9.Value = .VLookup(vLookupValue, myCoreRange, 13, False) * txtCoreMultiplier.Value
        txtCorePB10.Value = .VLookup(vLookupValue, myCoreRange, 14, False) * txtCoreMultiplier.Value
        
      'Entry price Calculation ? ? ? ? ? obviously work in progress
    '    txtCorePB1.Value = .VLookup(vLookupValue, myEntryRange, 5, False) * txtCoreMultiplier.Value
    '    txtCorePB2.Value = .VLookup(vLookupValue, myEntryRange, 6, False) * txtCoreMultiplier.Value
    '    txtCorePB3.Value = .VLookup(vLookupValue, myEntryRange, 7, False) * txtCoreMultiplier.Value
    '    txtCorePB4.Value = .VLookup(vLookupValue, myEntryRange, 8, False) * txtCoreMultiplier.Value
    '    txtCorePB5.Value = .VLookup(vLookupValue, myEntryRange, 9, False) * txtCoreMultiplier.Value
    '    txtCorePB6.Value = .VLookup(vLookupValue, myEntryRange, 10, False) * txtCoreMultiplier.Value
    '    txtCorePB7.Value = .VLookup(vLookupValue, myEntryRange, 11, False) * txtCoreMultiplier.Value
    '    txtCorePB8.Value = .VLookup(vLookupValue, myEntryRange, 12, False) * txtCoreMultiplier.Value
    '    txtCorePB9.Value = .VLookup(vLookupValue, myEntryRange, 13, False) * txtCoreMultiplier.Value
    '    txtCorePB10.Value = .VLookup(vLookupValue, myEntryRange, 14, False) * txtCoreMultiplier.Value
    End With
End Sub

Regards

Derek Turner +++

>________________________________
> From: daniel d stagerobber@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 8 February 2013, 22:24
>Subject: [ExcelVBA] Is there a better way to populate textboxes. I'm using a combobox, vba &vlookup
>
>
> 
>
>Hi,
>
>I have a combobox,cboPricingInstruction, with 43 columns populated by a
>dynamic range, PricingInstruction.
>
>I'm using vlookup to populate the price breaks. When a vlookup argument
>isn't found, it returns an error.
>
>Generally, the errors occur in the core price calculation section of the
>code below.
>
>Is there a better way to populate the textboxes?
>
>I included a screen shot of the user form.
>
>Thanks for any suggestions.
>
>Dan
>
>Here's the vba for cboPricingInstruction:
>
>Private Sub cboPricingInstruction_Change()
>On Error Resume Next
>Dim myCoreRange As Range
>Set myCoreRange = Worksheets("tblPriceListCorePart").Range("CorePrices")
>
>Dim myEntryRange As Range
>Set myEntryRange =
>Worksheets("tblPriceListEntryAdder").Range("EntryPrices")
>
>Me.txtCoreComp.Value = Me.cboPricingInstruction.Column(1)
>Me.txtCoreMultiplier.Value = Me.cboPricingInstruction.Column(2)
>Me.txtAdapterConfig.Value = Me.cboPricingInstruction.Column(3)
>Me.txtRequiredEntryAdder.Value = Me.cboPricingInstruction.Column(4)
>Me.txtRequiredEnv.Value = Me.cboPricingInstruction.Column(5)
>Me.txtCheckedBy.Value = IIf(Len(Me.cboPricingInstruction.Column(31)) >
>0, "Checked by " & Me.cboPricingInstruction.Column(31), "Not Checked")
>Me.txtCheckByDt.Value = Format(Me.cboPricingInstruction.Column(32),
>"MM/DD/YYYY")
>Me.txtAppBy.Value = IIf(Len(Me.cboPricingInstruction.Column(20)) > 0,
>"Approved By " & Me.cboPricingInstruction.Column(20), "Not Approved")
>Me.txtApprovedByDt.Value = Format(Me.cboPricingInstruction.Column(19),
>"MM/DD/YYYY")
>Me.txtEnteredBy.Value = IIf(Len(Me.cboPricingInstruction.Column(22)) >
>0, "Entered by " & Me.cboPricingInstruction.Column(22), "Notify
>supervisor.")
>Me.txtEnteredByDt.Value = Format(Me.cboPricingInstruction.Column(26),
>"MM/DD/YYYY")
>Me.txtRevDt.Value = IIf(Len(Me.cboPricingInstruction.Column(40)) > 0,
>Me.cboPricingInstruction.Column(40), "Notify supervisor.")
>Me.txtComment.Value = Me.cboPricingInstruction.Column(21) & Chr(10) &
>Me.cboPricingInstruction.Column(27)
>
>'Core price calculation
>Me.txtCorePB1.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 5,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB2.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 6,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB3.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 7,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB4.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 8,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB5.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 9,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB6.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 10,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB7.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 11,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB8.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 12,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB9.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 13,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB10.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 14,
>False) * txtCoreMultiplier.Value
>
>'Entry price Calculation
>Me.txtCorePB1.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 5,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB2.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 6,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB3.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 7,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB4.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 8,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB5.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 9,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB6.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 10,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB7.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 11,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB8.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 12,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB9.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 13,
>False) * txtCoreMultiplier.Value
>Me.txtCorePB10.Value = WorksheetFunction.VLookup(Me.txtCoreComp.Value &
>Me.txtAdapterConfig.Value & Me.txtShellSize.Value, myCoreRange, 14,
>False) * txtCoreMultiplier.Value
>
>End Sub
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Files 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___