I am working on a project of excel vba. I have a user from for entry . The user form contain Seven Groups of Radio buttons.Every groups has Five option buttons.Now I want that a user must check every group of radio buttons.and when group remain unchecked the background of that group change to red color.and also a warning message should appear.Please give me a little hints for vba code.[ATTACH=CONFIG]68356[/ATTACH]
Guide for vba Radio buttons
-
-
-
Re: Guide for vba Radio buttons
Provide an example workbook
-
Re: Guide for vba Radio buttons
Please check the image.In this image Seven groups of radio buttons.Every group has 5 Radio button.I want to bound the user to check every group of option buttons.And if accidentally the user left uncheck any group of radio buttons,the back ground color of that group must change to red and as well as a warning message appear.
-
Re: Guide for vba Radio buttons
Provide an example workbook.
You could try something like this
-
Re: Guide for vba Radio buttons
Below is the code for first group of radio buttons having value when false or true.If a user leave this group unchecked,the back ground color of this group must be red.
Code
Display MoreIf optA1.Value = True Then ActiveCell.Offset(0, 4).Value = 2 ElseIf optA1.Value = False Then ActiveCell.Offset(0, 4).Value = 0 End If If optA2.Value = True Then ActiveCell.Offset(0, 5).Value = 2 ElseIf optA2.Value = False Then ActiveCell.Offset(0, 5).Value = 0 End If If optA3.Value = True Then ActiveCell.Offset(0, 6).Value = 2 ElseIf optA3.Value = False Then ActiveCell.Offset(0, 6).Value = 0 End If If optA4.Value = True Then ActiveCell.Offset(0, 7).Value = 2 ElseIf optA4.Value = False Then ActiveCell.Offset(0, 7).Value = 0 End If If optA5.Value = True Then ActiveCell.Offset(0, 8).Value = 2 ElseIf optA5.Value = False Then ActiveCell.Offset(0, 8).Value = 0 End If
-
-
Re: Guide for vba Radio buttons
Here optA1,optA2,optA3,optA4,optA5 are five radio buttons of first group.Similarly six more groups having each group Five radio buttons.I want that if any of these option group remain unchecked, the back ground of all the radio buttons to red and also a warning message appear.For example
if radio buttons of 1st group (optA1,optA2,optA3,optA4,optA5)=False then the background color becomes change with a warning message.Thanks -
Re: Guide for vba Radio buttons
[sw]*[/sw]
-
Re: Guide for vba Radio buttons
I have upload the simple file.Thanks for your nice co-opration
-
Re: Guide for vba Radio buttons
You could change the code to
Code
Display MorePrivate Sub CommandButton1_Click() If OneOfEachOptionIsClicked Then ' existing save to sheet code Sheets("Entry").Activate Range("D8").End(xlDown).Offset(1, 0).Select 'ActiveCell.Offset(0, 0).Value = DTPicker1.Value ActiveCell.Offset(0, 1).Value = ComboBox1.Value ActiveCell.Offset(0, 2).Value = TextBox3.Value ' etc Else MsgBox "select an option from the indicated groups." End If End Sub Function OneOfEachOptionIsClicked() As Boolean Dim oneControl As Variant For Each oneControl In Me.Controls If TypeName(oneControl) = "OptionButton" Then If oneControl.Value Then oneControl.Parent.Tag = "OK" End If Next oneControl OneOfEachOptionIsClicked = True For Each oneControl In Array(Me.Frame3, Me.Frame4, Me.Frame5, Me.Frame6, Me.Frame7, Me.Frame8, Me.Frame9) If oneControl.Tag <> "OK" Then oneControl.BackColor = vbRed OneOfEachOptionIsClicked = False Else oneControl.BackColor = Me.BackColor End If oneControl.Tag = vbNullString Next oneControl End Function
Also, this code
CodeIf optA1.Value = True Then ActiveCell.Offset(0, 4).Value = 2 ElseIf optA1.Value = False Then ActiveCell.Offset(0, 4).Value = 0 End If
can be replaced by -
Re: Guide for vba Radio buttons
Your whole DataEntry code could be changed to this:
Code
Display MorePrivate Sub CommandButton1_Click() If OneOfEachOptionIsClicked Then Rem write to sheet With Sheets("Entry").Range("D8").End(xlDown).Offset(1, 0) .Offset(0, 0).Value = DTPicker1.Value .Offset(0, 1).Value = ComboBox1.Value .Offset(0, 2).Value = TextBox3.Value .Offset(0, 4).Value = (-2 * CDbl(optA1.Value)) .Offset(0, 5).Value = (-2 * CDbl(optA2.Value)) .Offset(0, 6).Value = (-2 * CDbl(optA3.Value)) .Offset(0, 7).Value = (-2 * CDbl(optA4.Value)) .Offset(0, 8).Value = (-2 * CDbl(optA5.Value)) .Offset(0, 9).Value = (-2 * CDbl(optB1.Value)) .Offset(0, 10).Value = (-2 * CDbl(optB2.Value)) .Offset(0, 11).Value = (-2 * CDbl(optB3.Value)) .Offset(0, 12).Value = (-2 * CDbl(optB4.Value)) .Offset(0, 13).Value = (-2 * CDbl(optB5.Value)) .Offset(0, 14).Value = (-2 * CDbl(optC1.Value)) .Offset(0, 15).Value = (-2 * CDbl(optC2.Value)) .Offset(0, 16).Value = (-2 * CDbl(optC3.Value)) .Offset(0, 17).Value = (-2 * CDbl(optC4.Value)) .Offset(0, 18).Value = (-2 * CDbl(optC5.Value)) .Offset(0, 19).Value = (-2 * CDbl(optD1.Value)) .Offset(0, 20).Value = (-2 * CDbl(optD2.Value)) .Offset(0, 21).Value = (-2 * CDbl(optD3.Value)) .Offset(0, 22).Value = (-2 * CDbl(optD4.Value)) .Offset(0, 23).Value = (-2 * CDbl(optD5.Value)) .Offset(0, 24).Value = (-2 * CDbl(optE1.Value)) .Offset(0, 25).Value = (-2 * CDbl(optE2.Value)) .Offset(0, 26).Value = (-2 * CDbl(optE3.Value)) .Offset(0, 27).Value = (-2 * CDbl(optE4.Value)) .Offset(0, 28).Value = (-2 * CDbl(optE5.Value)) .Offset(0, 29).Value = (-2 * CDbl(optF1.Value)) .Offset(0, 30).Value = (-2 * CDbl(optF2.Value)) .Offset(0, 31).Value = (-2 * CDbl(optF3.Value)) .Offset(0, 32).Value = (-2 * CDbl(optF4.Value)) .Offset(0, 33).Value = (-2 * CDbl(optF5.Value)) .Offset(0, 34).Value = (-2 * CDbl(optG1.Value)) .Offset(0, 35).Value = (-2 * CDbl(optG2.Value)) .Offset(0, 36).Value = (-2 * CDbl(optG3.Value)) .Offset(0, 37).Value = (-2 * CDbl(optG4.Value)) .Offset(0, 38).Value = (-2 * CDbl(optG5.Value)) End With Else MsgBox "select an option from the indicated groups." End If End Sub Function OneOfEachOptionIsClicked() Dim oneControl As Variant For Each oneControl In Me.Controls If TypeName(oneControl) = "OptionButton" Then If oneControl.Value Then oneControl.Parent.Tag = "OK" End If Next oneControl OneOfEachOptionIsClicked = True For Each oneControl In Array(Me.Frame3, Me.Frame4, Me.Frame5, Me.Frame6, Me.Frame7, Me.Frame8, Me.Frame9) If oneControl.Tag <> "OK" Then oneControl.BackColor = vbRed OneOfEachOptionIsClicked = False Else oneControl.BackColor = Frame2.BackColor End If oneControl.Tag = vbNullString Next oneControl End Function
-
-
Re: Guide for vba Radio buttons
Thanks I have done.I really appropriate your co-operation.You are experienced person.Thanks again.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!