Converting a successful Excel Spreadsheet to a VBA based user form.

Hi,

I've recently constructed a spreadsheet which makes a simple calculation when a component is selected, It works perfectly
but I've been asked to supply another department. However, to do that its apparent that something in VBA will
be alot more suitable and stable.

So, I've compiled a combo box with a list of items which determines my variance in the calculation.
The Combo box has (currently) 3 variables in the box:
C05093 = which equals 50
C05094 = which equals 53
C05095 = which equals 55

Therefore, the calculation is:

Team Size - C05095 - offset = 1235mm
1300mm - 50mm - 10mm = 1235mm

The variable part is the pull down menu of C05095.

Any advice?

OK , I'll prep one now

I'm trying to determine the VBA code to emulate what the spreadsheet does i.e. - enter team figure, pull down the action menu which will give you a width and that will provide the user with a recommended reading, which the the figure that is important.

Try this:

Please Login or Register  to view this content.

should i paste in the module or user form?

In UserForm. But I think it would be better if you can declare a global array() for the action values.

It works great, there's just one other amendment and its ready to go. Rather than display the 55 in the min box, would it be possible to take the following and put the outcome in the min box:
Say, 1300 - BRCKT016 (55) - 10 = 1235, whereby the 1235 is displayed in the Min Box??

Sorry scrap that, it's easier to add another text box and use that as the outcome.

So my VBA code now has to take the contents of Team Size minus the min box minus 10 and display the outcome in an extra box called position.

Sorry Olly, now seeing it. I've decided to refrain from putting any data in the Excel Spreadsheet and use VBA methods exclusively. I was able to design OK with Excel but my VBA is currently limited. I'm going to try and keep every thing in the VBA domain rather than have different data locations.

Originally Posted by bralew

Sorry Olly, now seeing it. I've decided to refrain from putting any data in the Excel Spreadsheet and use VBA methods exclusively. I was able to design OK with Excel but my VBA is currently limited. I'm going to try and keep every thing in the VBA domain rather than have different data locations.

Why? There aren't different domains there is only Excel, all you're doing with VBA is automating it, why would you want to make things more difficult and less flexible?

Hi,

I,ve tried this code but to no avail. Basically, I want text box 3 to display the results of the sum of text box1 and text box2. Here's the code

Please Login or Register  to view this content.

thanks olly, sorry I'm trying to gets to grips with VBA and keep an eye on the forum, I'll give it a whirl now mate :-)

Olly, that is wonderful, there's just one small addition. Would it be possible to put the answer in "position" textbox and in width in text box width. I've updated the userform accordingly. But that is brilliant, fairplay!!!!.
I'll attach the file

and put the readings in mm??????????? :-)

That is beautiful!! Thank you ever so much, I've got plenty to keep my occupied now, Thank You Olly :-)

As you are trying to learn, so I have attached this file, which will show you another way. I have used 2 dimensional array here.