PDA

View Full Version : [SOLVED] VBA Code - Set up condition to an Option Button



gary2014
09-07-2014, 11:21 AM
Hi,

I'm in final stage of my project. I need your help in this critical hour.

I set up 3 "Option Buttons" viz, "Korfball", "Sepaktakraw" and "Kabbadi".

I want to set up a condition to one of the Option Button "Kabbadi".

The Option Button "Kabbadi" should activate only when there is a value in cell A27 or B28. (code enclosed in for reference)
or
The ability to click on the Option Button "Kabbadi" should be enabled only when there is a value in cell A27 or B28. (code enclosed in for reference)

12237

I request you to help me with this so that I can complete my project.

SamT
09-07-2014, 12:13 PM
In the UserForm_Initialize sub

With Sheets("Sheet Name Here")
If (Range("A27") = "") And (Range("B28") = "") Then
Me.Controls("Kabbadi").Enabled = False
Else
Me.Controls("Kabbadi").Enabled = True
End If
End With

You can use the # Icon to place Code tags around your code to format it like this. Click the icon and paste the code between the two code tags, or Select the code and click the Icon.

BTW, I would refactor those three Control_Click subs to

Private Sub Control_Click()
ShowPictures
End Sub

Then I would add
Sub ShowPictures()
With Me
.Pictures(picKorfball").Visible = .Korfball.Value
Etc
Etc
End with
End Sub

gary2014
09-07-2014, 12:28 PM
Thank you, Sam.
Let me try it.

gary2014
09-07-2014, 12:31 PM
If I'm unable to do it, I'll share the file with you through "zippyshare.com.

Will you help me if I share the file? (In case I'm not able to fit the above close)?

SamT
09-07-2014, 01:25 PM
Share the file by uploading it here.

At the bottom of the Post Editor, you see "Go Advanced". In the Advanced Editor Page, below the Editor, you will see "Manage Attachments."

BTW, anything you have written in the Basic Editor will be carried over to the Advanced Editor for you.

gary2014
09-07-2014, 01:53 PM
Sure, I am attaching you a file for your reference.

Kindly help me with the VBA code

SamT
09-07-2014, 02:42 PM
Whoops! That is not a UserForm, it is a Worksheet. Worksheets don't have an Initialize Event, but they have an Activate Event. In order to allow the control to change state, I used the Worksheet's Change Event.

Worksheet controls must be refered to as OLEObjects or Shapes, except for their Event Subs.

Add these two subs to your Project in the Worksheet Code Page.

Private Sub Worksheet_Activate()
If (Range("A27") = "") And (Range("B28") = "") Then
OLEObjects("Kabbadi").Enabled = False
Else
OLEObjects("Kabbadi").Enabled = True
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If (Range("A27") = "") And (Range("B28") = "") Then
OLEObjects("Kabbadi").Enabled = False
Else
OLEObjects("Kabbadi").Enabled = True
End If
End Sub

gary2014
09-07-2014, 10:13 PM
Excellent!!!!
That worked!!
You have an excellent knowledge in VBA.......
Thank you once again.