Excel Forms

[JezLisle]

I have a form in Excel that records data, its is an Order Form. On this is a column for Price. I have a number of rows for creating a Description of Goods, Qty & Price. These are all as a Text Box. At the bottom is a Total Box, this is as a Label.

What I am trying to do is after the Text Boxes have had a value input is to calculate the Total Price as we go along. How do I get this to work?

This is what I have been doing....

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

when inputting a Price in the Text Boxes it comes back with Type Mismatch and highlights Sub TotalCalc. I got as far as inputting 2 Price in when the Type Mismatch came up.

What can that be for?

Also lblTotal will still not add them up but just keep putting whatever is in the TextBoxes, eg 55555

[royUK]

TextBox entries are by default strings, you need to convert them with CLng

Please Login or Register  to view this content.

You can actually combine it all into one Procedure

Please Login or Register  to view this content.

[JezLisle]

I've tried the rejigged code and I still have an issue with it. Everytime I input a number in the Price TextBox is brings up the Error MsgBox, and doesnt Total the lblTotal.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Where am I going wrong?

[royUK]

You might get an error message if any textbox is empty, it must have a value so set a default value of zero,

[Andy Pope]

You can condense your code

Please Login or Register  to view this content.

Also I notice your declared your variables as Double but are using the CLNG function which will lose the floating point information

[JezLisle]

Thanks for that, what do I delcare IngTotalPrice as of not a Double?

I also need the ability to have empty TextBox's depending on whether an order is raised with so many products.

[VBA Noob]

JezLisle,

Please read forum rules below and then add the link to the cross post to the other forum

VBA Noob

[JezLisle]

I have tried a couple of thing of things with this...

Firstly I have taken out the MsgBox line as I want to have the ability to leave any number of TextBoxes empty as they might not have an order. Also I tried this as well

Please Login or Register  to view this content.

When this run it still doesnt add up in lblTotal. This always stays as zero, If I change the Sub GetTotal to this below I get the Compile Error message: Member or Data Member not found (not sure what it means)

Please Login or Register  to view this content.

Also am I correct in thinking that for every TextBox that I have for putting a price in will need this code behind it?

Please Login or Register  to view this content.

As there are 25 TextBox then I have this for each indvidual TextBox.

[royUK]

Please do as requested by VBA Noob & add the link

[JezLisle]

http://www.dbforums.com/showthread.php?t=1630950

[royUK]

Thanks

Andy's code loops through each textbox & sums each one that contains a numeric value, converting it with cDbl(check VBA Help), if it not numeric it reports a non numeric entry. Maybe this change might work, if there is a non numeric value or blank it changes the value to zero. Test it & kllet us know.

Please Login or Register  to view this content.

[JezLisle]

I have tried that new code and when I run the form it still shows in the lblTotal as a zero, am I correct in having this as a label or should it be a TextBox?

Also do I need the Sub GetTotal() and the Private Subs txtPrice1_ AfterUpdate() 1 to 25, because if I do then I get the Compile Error message: Member or Data Member not found

[royUK]

A label's fine for the result. Can you attach a sample workbook with your userform in? Please zip it first as sometimes unzipped workbooks don't download correctly.

[JezLisle]

This is a copy of the Excel Database with Forms

[royUK]

You weren't using the code that i could see, you still had this in use.

Please Login or Register  to view this content.

You didn't declare lngTotalPrice, I have done this but changed it a Currency

Please Login or Register  to view this content.

I think the attached does what you want, but the rest of the code could be streamlined, see the amended code for cmdAdd:

Please Login or Register  to view this content.

[JezLisle]

Thanks Roy, I hadnt changed the Sub GetTotal() as I didnt quite understand what you had done and only changed the Module on TotalPrice...

When I try run it comes up with Next without For what does that mean?

I will have a look at streamlining the other code now you have amended the cmdAdd. I've learnt something there that I didnt know. Thankyou

[royUK]

I've just checked the code & I deleted a wrong line. I have uploaded an amended file.

I think your code lines could be reduced tremendously if you use the Loop through controls method.

What Database do you send the forms to/ Is an access database/ just wondering why yo don't build the form within the database.

[JezLisle]

Ok cool, will have a look now

Thanks Roy.

I dont know that much about using the forms in Excel its the first time I have used it properly like this.

All the data is then sent to an Access Database where its stored and I can build some reports for the Management to look at and review spending and so forth. I'd love to have built this directly in Access as it would have been far easier and much better. The problem being is that the Company I work for do not allow people to create their own database, it has to come through the IT department, and also they apparently dont have anyone to support it if it falls over. Why thats necessary when I would have built it I dont know.

The other reason is that every user on the network doesnt have MS Office Proffesional loaded onto their PC. Its the bog standard one, and to upgrade it apparently would cost thousands just for a small number of users.

So I have to grin and bare it. Not really much I can do. Already complained somany times that dont have enough tools do my job

[royUK]

You can reduce code in the cancel button like this

Please Login or Register  to view this content.

[JezLisle]

Wow that really reduces all that code

Thanks for that.

Also the lblTotal now adds up too, chuffed now that works :D

Thanks again, no doubt I will be back again soon with another issue.