+ Reply to Thread
Results 1 to 3 of 3

ensure user selects radio button before adding information

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    430

    ensure user selects radio button before adding information

    Good Afternoon folk

    I have a userform for inputing information to a spreadsheet using the code below. It works fine however if the user does not select a radio button to select a score its saving the rest without it. How can I ensure the user selects a score,,,, ob10, ob8, ob6, ob4, ob2, obna

    Private Sub cmdAdd_Click()
    
    Dim iRow As Long
    Dim ws As Worksheet
    Dim a As Integer
    Set ws = Worksheets("Scorepoints")
    
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
      
    a = MsgBox("Please check all information is correct. Select 'Yes' to save, Select 'No' to change", vbYesNo)
    If a = vbYes Then
      Unload Me
    Else
      Exit Sub
    End If
    
    ws.Cells(iRow, 1).Value = Me.txtpoint.Value
    ws.Cells(iRow, 2).Value = Me.txtname.Value
    ws.Cells(iRow, 3).Value = Me.txtcomments.Value
    ws.Cells(iRow, 4).Value = Me.ob10.Value
    ws.Cells(iRow, 5).Value = Me.ob8.Value
    ws.Cells(iRow, 6).Value = Me.ob6.Value
    ws.Cells(iRow, 7).Value = Me.ob4.Value
    ws.Cells(iRow, 8).Value = Me.ob2.Value
    ws.Cells(iRow, 9).Value = Me.obna.Value
    ws.Cells(iRow, 10).Value = Me.cbhr.Value
    ws.Cells(iRow, 11).Value = Me.obone.Value
    ws.Cells(iRow, 12).Value = Me.obtwo.Value
    ws.Cells(iRow, 13).Value = Me.obthree.Value
    
      Unload Me
      
      scoreselect.Show
      
    End Sub
    Many thanks for your help
    Last edited by JamesT1; 05-27-2014 at 10:02 AM.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: ensure user selects radio button before adding information

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim a As Integer
    Dim Ctrl As Control
    Dim OneSelected As Boolean
    
    OneSelected = False
    
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "OptionButton" Then
            If Ctrl Then
                OneSelected = True
                Exit For
            End If
        End If
    Next
    
    If Not OneSelected Then
        MsgBox "At least one score must be selected."
        'Setfocus back to any control you wish.
        Me.cmdAdd.SetFocus
        Exit Sub
    End If
    
    'Rest of code here.
    If you have more than one group of optionbuttons, then you could test for control names.
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "OptionButton" Then
            Select Case Ctrl.Name
            Case "ob10", "ob8", "ob6", "ob4", "ob2", "obna"
                OneSelected = True
            End Select
        End If
    Next
    You can always set a optionbutton as a default when the userform starts.
    Private Sub UserForm_Initialize()
    Me.OptionButton1.Value = True
    End Sub
    David
    (*) Reputation points appreciated.

  3. #3
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    365
    Posts
    430

    Re: ensure user selects radio button before adding information

    Tinbendr

    this is right on the button and exactly what I require, many thanks for your help, much appreciated

    for anyone who needs something similar,,, this is the adjusted code

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim a As Integer
    Dim Ctrl As Control
    Dim OneSelected As Boolean
    Set ws = Worksheets("Scorepoints")
    
    OneSelected = False
    
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "OptionButton" Then
            If Ctrl Then
                OneSelected = True
                Exit For
            End If
        End If
    Next
    
    If Not OneSelected Then
        MsgBox "At least one score must be selected."
        'Setfocus back to any control you wish.
        Me.cmdadd.SetFocus
        Exit Sub
    End If
    
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
      
    a = MsgBox("Please check all information is correct. Select 'Yes' to save, Select 'No' to change", vbYesNo)
    If a = vbYes Then
      Unload Me
    Else
      Exit Sub
    End If
    
    ws.Cells(iRow, 1).Value = Me.txtpoint.Value
    ws.Cells(iRow, 2).Value = Me.txtname.Value
    ws.Cells(iRow, 3).Value = Me.txtcomments.Value
    ws.Cells(iRow, 4).Value = Me.ob10.Value
    ws.Cells(iRow, 5).Value = Me.ob8.Value
    ws.Cells(iRow, 6).Value = Me.ob6.Value
    ws.Cells(iRow, 7).Value = Me.ob4.Value
    ws.Cells(iRow, 8).Value = Me.ob2.Value
    ws.Cells(iRow, 9).Value = Me.obna.Value
    ws.Cells(iRow, 10).Value = Me.cbhr.Value
    ws.Cells(iRow, 11).Value = Me.obone.Value
    ws.Cells(iRow, 12).Value = Me.obtwo.Value
    ws.Cells(iRow, 13).Value = Me.obthree.Value
    
      Unload Me
      
      scoreselect.Show
      
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Is there a way to add a button that selects the next checkbox in a row?
    By R_Bish in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-04-2014, 05:42 PM
  2. Replies: 3
    Last Post: 08-28-2013, 05:39 AM
  3. Prompting User to Select Radio Button
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2011, 06:48 PM
  4. to ensure all radio button groups are used before moving on.
    By lrm75uk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2010, 08:20 AM
  5. Pressing one radio button to select another radio button
    By raehippychick in forum Excel General
    Replies: 1
    Last Post: 09-13-2007, 11:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1