Putting values in textbox in userforms

[MIAnalyst]

I have a User Form that I created to be used as an input data form. There is a text box in it that I want to have automatically populated depending on what has been put in another text box eg:

textbox named WeeksTraining has a value of 6 therefore textbox named WorkRatePercentage has a value of 22%

Thanks

 

[SkipVought]

Hi,

You really did not state a condition. Did you mean,

"IF textbox named WeeksTraining has a value of 6 THEN textbox named WorkRatePercentage should be assigned 22%"?

if Me.WeeksTraining.Text = "6" then
   Me.WorkRatePercentage.Text = "22%"
else
   ???????
end if

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[SkipVought]

maybe should be...

if Me.WeeksTraining.Text = "6" then
   Me.WorkRatePercentage.Text = "22%"
else
   Me.WorkRatePercentage.Text = ""
end if

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[bdmangum]

I'm assuming the WeeksTraining textbox is filled by the user and not when the form initializes? If so, make sure to Skip's code in the change event.

Private Sub WeeksTraining_Change()

If Me.WeeksTraining.Text = "6" then
   Me.WorkRatePercentage.Text = "22%"
Else
   Me.WorkRatePercentage.Text = ""
End if

End Sub

BD

 

[MIAnalyst]

Thanks very much for you help to both of you. That seems to work - just I have 52 weeks and different percetages for each so need to build that into the code

 

[mintjulep]

If there is some defined mathematical relationship between WeeksTraining and WorkRatePercentage you could use the formula rather than a 52-way if-then-else statement.

Otherwise, there are still better ways than a 52-way if-then-else statement.

Is there any valid reason why the user could, would, should be allowed to change the entry in WorkRatePercentage?

 

[MIAnalyst]

No, unfortunately, it's not as easy as a formula - would have loved it if it was! The percentage may fluctuate slightly on a couple of occasions so may need to be amended without me having to go in and change the code. I have three boxes for % which need to be linked to the one WeeksTraining box.

 

[bdmangum]

I'm not certain I understand exactly how you are planning to insert the different % values. Regardless, could you not create a hidden sheet which contains a table with the values? Then all you would need is a small segment of code to find the WeeksTraining value in the table and from that send the correct % value. Depending on your variants, this method might work the best for your needs, assuming I understand what your needs are.

 

[SkipVought]

"...it's not as easy as a formula ..."

Then what IS the logic?

If there is no defined logic, how do you expect to generate a value?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mikej336]

You might consider a CASE statement with each of the 52 weeks.

Simplify your code and keep it all together.

Private Sub CommandButton1_Click()

Select Case Me.WeeksTraining.Text
Case Is = 1
Me.WorkRatePercentage.Text = "22%"
Case Is = 2
Me.WorkRatePercentage.Text = "22%"
Case Is = 3
Me.WorkRatePercentage.Text = "22%"
Case Is = 4
Me.WorkRatePercentage.Text = "22%"
Case Is = 5
Me.WorkRatePercentage.Text = "22%"
Case Is = 6
Me.WorkRatePercentage.Text = "22%"
Case Else
Me.WorkRatePercentage.Text = ""
End Select


End Sub

Mike

 

[misterstick]

so may need to be amended without me having to go in and change the code

then this isn't a job for VBA.
use a hidden sheet and VLOOKUP()



mr s. <

 

[MIAnalyst]

SkipVought - there is a logic, just not a straightforward mathematical one, it's difficult to explain how.

Plus all the goalposts have just changed and I now have percentages relating to different weeks for different teams and skillsets.

Thanks for everyone's suggestions though

 

[SkipVought]

"...just not a straightforward mathematical one, it's difficult to explain how..."

If you cannot describe the logic, you will NEVER be able to design and code a solution!!!

"...Plus all the goalposts have just changed ..."

It is vital and essential that ALL requirements are written down and agreed to be the interested parties. When the requirements change, it often means that the design and logic has to be revisited, which impacts the cost and schedule of the delivered item. Just a fact of life.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[MIAnalyst]

For the percentages - this is one of the tables:

Week no OOE WR
1 0 0
2 10 20
3 20 30
4 30 40
5 40 50
6 40 50
7 45 55
8 45 55
9 50 60
10 50 60
11 55 65
12 55 65
13 60 70
14 60 70
15 60 70
16 65 70
17 65 75
18 65 75
19 65 75
20 65 75
21 70 80
22 70 80
23 70 85
24 70 90
25 70 95
26 75 100
27 50 100
28 50 100
29 55 100
30 55 100
31 60 75
32 60 80
33 65 85
34 70 95
35 75 100
36 75 100
37 75 100
38 75 100
39 75 100


As for goalposts being moved, this is something that happens weekly within our company. I only do internal reporting.

 

[bdmangum]

As long as a table of some sort exists you can populate the second textbox quite easily from the table. See my last post. If you want a code example let me know.

BD

 

[MIAnalyst]

I've spoken to the person who wants this and they've decided they're going to manually input the data as there's far too much to try and put in coding now for someone of my level.

Again I would like to thank everyone for all their help

 

[SkipVought]

Goalposts changing.

DATA can change. that is not a goalpost.

How the data is processed -- changing THAT goalpost poses problesm.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}