Excel 2016 VBA Option button or radio button VBA code.

[Windy borough]

Hi,

I have a very simple userform which I made to transfer data from form to the sheet. I managed to set up and have everything working apart from the radio buttons. Their functionality is not working. I want to achieve the purpose of the userform but radio buttons are doing my head in, to be honest.

The code is:

Private Sub CommandButton1_Click()

Dim dcc As Long
Dim abc As Worksheet

Set abc = Worksheets("Key List")

With abc

dcc = .Range("A" & Rows.Count).End(xlUp).Row

.Cells(dcc + 1, 1).Value = Date
.Cells(dcc + 1, 2).Value = Me.TextBox1.Value
.Cells(dcc + 1, 3).Value = Me.TextBox2.Value
.Cells(dcc + 1, 4).Value = Me.OptionButton1.Value
'.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
.Cells(dcc + 1, 6).Value = Me.TextBox3.Value

If OptionButton1.Value = True Then

.Cells(dcc + 1, 4).Value = "Yes"

End If

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

End With

End Sub


It's a very simple form to achieve a simple data transfer but I can not manage to make radio buttons working! I need help with VBA code and if somebody can provide the code which I can use, I'd appreciate it very much. I do not have VBA background and I struggle to explain using excal vba terms. Only one of the radion buttons needs to be selected, its logical I think that if something is in then it can not be out at the same time. Both options can not be selected. I have them grouped them separately because there are two separate columns on sheet for each radio button. Key is in Key in group and key out is in key out group.

I really need help with this last bit of userform and if anybody can kindly help me out here I'd be very grateful and appreciate it very much. Thanks in advance. Cheers.

 

[Windy borough]

@dmt32 I did tried your code, honest! I also tried so many other codes that it got so long that I was just confused that what's what. I had the working code before your code, I copy pasted it and that's the file I uploaded. I appreciate your help, who would've known it's the font colour. Apparently you and fluff are the ones lol. I'll try the code and update in a min. Appreciate your help. Cheers.

 

[Fluff]

If you want "Yes" in either col D or E depending on which option button is selected try

Private Sub CommandButton1_Click()

   Dim dcc As Long
   Dim abc As Worksheet
   Dim Yc As Long
 
   Set abc = Worksheets("Key List")
   Yc = Choose(Me.OptionButton1 + 2, 4, 5)

   With abc
   
      dcc = .Range("A" & Rows.Count).End(xlUp).Row
      
      .Cells(dcc + 1, 1).Value = Date
      .Cells(dcc + 1, 2).Value = Me.TextBox1.Value
      .Cells(dcc + 1, 3).Value = Me.TextBox2.Value
      .Cells(dcc + 1, Yc).Value = "Yes"
      '.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
      .Cells(dcc + 1, 6).Value = Me.TextBox3.Value
   
   End With
   
   TextBox1.Text = ""
   TextBox2.Text = ""
   TextBox3.Text = ""
   Me.OptionButton1 = 0
   Me.OptionButton2 = 0

End Sub

This will also reset the option buttons.

 

[dmt32]

@dmt32 I did tried your code, honest! I also tried so many other codes that it got so long that I was just confused that what's what. I had the working code before your code, I copy pasted it and that's the file I uploaded. I appreciate your help, who would've known it's the font colour. Apparently you and fluff are the ones lol. I'll try the code and update in a min. Appreciate your help. Cheers.

I added the Font ColorIndex setting for that field just to make sure.


If all working good news glad we could help

Dave

 

[Windy borough]

@dmt32 @Fluff both of you are spot on. The code works like a charm! Yes changing the font helped a lot too! lol lol. @Fluff with yours the form resets and yes I need yes in both columns depending which one is selected. With @dmt32 code it also gives out yes in both columns as per selected button. Many thanks to you guys both. Saved my weekend, well and truly appreciate your help! Going to grab a pizza or two and have a breather. What a day, what a day. Cheers guys for your help, I appreciate it very much. Cheers.

 

[Windy borough]

@dmt32 lol lol you not taking any chances are you? lol added the font colorindex setting to make sure. Love it, lol. Yes all working as I need it to be working, bundle of thanks to you and @Fluff both. Cheers guys your help is appreciated!

I added the Font ColorIndex setting for that field just to make sure.


If all working good news glad we could help

Dave

 

[Fluff]

Glad we could help & thanks for the feedback