Do ALL labels at ONCE

[Robert]

Hello,

My problem is the following I have a lot of labels which have to
change from color if a value is reached.

Private Sub UserForm_Click()

' # labels = 86
' height = 1.2
' fractions

y1 = (1 / 86) * 1.2
y2 = (2 / 86) * 1.2
y3 = (3 / 86) * 1.2
y4 = (4 / 86) * 1.2
y5 = (5 / 86) * 1.2
y6 = (6 / 86) * 1.2
y7 = (7 / 86) * 1.2
y8 = (8 / 86) * 1.2
y9 = (9 / 86) * 1.2
y10 = (10 / 86) * 1.2
y11 = (11 / 86) * 1.2
y12 = (12 / 86) * 1.2
'etc. till y86

If the Height (which is the value in Cell B14) is bigger then a
fraction the corresponding label should swich of colour

which I put like this:

If Cells(14, "B").Value > y1 Then label0.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y2 Then Label1.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y3 Then Label2.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y4 Then Label3.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y5 Then Label4.ForeColor = RGB(0, 0, 0)
etc..till label85 (since I started @ label0)

But there should be a easier way I guess:S.

And there is one more thing. I have a reset button placed on the
userform.

If clicked, then it should trigger the code to give all the labels the
initial colour.

I have this know also put as above. which 86 lines of code.

Any suggestions would be welcome.

 

[Guest]

Robert,

try something like this:

Dim y As Integer

For y = 1 To 86
If Range("B14").Value > (y / 86) * 1.2 Then
Me.Controls("Label" & CStr(y - 1)).ForeColor = RGB(0, 0, 0)
End If
Next y


for the reset button, assuming the initial color is red, then something like
this:


Dim y As Integer

For y = 0 To 85
Me.Controls("Label" & y).ForeColor = vbRed
Next y

 

[Susan]

hi robert
try this........ will take some extra effort @ first, but then will
all be set.
for each label, under properties, insert a tag (all the same).
for demonstration purposes i'll use "blue"
so all your labels have the tag "blue".

dim ocontrol as control
dim rCell as range, c as Range
dim rMyRange as range

set rCell = worksheet.Cells(14, "B")
set rMyRange = worksheet.range("y1:y85")

for each c in rMyRange
if < rCell then
for each ocontrol in me.controls
if typeof ocontrol = msforms.labels then
if ocontrol.tag = "blue" then
ocontrol.ForeColor = RGB(0, 0, 0)
end if
end if
next ocontrol
end if
next c

WARNING - UNTESTED! SAVE A COPY OF YOUR SPREADSHEET BEFORE TRYING!

to change all the ocontrols back to the same color, you'd just take
out the "if" statements & loop thru the for-each-next cycle once time.
hope it helps!
susan

 

[Rick Rothstein \(MVP - VB\)]

My problem is the following I have a lot of labels which have to

change from color if a value is reached.

Private Sub UserForm_Click()

' # labels = 86
' height = 1.2
' fractions

y1 = (1 / 86) * 1.2
y2 = (2 / 86) * 1.2
y3 = (3 / 86) * 1.2
y4 = (4 / 86) * 1.2
y5 = (5 / 86) * 1.2
y6 = (6 / 86) * 1.2
y7 = (7 / 86) * 1.2
y8 = (8 / 86) * 1.2
y9 = (9 / 86) * 1.2
y10 = (10 / 86) * 1.2
y11 = (11 / 86) * 1.2
y12 = (12 / 86) * 1.2
'etc. till y86

If the Height (which is the value in Cell B14) is bigger then a
fraction the corresponding label should swich of colour

which I put like this:

If Cells(14, "B").Value > y1 Then label0.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y2 Then Label1.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y3 Then Label2.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y4 Then Label3.ForeColor = RGB(0, 0, 0)
If Cells(14, "B").Value > y5 Then Label4.ForeColor = RGB(0, 0, 0)
etc..till label85 (since I started @ label0)

But there should be a easier way I guess:S.

I am pretty sure that the all of the above can be reduced to this one-liner
Click event procedure...

Private Sub UserForm_Click()
Me.Controls("Label" & CStr(Int(213 * v / 3) - 1)).ForeColor = RGB(0, 0,
0)
End Sub

Give it a try and let me know.

Rick

 

[Susan]

well, now, that's certainly much more efficient than mine!
for us not-gurus, could you explain that one-liner?
like, where are you getting CStr? the label name?
and where is the (213*v/3)-1 ?

thanks!
susan

 

[Robert]

Thank you all for the great tips!

I tried the first and that made me very happy. Learned another skill.

Susan, I stored your code. Pretty sure I can use it in the future.

And then, Rick, I tried the simplified oneliner, but it doest work. I
am pretty much sure that it has also to do with my lack of vba skills
so far.

It is necessairy for the labels to swich code when the value in cell
B14 is bigger as one of the fractions.

Thanks again! A lot!!

 

[Rick Rothstein \(MVP - VB\)]

And then, Rick, I tried the simplified oneliner, but it doest work. I

am pretty much sure that it has also to do with my lack of vba skills
so far.

Of course the code didn't work... I forgot to translate my test code (using
a variable "v") to your actual case (using the contents of cell "B14").
Sorry for the confusion that might have caused.

Here is the revised code which should work fine for you now....

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(213 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(255, 255, 255)
End Sub

Give it a try and let me know how it works.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Of course the code didn't work... I forgot to translate my test code
(using a variable "v") to your actual case (using the contents of cell
"B14"). Sorry for the confusion that might have caused.

Here is the revised code which should work fine for you now....

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(213 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(255, 255, 255)
End Sub

Give it a try and let me know how it works.

Whoops! Sorry, more test code problems... the 255's in the RGB function
should be 0's to match your original posting...

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(213 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(0, 0, 0)
End Sub

Now that one-liner **should** work for you... give it a try.

Rick

 

[Rick Rothstein \(MVP - VB\)]

well, now, that's certainly much more efficient than mine!

for us not-gurus, could you explain that one-liner?
like, where are you getting CStr? the label name?
and where is the (213*v/3)-1 ?

First, let's straighten out the one-liner so it refers to what the OP
wanted... I accidentally left in my test code variable ("v") instead of
using the Cell the OP wanted ("B14"). Here is the corrected code...

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(213 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(0, 0, 0)
End Sub

Now, with this corrected code... do you still have questions?

Rick

 

[Robert]

First, let's straighten out the one-liner so it refers to what the OP
wanted... I accidentally left in my test code variable ("v") instead of
using the Cell the OP wanted ("B14"). Here is the corrected code...

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(213 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(0, 0, 0)
End Sub

Now, with this corrected code... do you still have questions?

Rick

I kind of get it, but what about the 213 and divide by 3. It does seem
to work though.


However, I am using the first reply to my question that really seems
to do the trick for me except one thing:
_____________________________________________________________________________________________
Private Sub SimulationStart_Click()
'button on userform

Dim y As Integer

Call ProcessCalculate

For y = 1 To 86
If Range("B14").Value > (y / 86) * 1.2 Then
Me.Controls("Label" & CStr(y - 1)).BackColor = vbBlack
Me.Controls("Label" & CStr(y - 1)).BorderColor = vbBlack
End If
Next y

AND HERE IS MY PROBLEM (explained below).

If Range("B14").Value <= 1.2 Then..... START FROM TOP.
_____________________________________________________________________________________________

Perhaps I should ask this in another topic but the problem is that
"macro processcalculate" all of the calculation steps executes. This
calculations create a new value in cell B14 untill it sets to >1.20

You see. the thing I am creating is an emptying vessel. And with the
current approach I do not get to see a dynamic picture but just at
once a turn of all the labelcolors.

 

[Susan]

if you wanted to see it happening, i think you'd have to insert a
pause in there before the end if......
i searched the newsgroup:
************************
Example 1:
This example pauses a running macro for approximately 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

This example displays a message indicating whether 10 seconds have
passed.

If Application.Wait(Now + TimeValue("0:00:10")) Then
MsgBox "Time expired"
End If
************************
Example 2:
Sub MyWait(PauseSeg As Single)
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub

Sub Test
MyWait 2 'This pauses 2 second.
MyWait 0.5 'This pauses half second. (Not on Mac)
End Sub
************************
Example 3:
link to newsgroup post:
Slow Down the Macro to View
http://groups.google.com/group/micr...slow+down+macro&rnum=1&hl=en#68acf7e232126853
***********************
none of these are mine - "borrowed" off the newsgroup!

susan

 

[Rick Rothstein \(MVP - VB\)]

First, let's straighten out the one-liner so it refers to what the OP

I kind of get it, but what about the 213 and divide by 3. It does seem
to work though.

Damn, another mistyping! The 213 was supposed to have been 215 (the values
you used were probably such that you didn't get close to the point where the
difference would show up in the calculation). The corrected statement....

Private Sub CommandButton1_Click()
Me.Controls("Label" & CStr(Int(215 * Cells(14, "B"). _
Value / 3) - 1)).ForeColor = RGB(0, 0, 0)
End Sub

Anyway, the 215 and 3 come from the 1.2 and 86 in your original post. 86/1.2
equals 71.6666666666667 which obviously is 71-2/3. If you change that to an
improper fraction, it becomes 215/3 (the numerator is calculated as
3*71+2=215). The location for the 215 and 3 in the formula comes from
rearranging the inequalities in your original If-Then listing; for example,
the general Xth case...

If Cells(14, "B").Value > yX Then

where yX comes from this...

yX = (X / 86) * 1.2

Using V for Cells(14, "B").Value so it is easier to see the algebraic
manipulations, we have...

V > (X / 86) * 1.2

which, since we want X, rearranges to...

1.2 * V > X / 86

which becomes

1.2 * V * 86 > X

71.666667 * V > X

215 * V / 3 > X

Putting the original Cell reference for V back in gives...

215 * Cells(14, "B").Value / 3

Rick

 

[Rick Rothstein \(MVP - VB\)]

See inline....

However, I am using the first reply to my question that really seems
to do the trick for me except one thing:
_____________________________________________________________________________________________
Private Sub SimulationStart_Click()
'button on userform

Dim y As Integer

Call ProcessCalculate

For y = 1 To 86
If Range("B14").Value > (y / 86) * 1.2 Then
Me.Controls("Label" & CStr(y - 1)).BackColor = vbBlack
Me.Controls("Label" & CStr(y - 1)).BorderColor = vbBlack
End If
Next y

When did BackColor and BorderColor come into play? Your original post only
mentioned ForeColor. That would require a minor modification to my one-liner
(which can no longer be a one-liner)...

Private Sub CommandButton1_Click()
Dim LabelNumber As String
LabelNumber = CStr(Int(215 * Cells(14, "B").Value / 3) - 1)
Me.Controls("Label" & LabelNumber).BackColor = vbBlack
Me.Controls("Label" & LabelNumber).BorderColor = vbBlack
End Sub

AND HERE IS MY PROBLEM (explained below).
If Range("B14").Value <= 1.2 Then..... START FROM TOP.
_____________________________________________________________________________________________

Perhaps I should ask this in another topic but the problem is that
"macro processcalculate" all of the calculation steps executes. This
calculations create a new value in cell B14 untill it sets to >1.20

You see. the thing I am creating is an emptying vessel. And with the
current approach I do not get to see a dynamic picture but just at
once a turn of all the labelcolors.

I guess I am not completely following what you want to do. The value in B14
is "fixed" when you run your macro... only one Label will meet the range
criteria you posted in your original posting... that one Label is what my
code changes. In the paragraph above, you seem to be saying B14 will have
some kind of continuous changing value... can you clarify that please?


Rick

 

[Robert]

which becomes

1.2 * V * 86 > X

71.666667 * V > X

215 * V / 3 > X

Putting the original Cell reference for V back in gives...

215 * Cells(14, "B").Value / 3

Rick

Wow, that´s great!

I havent been using alot of math in my codes, but it does cleans up
the mess!

Thanks again for your help! 5 stars

 

[Rick Rothstein \(MVP - VB\)]

which becomes

Wow, that´s great!

I havent been using alot of math in my codes, but it does
cleans up the mess!

I was a Mathematics major many, many, many years ago in college... I can't
help myself except to see things in a mathematical light.

Thanks again for your help!

You are more than welcome... it was my pleasure.

Rick