help with Select Case

[tommy_mo]

Hello folks-

I have a question about using "select case"

I would like to use the code below to change the color of a textbox (text59) based on the combo box (Status) text. It works well, but I have other textboxes and combo boxes that I would like to perform the same. Is there a way to loop this for other controls using the same on open event? Cheers to anyone willing to help! -Tom


Private Sub form_Open()

Select Case Me.[Status]
Case Is = "Completed study"
Forms!mainsubform!Text59.BackColor = vbGreen
Case Is = "Dropped from study"
Forms!mainsubform!Text59.BackColor = vbRed
Case Is = "Rejected"
Forms!mainsubform!Text59.BackColor = 65535
Case Else
Forms!mainsubform!Text59.BackColor = 16777215
End Select

 

[WayneRyan]

Tommy,

Private Sub form_Open()

Select Case Me.[Status]
   Case "Completed study"
         Forms!mainsubform!Text59.BackColor = vbGreen
   Case "Dropped from study"
         Forms!mainsubform!Text59.BackColor = vbRed
   Case "Rejected"
        Forms!mainsubform!Text59.BackColor = 65535
   Case Else
        Forms!mainsubform!Text59.BackColor = 16777215 
End Select

Wayne

 

[pbaldy]

Several ways, depending on your exact situation. One:

Private Sub form_Open()

Select Case Me.[Status]
...
End Select

Select Case Me.[AnotherOne]
...
End Select

More info about the goals would probably yield a better response.

 

[tommy_mo]

Thanks for the responses! Some more info:

I have a form called "mainform" with 12 textboxes that are associated with tabs (12 total tabs) on a subform called "subform_study_details". I would like the associated textbox on the main form to change color based on a combo box located on each tab. Each combo box has about 10 text values, but I'm only concerned with flagging 3 or 4 with green or red. I can get the code to work by using an update event for each combo box, but each textbox already has values that I would like to flag with color. Hope this helps. Cheers! -Tom

 

[WayneRyan]

Tom,

Using the Update event for each textbox is not a bad thing.

If you wanted to centralize the code, you could use the BeforeUpdate
or AfterUpdate event for the form. That way you'll only have one
occurrence of your code.

Wayne

 

[The_Doc_Man]

Make the relevant code into a subroutine with two arguments: a control and the status string.

Public Sub SetStatusColor( ctlX as control, Status as string)

Select case Status

Case "status1" ctlx.backcolor=color1
Case "status2" ctlx.backcolor=color2
Case "status3" ctlx.backcolor=color3
...
else ctlx.backcolor=defaultcolor

end sub

Then activate it as

SetStatusColor me.boxA, status
SetStatusColor me.boxB, status

etc. etc.

If this is to be used on one form only, it can be private in the form's class module. If you might wish to use this more than one place, try a general module and in that case it must be public - because the references to it will still be in the form's class module and therefore will have to cross module boundaries. But this is no biggie.

Warning - not all controls have a back color. I think, for example, radio buttons do not. So don't call it if you don't mean it.

 

[tommy_mo]

Hey Doc-

I like your idea, but I have to say that I have no experience with modules. Is there any way you could explain your suggestion in more detail so I can give it a shot. I do plan on using just one form by the way. Cheers! I'll try to work through your suggestion in the mean time. -Tom