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.

 

[dmt32]

Hi,
try this update

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 = IIf(Me.OptionButton1.Value, "Yes", "No")


    '.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
    .Cells(dcc + 1, 6).Value = Me.TextBox3.Value
    
    
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    
    End With


End Sub

I have assumed the False answer (not selected) is No but change as required

Dave

 

[Windy borough]

Hello Dave. Thanks for your help, I appreciate it very much. I tried your suggestion and it doesn't work. It's the same, all fields gets populated but the radiobutton is not working. You think there's something wrong with the code? I have no vba knowledge and I've been at this since morning and I have no clue what I'm missing. I appreciate your help and if you have any suggestions then kindly let me know. Cheers.

Hi,
try this update

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 = IIf(Me.OptionButton1.Value, "Yes", "No")


    '.Cells(dcc + 1, 5).Value = Me.TextBox4.Value
    .Cells(dcc + 1, 6).Value = Me.TextBox3.Value


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

    End With


End Sub

I have assumed the False answer (not selected) is No but change as required

Dave

 

[dmt32]

Hi,
Nothing wrong with suggestion, it works OK for me.

If can place copy of your workbook in a dropbox & provide a link to it here, I or another here should be able to figure out what your issue is.

Dave

 

[Windy borough]

@dmt32 Thanks for the quick reply. I will upload it and post the link here. I'm 99.100% sure it's me who's messing it about. Many thanks for your help, much appreciated. Cheers.

 

[Windy borough]

Hello dmt32.

I don't have dropbox but I've uploaded it to google drive.

https://drive.google.com/open?id=1zEDhnpnNHQErXORWGkqe0cpXXG5MscHB

Kindly have a look at it. I had so much rubbish code which was in there due to trial and error and I've tried to uncluttered it as much as possible. I copied the original code to avoid confusion. I did tried to do one particular thing but it just wasn't working like any other thing that I was trying to do. After add entry is clicked all textboxes are cleared but optionbutton whichever option was selected doesn't clear itself or the form doesn't fully reset. If you have a min perhaps you can see sort it too. I appreciate your patience and help. Thanks in advance. Cheers.

Hi,
Nothing wrong with suggestion, it works OK for me.

If can place copy of your workbook in a dropbox & provide a link to it here, I or another here should be able to figure out what your issue is.

Dave

 

[Fluff]

First off, change the font in col D to black. That way you can see what's happening

 

[Windy borough]

Seriously??!! It's been doing my head in since morning!! @Fluff I think now you can better understand the level of expertise you are dealing with!! lol lol. Funny thing is, now I have no idea which code it was which was working!! So... it's square one right now lol. I got called into the office and found out I need this working tomorrow morning. It's friday and I'm hammering myself in this and it was working all along until, I deleted the codes and made it refresh because it was the font.... can you imagine my misery? lol. Sigh... what sorcery is this, how did you even had the idea to check the font colour? lol. Now if I can only remember which code it was which was working lol. Good one @Fluff

First off, change the font in col D to black. That way you can see what's happening

 

[dmt32]

Hi,
first, you did not use the code I published, second you need to set your font to black in Column D, E


try this update

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 + 1


    .Cells(dcc, 1).Value = Date
    .Cells(dcc, 2).Value = Me.TextBox1.Value
    .Cells(dcc, 3).Value = Me.TextBox2.Value


    With .Cells(dcc, IIf(Me.OptionButton1.Value, 4, 5))
        .Value = "Yes"
        .Font.ColorIndex = xlAutomatic
    End With
    
    '.Cells(dcc, 5).Value = Me.TextBox4.Value
    .Cells(dcc, 6).Value = Me.TextBox3.Value




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


    End With




End Sub

I have assumed that you want Yes in either Column D or E depending on Option button selection?

Dave

 

[Windy borough]

Can you help me with how to clear the selected option once add entry is pressed? It stays at In or Out once data is entered. It should reset to blank form really. If not a lot of trouble then also where in form the code will go?! Please, I appreciate your help. Cheers.