Excel 2016 VBA Option button or radio button VBA code.

Windy borough

New Member
Joined
Nov 3, 2018
Messages
35
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
Hi,
try this update

Rich (BB code):
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

New Member
Joined
Nov 3, 2018
Messages
35
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

Rich (BB code):
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 3, 2018
Messages
35
@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

New Member
Joined
Nov 3, 2018
Messages
35
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,992
Office Version
365
Platform
Windows
First off, change the font in col D to black. That way you can see what's happening ;)
 

Windy borough

New Member
Joined
Nov 3, 2018
Messages
35
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
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

Code:
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
 
Last edited:

Windy borough

New Member
Joined
Nov 3, 2018
Messages
35
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.
 

Forum statistics

Threads
1,081,556
Messages
5,359,549
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top