Excel VBA - Userform Textbox Manipulation

[roscoe]

Hey People!

Total newbie to this sort of thing, so could do with a bit of basi
help...

The problem I have concerns 8 textboxs in a userform controlled by
spinbutton. they contain quantities of an item to be purchased. What
need to do is to have the quantities of all eight, multiplied by price
specific to the item...

e.g. textbox1*11 etc

and then have the total of these 8 sums displayed in another blan
textbox

Apoligies if very basic, but i'm working against the clock

Many thanks,

Rosco

 

[Harald Staff]

Hi Roscoe

Not basic. Textboxes have Text properties, which are String variables. You
need something like Val to make them numbers -assuming also that reasonable
digits are entered.

Textbox9.Text = Val(TextBox1.Text) * 11 + _
Val(TextBox2.Text) * 12 + _
Val(TextBox3.Text) * 13 + _
Val(TextBox4.Text) * 14 + _
Val(TextBox5.Text) * 15 + _
Val(TextBox6.Text) * 16 + _
Val(TextBox7.Text) * 17 + _
Val(TextBox8.Text) * 18

HTH. Best wishes Harald

 

[Tom Ogilvy]

You don't say how to get the price for the specific item, so here is a guess

Dim tbox as MSforms.Textbox
Dim item as MSforms.Label
for i = 1 to 8
set tbox = Userform1.Controls("TextBox" & i)
set item = Userform1.Controls("Label" & i)
price = application.Vlookup(item,Worksheets("Costs").Range("A1:B2000"),0)
qty = clng(Tbox.Text)
'tbox1.Text = price * qty
tot = tot + price * qty
Next
Textbox9.Text = tot

 

[roscoe]

Thanks Harold

Question, if I copy and paste your code in to the code for the blan
txtbox (i.e. textbox9) and replace the names and prices with the ones
have, should that work?


Thanks Tom

The prices I have are just made up numbers, they're not in an
worksheet or anything, i'd just want to be able to put in any number.
have no idea behind the concept of what you've written proving m
newbie status, any chance it could be "dummed down"? I'm very muc
still learning.

could you write where the prices would be as "price1" "price2" etc


Thanks again guy

 

[Tom Ogilvy]

Use Harald's. It is as "dumbed down" as anything I could come up with.

As far as where to put it, when do you want it to fire. Putting it in the
code for Textbox9 wouldn't seem appropriate - in fact you shouldn't be using
a textbox for the sum since you want to publish a result, not allow the user
to interact with it. A label is more appropriate.

You could put the code in the Click event for Textbox8 or put a
commandbutton on the form and put it in the click event for the
commandbutton.

 

[Harald Staff]

Use Harald's. It is as "dumbed down" as anything I could come up with.

LOL. Thanks Tom

Best wishes Harald

 

[roscoe]

I know it's been a while but just saying thanks for solving m
problems!!!

cheers,

Rosco