Solved

Change button text color on option group change.

Posted on 2015-01-21
7
500 Views
Last Modified: 2015-01-21
Been a while since I took a stab at vba and access, but I have a simple problem.

I have an option group with 6 buttons, when one is pressed I'd like to bold and set the text color to red.

I realise I could use if or case to nest all 6 possible senarios, but seems needlessly messy. Such as this.

Select Case optPlant
    Case 1
        btn1.ForeColor = 255
        btn2.ForeColor = 0

Open in new window


What would seem ideal would be to call a for next loop to loop through all the toggle button options in the frame. Something like this, I just don't know the correct syntax for the controls under an option frame. Just seems like that would be cleaner since adding more buttons just gets really messy with the case way.

for each btn in optPlant.buttons
 if btn = pressed then 
    btn.forecolor =255
 else 
   btn.forecolor = 0
  end if
next

Open in new window

0
Comment
Question by:bhieb
7 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 40563182
try this codes

Private Sub optPlan_Click()
Dim j As Integer, x As Integer
j = Me.optPlan.Value
For x = 1 To 6
    Me("btn" & x).ForeColor = 0
Next
Me("btn" & j).ForeColor = 255
End Sub
0
 

Author Comment

by:bhieb
ID: 40563187
That would require the buttons to be named a certain way. Doable, but I'd like to keep the program as universal as possible. So the next guy behind me can just add another button and away they go.

Making some headway here on my own.  I can loop the controls like so, but how do I then add the validation to see if the
control is the one pressed and if so turn it red.  

This gives me their names, and from that I can do things such as y.forecolor = 255
For Each c In optPlant.Controls
   y = c.Name
Next c

Open in new window

0
 

Author Comment

by:bhieb
ID: 40563191
Actually 2 validations. One is the control a button (since the frame and label are also there, and if it is a button is it pressed.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:PatHartman
ID: 40563211
Access doesn't have any control arrays and the buttons in an option group don't have a value property.  Only the option group control has a value.  You have to change the label of the button.

            Me.Label51.ForeColor = vbRed

if you rename all the labels so they have a prefix followed by the button value, you should be able to concatenate the two to get the name.  I don't have time to figure out what syntax might work but it would be something like:
Controls("lbl" & Me.OptionGroupName).ForeColor

The problem is that although this will set one of the options to red, it doesn't address the others and I don't know what will happen if you reuse the option group.  Will all the labels eventually turn red?  So, you'll probably need a loop that sets the forecolor back to the default for all the labels.
0
 

Author Comment

by:bhieb
ID: 40563213
Figured it out Rey. Gonna toss you the points since your way would have worked. Here is the final code.

Dim ctlVar As Control

For Each ctlVar In optPlant.Controls
  If ctlVar.ControlType = acToggleButton Then
      If ctlVar.OptionValue = optPlant Then
        ctlVar.ForeColor = 255
      Else
        ctlvar.ForeColor = 0
      End If
End If
Next ctlVar

Open in new window

0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 250 total points
ID: 40563219
You can use the Controls collection of the option group.  Note that the collection includes any attached label, so you must check the ControlType:
Private Sub OptGroupName_AfterUpdate()
Dim ctl As Access.Control
For Each ctl In OptGroupName.Controls
  With ctl
    If .ControlType = acToggleButton Then
      If OptGroupName.Value = .OptionValue Then
        .ForeColor = 255
      Else
        .ForeColor = 0
      End If
    End If
  End With
Next ctl
End Sub

Open in new window


Good luck!
Graham Mandeno [Access MVP 1996-2015]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40563224
I am not great at loops like this but this seems to work OK for me...
Private Sub Form_Current()
    Call SetOptProps
End Sub

Private Sub Frame10_AfterUpdate()
    Call SetOptProps
End Sub

Private Sub SetOptProps()
If Me.Frame10 = 1 Then
    Me.Label14.FontBold = True
    Me.Label14.ForeColor = 255
    
    Me.Label16.FontBold = False
    Me.Label16.ForeColor = 0
    
    Me.Label18.FontBold = False
    Me.Label18.ForeColor = 0
ElseIf Me.Frame10 = 2 Then
    Me.Label14.FontBold = False
    Me.Label14.ForeColor = 0
    
    Me.Label16.FontBold = True
    Me.Label16.ForeColor = 255
    
    Me.Label18.FontBold = False
    Me.Label18.ForeColor = 0
ElseIf Me.Frame10 = 3 Then
    Me.Label14.FontBold = False
    Me.Label14.ForeColor = 0
    
    Me.Label16.FontBold = False
    Me.Label16.ForeColor = 0
    
    Me.Label18.FontBold = True
    Me.Label18.ForeColor = 255

 End If

End Sub

Open in new window


Sample DB is attached, FWIW
Database54.mdb
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now