• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Radio Button on No Selection

Dear Ninja,

I need help on the excel functions or formulas to radio button disables especially the No selection.

How do you disable after I selected the No radio button from the completed school attributes (reason: not completed education) and it will automatically disable the radio button for the got certificate and interest course attributes to prevent the wrong information.

Please view the upload in excel.

Let me know if there are any queries.

Thank you very much
 

Attachments

Dear Ninja,

I tried to copy the coding from other sources and see whether ninja can assist:-
Note: Not familiar the vba coding but tried on the disabled button.

Code:
With ActiveSheet

    s = ActiveSheet.Shapes(Application.Caller).Name

    Groups = Filter(Groups, s, 0)

    For Each Op In .OptionButtons

        Op.Enabled = 1

    Next

    Select Case s

     Case "Got_Certificate"

            For Each Op In .OptionButtons

                If Op.Name <> s Then Op.Value = 0

            Next

            .OptionButtons(s & 1).Value = 1

            'Write your code here

    End Select

    For Each k In Groups

        For i = 1 To 3

            .OptionButtons(k & i).Value = 0

            .OptionButtons(k & i).Enabled = 0

        Next

    Next

End With

End Sub

Sub Got_Certificate()

Dim b As Object

With ActiveSheet

    If .OptionButtons(1).Value = 1 Then

        For Each b In .OptionButtons

            b.Enabled = 1

        Next

        For i = 4 To 9

            .OptionButtons(i).Value = xlOff

            .OptionButtons(i).Enabled = 0

        Next

    End If

End With

End Sub
 

Attachments

Dear Ninja,

Good evening,

I am using a normal excel to complete the survey on the attributes of:-
Name: [Completed]
Gender: [Completed]
Phone: [Completed]
Address: [Completed]
Email: [Completed]


Ninja, How to solve the radio button when I selected the no radio button from the complete school?
Once I selected the no button, the attributes for Go Certificate and Interest Course are disabled.
Does this vba coding help this matter?

Kindly assist with thanks.
 

Attachments

Dear vletm,

Yea, I think only dummies like me is trying to copy here and there till I hit the wall badly for an Excel Ninja.

Thank you for your good ideas which I never taught your simple VBA codes are well understand after I sat there and read your coding for a few hours, trying to understand. It just that I stuck on how to structure it into simple coding. So, I write a few comments that I can understand and the rest is still blurred, which will learn from time to time.

Since your idea is good, especially the no selection that can disable the rest of the attributes, could I ask another 4 additional questions as below:-

1) How to outline the selection box that looks like a green selection button?

2) If I select yes at the Completed School attributes, it shows auto-selected the rest of the attributes. How can I give them the option to select it?
(I have tried another dummy copy from your code, but at the end stuck again.)

If .Range("C11").Interior.ColorIndex = 4 Then _

.Range("C13:D20").Interior.ColorIndex = 4

3) How to create a simple error handling of an idiot like me who doesn't know if I select the no button and I still hit the button for the rest of the attributes?

4) Is the radio button a bit confusing to work out?

Hope to hear from you, Ninja.

Thank you very much:)
 

Attachments

vletm

Excel Ninja
Falinaicare
You used something, which starts with s-letter. That would means hours and hours extra work. I would write about possible sample code.
Many other written codes would take time to figure ... what?
Questions are always welcome ...
1) It have to 'stop' code while do any modifications. ( eg to add Exit Sub to beginning of that code )
... and later have to remember to remove it too.
2) I would use term like "It will show those as You have selected before 'No'".
... or why those should able to do selections, if those cannot effect ...hmm?
3) Before hit something (or someone) should 'think' - is it wise to do something
... if someones challenge is that someone can only 'click-click...' or 'hit-hit...' then there would be more challenges.
Someway same as 'measure twice - cut once' -phrase ...
Of course, there could add eg some kind of routine, that user should verify 'no' ... but soon that would feel bad for user!
4) I checked Your radiobuttons and there were many copy and paste ... somethings ...
and You had idea to disable some selection ... but if I remember, those radiobuttons can be visible ... or not.
Of course, those Yes/No could show 'my way' more like 'option-buttons' ... but I tried to keep my sample - basic.
 

Attachments

Dear vletm,
Yeap, all in any letter s that I can think of in doing the small VBA are searching, sacrificing, seeing, and at a snail's gallop it takes many hours if you know based on your experience. "
As for me, still, a long way to go and learn.

Answer for the Sheet One - Horizontal
1) Do you mean the "Exit Sub" at the below of the first line?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Exit Sub
2) Yea, I still like how you disabled using the no button. That's a good point. Hmm, how can I refresh the form as a new form so that a new student can select again?
3) New idioms - " Measure twice, cut once". I think I shall put the sentence at the spreadsheet to inform the people who use the survey as a measurement. " Select one of the requirements below" rather than hit-hit and click click for fun. Completed at Sheet one, B10
Note: You should call yourself Excel and English Ninja or E&E Ninja. Great English.
4) The radio button is in the different names of each group but cannot disabled.
I have seen their resources in VBA code but I don't understand at all and copy also wrong.:confused:

Okay, vletm, I have renamed the Sheet One is called Horizontal and the sheet two is called Vertical. Attached file.

In the sheet two - Vertical, could you write your VBA simple code that has all in vertical?
1) Gender - Select Male or Female
2) Reading Category - Select either Classic, Fantasy or comedy
3) Book Name - Select either Samual Classic, Final Fantasy or Laughter the best medicine

I have using your code earlier to put into the second sheet - vertical but stuck again on the row, which no idea what to write next?

Hope to hear from you, E&E Ninja.

Thank you very much. :)
 

Attachments

vletm

Excel Ninja
Falinaicare
These codings would need ...hmm? sitting & coding ... and have to learn to have breaks.

1) Yes
2) I added a button
3) That's old phrase ... and ... there are many variations ... all of those are useful with many cases.
4) With forms, those maybe could also disable, but 'my way' is basic way to do these.

Your: What to write next?
... especially partially copy & past works ... if ... and ... if ... otherwise something else!
... You should try to figure - how would do it ... manually.

Now, You know something ... and ... after five years ... You would figure, how would those work ... or not.
 

Attachments

Dear vletm,

I have tried to do another sheet 3 called CarService by using your Horizontal and Vertical sheet of codes = no whereo_O.

Could you point where is my error? :confused:Attached files.

Ok, here is the requirement:-
1) Car Service Package - When I select the inspection button, it will disabled all other selection in oil service and other services column.
2) Car Service Package - When I select Basic, only normal oil at the Oil Service column and Other Services column for selection
3) Car Service Package - When I select either upgrade or advance, only Synthetic Engine Oil or Semi - Synthetic Oil column and Other Services Column for selection.

Remarks: the top XXXXXXXX are the rules and regulations on car service. Just for a sample of the form.

Hope to hear from you. Let me know if you have doubt on the requirement.

Thanks.
 

Attachments

Dear vletm,

Correct me if our understanding is two different worlds and I respect you as a good ninja who sparks that knowledge throughout your great experience.

Yes, I saw and read your last 4 lines as below:-
Your: What to write next?
... especially partially copy & past works ... if ... and ... if ... otherwise something else!
... You should try to figure - how would do it ... manually.
Now, You know something ... and ... after five years ... You would figure, how would those work ... or not.
Yeap, it is a long process after five years to figure out or otherwise. That's why I am creating a new sample and I tried to use your codes and see how if I am capable to handle it but unfortunately fail again. That's why I am here looking for ninja and learn from mistakes.

Sincerely, your codes can understand even though is not 100%, and at least I can know 10% from your simple coding. What is your comment when I submitted my CarService coding to you?

Questions for you here:-
1) In the Inspection button, you have disabled the oil service column. How about disable the other service column?
(I have uploaded the excel spreadsheet)
2) How do you managed to get the green outlines? also by coding?
3) Does the button need to merge as one for example, Inspection button at the cell C25 and D25?
4) What is the difference between using With.Range and Range only?
5) Why do we need to quote the .Cells(1,ax).Activate? What does it mean?
6) In this codes as below:-
If .Range("C25").Interior.ColorIndex <> 4 Then
For y = 24 To 30
For x = 12 To 13
If Not .Cells(y, x).Locked Then .Cells(y, x).Interior.ColorIndex = 4
Next x
Next y
End If
What is the meaning of this symbol <> 4? Does the colorIndex recognize this symbol?
7) What do you mean by your sentence - "If You left other 'routines' there ... like background ... then those tries to work too." ?

Thank you for your assistance.
 

Attachments

vletm

Excel Ninja
Falinaicare
'My last four lines'
... that You should do those by steps ... You tried to jump.
There were many positive things too ... eg You did a new layout, but ... that copy & paste ... hmm?
Would You try to do this step-by-step?
a) Your new feature ... which someway would something.
b) 'Car Service Package' -selection
c) and after that You would add one-by-one those features
above means, there should be only needed code with every step!

Answers:
1) As You've written 'Inspection' is like a single procedure ... no matter what have You selected before .. or how?
... that's why I hide those 'no need to see'-features.
2) Manually
3) Your upper section ... those 'XXXXXXX' would be all same widths ... hmm?
or have to play with two cells ... and how to get texts look useful? ... okay, to split words with two cells ...
4) As You have noticed, I have used With Activesheet
then no need to write ActiveSheet.range(.... to be sure that always refer to correct sheet.
Other words ... we could have same phonenumber ... okay? ... but if I would phone to You, I would use ... Country code before phonenumber to make sure that I won't phone to ... myself.
5) To avoid possible editing those 'buttons' ... there are none protection
6) it checks has 'Inspection' NOT selected ( as green as 4 ) as answer #1
.Range("C25") as 'Inspection'-button
.Interior is ... 'interior'-feature of above

.ColorIndex is ... 'colorindex'- value of above
<> is ... as 'different'
Interesting question ...
7) Something same as ... would You try...? reply above ... or other words ...
You take a any song ...
if You could listen it only by one track ... eg singer ... other would be muted
next You could add there eg bass ... later other instruments
If You mix one song with other song ... those tracks ... of course, could match ... but many times not! ( You can try to mix eg Mozart and AC/DC )
( Your copy & paste could do something which You have not planned at all - side effects
as well as all medicines have some side effects ... eg medicine for headache could cause something for Your stomach ... )
 

Attachments

Dear vletm,

Thanks for your advise. I will try to do step by step - "Practice make Perfect".:cool:

My replied from your answers:-
1) I have solve the Inspection that can disabled the other services by changing the code as below:-
Code:
If Not .Cells(y, x).Locked Then .Cells(y, x).Interior.ColorIndex = xlNone
2) Manually? Do you mean you just use normal Excel to draw the outline?
3) I finally understand that, if you want to create a button that has 2 cells, for example, Inspection button at the cell C25 and D25, I have to merge both of the cell manually by using normal excel and coding it as C25 as one button.
4) It seems that VBA has many flexibilities coding like
Code:
With Activesheet
and
Code:
ActiveSheet.range(....
. That I have to explore it.
5) " To avoid possible editing those 'buttons' ... there are none protection " - that's a good one idea of yours.:)
6) ok
7) I do agree with you. Not all mixtures are good and there will be side effect or poison if it consume much of it.
How many years of experience do you have as a Excel Ninja?

Questions:-
1)
Code:
If Not .Cells(y, "F").Locked Then .Cells(y, "F").Interior.ColorIndex = 4
- Is this cursor will fly to cell F every time I accidentally clicked the button. Do you have any other idea that it will go out of the form rather than cell F and asking us to type of something.
2)
Code:
If .Range("D12").Interior.ColorIndex = 4 Then _
                            .Range("C14:D21").Interior.ColorIndex = xlNone
                            .Cells(ay, "F").Activate
Do you have any other way that the cursor will go to cell F? Could we put a pop up message rather than the cursor fly to the top?

Hope to hear from you.

Thank you very much.
 

Attachments

vletm

Excel Ninja
Falinaicare
1) As I tried to write based You logic ... no need to disable.
2) Manually ... each cell has also borders ... have You ever used those?
3) If wide text then it would need wider area to show.
... and soon, You also would like to solve ... how to do those separate "Yes" / "No" -buttons with one button
... text would change ... as well as background color green or none
4) There are many ways to do useful code, but sometimes - even one missing dot would change everything upside down
5) Of course, if sheet needs 'protect' then many things would need to do other way.
7) two plus

Your Questions:
1) Sheet can also 'protect' then .. as written above ... many thing would need to do other way.
2) Instead "F" could use number six ... what would popup show? what would happen after popup?
 
Dear vletm,

First Part:-

2) Manually borders - Yes like this right?1597046947065.png
3) Now I am okay with the current Yes/No button until it requires changing the text, in the future.
4) Totally agree with you. Only one silly error, the whole thing jammed.
7) :awesome:You are amazing - 2 years can become an Excel Ninja which you are a fast learner.

Second Part:-
Instead "F" could use number six ... what would popup show? what would happen after popup?
Hmm, VBA message box stated "Please select the correct button " especially on the Inspection button which all the buttons disabled.

New Questions:-
1) Why the case 25 to 26 on the first line and the second line it stated
Code:
 If .Range("C27").Interior.ColorIndex = 4 Or .Range("C28").Interior.ColorIndex = 4 Then
. Refer to the code below:-
1)
Code:
Case 6
                Select Case ay
                    Case 25 To 26
                        If .Range("C27").Interior.ColorIndex = 4 Or .Range("C28").Interior.ColorIndex = 4 Then
                            With .Range("F25:G26")
                                .Interior.ColorIndex = xlNone
                                .Locked = True
                            End With
2) If I want to concentrate on the code only the cell C27, (Upgrade button) then, choose either cell F25: G26, could I write:-

Code:
 Case 6
                Select Case ay
                    Case 27
                             .Range("F25:G26").Interior.ColorIndex = xlNone
                            With Target
                                .Interior.ColorIndex = 15
                                .Locked = False
                            End With
Hope to hear from you. Let me know if you have any feedback.

Thank you.
 

vletm

Excel Ninja
2) that's one possible way too
3) It's Your choice - Your steps
4) As You know - if You're looking for number two (2) for result ... there are many other numbers which won't match with correct result.
7) There are own criteria, how to come ...

Hmm, VBA message box stated "Please select the correct button " especially on the Inspection button which all the buttons disabled.
What would be the correct button? You missed - after?

Your new 1)
... based You written logic - when those 'button' could work? ... and if selection will change, what needs to update ...

Your 1) & 2)
... I won't start to think any new logic - that could mean many changes ... many time to write whole code again from zero!
... The logic should be clear before start to code ... it's possible to add something ... but even that would mean a lot of extra work!
... Why to use new color? Are You sure that there won't be any side effect cause that?
 
Dear vletm,

Good day

... I won't start to think any new logic - that could mean many changes ... many time to write whole code again from zero!
... The logic should be clear before start to code ... it's possible to add something ... but even that would mean a lot of extra work!
... Why to use new color? Are You sure that there won't be any side effect cause that?
This time is not write from zero, I am using your Car Service coding to do my Tyre Section. It is all the same, but name changes. It just that the structure of the column and row which need to enable and disable it.
Therefore, there is no new logic as it relates our topic to the button. Not the radio button, but your idea of a simple button.

Your new 1)
... based You written logic - when those 'button' could work? ... and if selection will change, what needs to update ...
Ok, if you see the spreadsheet on Tyre Section Sheet, I am okay with all the selections, if you click the "no changes", "engine changes" - both of them are all disabled.
Tyre Changes is to match either Change 4-tyres or Change 2-tyres and then tyre -add features, Yes/No.
Note: I have circled it in orange color for your reference, which I stuck from there.

What would be the correct button? You missed - after?
Please choose the correct button after a Tyre Changes Column
Note: I have to use the specific section so that the pop-up message is good to know.

Let me know if you have feedback.

Thanks.
 

Attachments

vletm

Excel Ninja
If You refer to other sheet - please, remember to give a clear hint...
'Tyre Section' ... there are ... engine still ...
as well as ... there were a lot of code which could effect the result ( copy & paste could work, if and if and if ... many times not! )
Did You try to do this ... step-by-step? ... if so then those non-working codes should be hidden.
I tried to figure Your ... challenge ... and I finally get something ... but only You'll know - what should be there?
Step-by-step eg...
#1 - solve 'left side'-buttons ( no matter of 'other buttons')
#2 - solve 'center'-buttons ( no matter of 'right side-buttons')
#3 - solve 'right side'-buttons
 

Attachments

Dear vletm,

From your coding, we are nearly there..... I explain more details in blue color text since my explanation is not comprehensive.

Step-by-step eg...
#1 - solve 'left side'-buttons ( no matter of 'other buttons') - Left button no problem. So far so good.
#2 - solve 'center'-buttons ( no matter of 'right side-buttons') - Yes, the hint is only the row - Tyre changes can only select either Change 4-tyres or Change 2 -tyres.
The engine button is the same as the no changes button (All disable on the column of Any Changes? and
Tyre-Add Features are valid.

#3 - solve 'right side'-buttons - so far okay from your previous codes.

I am still using back the previous excel spreadsheet for your assistance on the middle section #2.

Let me know if there are any queries.

Thank you very much.
 

Attachments

Dear vletm,

First and foremost, thanks for your patience and understanding. I have to explain again.

I have changed a few codes from your coding for the column no changes and engine changes. Attached the file.

Column Name:
No Changes - When I selected it, the codes are in good as it disabled. This is to tell the user that you cannot click the other column as it is not a tyre section.
Engine Changes - When I selected it, the codes are in good as it disabled. This is to tell the user that you cannot click the other column as it is not a tyre section.
Tyre Changes - This one yes, when I selected it, I can select either Change 4 - tyres or Change 2 - tyres. Thus, I can also select the Tyre-Add Features.
Can you assist in this section only?

The reason I am doing this is to tell the user only tyre section with all the tyre accessories or components are match.

Feedback to me again if there are any queries.

Thank you very much.
 

Attachments

vletm

Excel Ninja
I tried to figure Your ... dreams
but
I can and will use only my file
- especially after Your 'I have changed a few codes from your coding for the column no changes and engine changes.' = double time to check ... what?
I added that 'Tyre - Add Features'-selection...

Your Can you assist in this section only?
... I won't ... cannot ... only ... all matters!
 

Attachments

Top