Userform Problems

[f2e4]

Hey guys,

Its been about 6 months since I've had to do any VBA and think I may have forgotten a large portion so please bear with me on this one.

I have created a worksheet that acts like a finance summary sheet that can be tweaked to show you what the overall effect is on your longon term figures.

I am now trying to turn this worksheet into a userform (looks a bit more professional) that is going to pull data in from a worksheet - 'data sheet' in the same way as the original worksheet does.

OK..... so here are my queries:

1. How do I format a textbox?
The cells are formatted as #,##0 and I thought the following would work:
[vba]TextBox1 = Format(TextBox1, "#,##0")[/vba]

2. How do I conditionally format a textbox? This is what I have been using:
[vba]If Val(TextBox11.Value) < "0" Then TextBox11.BackColor = RGB(255, 0, 0) 'Red
If Val(TextBox11.Value) > (Val(TextBox6.Value) * 0.025) Then TextBox11.BackColor = RGB(255, 134, 0) 'Orange
If Val(TextBox11.Value) > "0" & < (Val(TextBox6.Value) * 0.025)TextBox11.BackColor = RGB(0, 255, 0) 'Green '
This one is meant to be between 0 and the orange condition[/vba]

3. Embarrasing Question: Why does this not work?
[VBA]Val(Textbox11.value) = Val(Textbox6.value) - Val(Textbox1.value)[/VBA]


More questions on this userform to follow once these are answered.

Thanks again for all the help

Spreadsheets attached

[f2e4]

Original worksheet version attached for reference.

[Bob Phillips]

Without digging too deep into your post, I would caution against a userform. Forms are high maintenance, you have to do so much manually that comes packaed with worksheets.

[nickgill]

f2e4

I would agree with xld on the caution of using a userform with the number of values you're trying to capture and the number of calculations you're looking to do. For instance, you'll need to setup event handlers so that whenever an input changes, any of the text boxes that depend on that input also change.

To answer your questions:

1. You need to set the format when you set the value. Your code is correct but can't go in the UserForm Initialise section as this will only run once (on the form being loaded).

2. You're missing a Then on the third IF. You could also show this as a Select Case to make things a bit easier to follow.

3. You can't have (and don't need) the Val on the left hand side.

Your code could also look like:
[vba]
TextBox6.Value = 100000
TextBox1.Value = 8000

Dim OrangeCondition As Integer

TextBox11.Value = Format(Val(TextBox6.Value) - Val(TextBox1.Value), "#,##0")

OrangeCondition = Val(TextBox6.Value) * 0.025

Select Case Val(TextBox11.Value)
Case Is < 0
TextBox11.BackColor = RGB(255, 0, 0) 'Red
Case Is > OrangeCondition
TextBox11.BackColor = RGB(255, 134, 0) 'Orange
Case 0 To OrangeCondition
TextBox11.BackColor = RGB(0, 255, 0) 'Green
End Select
[/vba]

[f2e4]

Thanks very much for the advice guys!!!

If there is a risk in using a userform for what I want to do, is there any other way of setting up something similar other than using the plain worksheet format.

[Bob Phillips]

f2e4, the risk in the work you will have to do. Tell us what are the objections to a worksheet. My experience tells me you can control it just as easily, take far less time, and make it look as good as you need.

[f2e4]

it wasn't really my choice - my boss found out about userforms and now has his mind set on it.

Will try to talk him out of it but inevitably will have to give him some kind of form be it a very simple form linked to the summary worksheet.

[Bob Phillips]

You can do a very simple form by selecting the data and using Data>Form... No bells and whistles, but it is a form.