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

 

Thanks for your response, Derek. I need to study what you have here.

You wrote:

> Something like this - txtCorePB5.Value = .VLookup(vLookupValue, myCoreRange, 5, False) * txtCoreMultiplier.Value (spot the difference)

I've spotted the difference, and I'm wondering what you had in mind for that suggestion--a loop of some kind?

I used to have all those vlookup formulas in the spreadsheet with the iserror. The worksheet was taking too much time to calculate. I finally had to assign calculation to manual.

That's one reason I'm attempting the userform. I have other goals for the userform, as well. I can talk about them later if you're interested.

In my code sample, I've shown Core price calculation and Entry Price Calculation. The final application will have more sections each with the same kind of vlookup price breaks.

Clamp
Two-Piece
Mod Code
Plating
Chain
Band
SelfLock
Subtotal
Markup
Discount

So I will end up with even more textboxes. Will labels use less resources? I'm curious about the advantage of labels.

It seems I may need to simulate the VB6 Controls Array object. I'll need to research that.

Do you foresee issues with that many vlookups?

I'm unable to upload the screen shot in the files section.

Thanks for your comments.

Dan

--- In ExcelVBA@yahoogroups.com, Derek Turner wrote:
>
> 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
> >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 (4)

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

.

__,_._,___