Load data back into userform - radio buttons and checkboxes

S

Storm8585

New Member
Joined
Sep 5, 2014
Messages
47
Hi all
Help needed again! I have a userform which includes grouped radio buttons and checkboxes. The form needs to load and save data between sessions. Short of coding each radio box result to an individual cell somewhere, I was hoping to just store the caption name of the selected radio button to a single cell. Then when the form is loaded up again, the code will find the group name, then check the cell for the value which corresponds to the radio button captions and set it to true. I can do the download part - but not the upload bit! Also is there something similar for checkboxes??
Any help greatly appreciated.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
One method
- name of the option button written to cell A1 in Sheet1 when the form is unloaded
- that value is used when the userform is initialized

VBA Code: 
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    'write name of selected option option button to cell A1 in Sheet1
    Dim c As Control, cel As Range
    Set cel = Sheets("Sheet1").Range("A1") 
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Colours" And c.Value Then cel = c.Name
        End If
    Next c
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    On Error Resume Next
    Me.Controls(Sheets("Sheet1").Range("A1").Value) = True
    On Error GoTo 0
End Sub
 
Upvote 0
- name of the selected option button written to cell A1 in Sheet1 when the form is unloaded
- name of the selected checkbox written to cell A2 in Sheet1 when the form is unloaded
- values used when the userform is initialized

VBA Code: 
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim c As Control, cel As Range
    Set cel = Sheets("Sheet1").Range("A1")
   
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Colours" And c.Value Then cel = c.Name
        End If
        If TypeName(c) = "CheckBox" Then
            If c.GroupName = "Animals" And c.Value Then cel.Offset(1) = c.Name
        End If
    Next c
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim cel As Range
    Set cel = Sheets("Sheet1").Range("A1")

    On Error Resume Next
    Me.Controls(cel.Value) = True
    Me.Controls(cel.Offset(1).Value) = True
    On Error GoTo 0
End Sub
 
Upvote 0
Hi Yongle, thanks for your time helping out Storm8585 and the rest of the community - I have also found this reply very useful.

I wonder if you could help me a bit further? On my userform, I have several groups of radio buttons (Q1, Q2, Q3.......Q12) with 5 radio buttons in each. I amended the cell references (I need the outputs for the 12 questions to go into cells CJ3:CU3) and inserted your code to see if it worked for me (which it does). My question is, do I need to repeat the code for each group of buttons, or is there a shorter way of doing this?

hopefully my question is clear?

thanks in advance

Rich
 
Upvote 0
Hi,
you can store the control values in the registry of your desktop & retrieve them when loading the form

Try following

Place Code in a STANDARD module

VBA Code: 
Enum XLFormSettings
    xlSaveSettings
    xlGetSettings
    xlDeleteSettings
End Enum

Sub FormSettings(ByVal Form As Object, ByVal Settings As XLFormSettings)
    Dim Ctl As Control
    Dim CtlType As String
    Dim UsersName As String, Section As String
    Dim Default As Variant

    UsersName = Environ("USERNAME")
    Section = "Settings\" & UsersName & "\" & Form.Name

    On Error Resume Next
    For Each Ctl In Form.Controls
        CtrlType = TypeName(Ctl)
            Select Case CtrlType
        Case Is = "OptionButton", "CheckBox", "TextBox", "ComboBox"
            Default = IIf(CtrlType = "TextBox" Or CtrlType = "ComboBox", "", False)
            If Settings = xlGetSettings Then
                Ctl.Value = GetSetting(AppName:="UserForm Settings", Section:=Section, Key:=Ctl.Name, Default:=Default)
            ElseIf Settings = xlSaveSettings Then
                SaveSetting AppName:="UserForm Settings", Section:=Section, Key:=Ctl.Name, setting:=CStr(Ctl.Value)
            Else
                DeleteSetting AppName:="UserForm Settings", Section:=Section
            End If
        End Select
    Next Ctl
    On Error GoTo 0
End Sub

Following codes place in the useform events shown

VBA Code: 
Private Sub UserForm_Initialize()
    FormSettings Me, xlGetSettings
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    FormSettings Me, xlSaveSettings
End Sub

Code can be applied to any userform in your project

Hope Helpful

Dave
 
Last edited:
Upvote 0
Hi Dave - sorry about the delay in replying (I haven't been working the last few days). Thanks for taking the time to reply, I'll give it a go just now.

many thanks

Rich
 
Upvote 0
Hi Dave / all

I haven't been able to get the code above working, but I have sort of managed to achieve my goal by repeating your original code for each question (even though its a bit of a long winded way to do it!). The code I have inserted looks like this:

Code: 
'Write question data to correct cells in Database
    Dim c As Control, cel As Range
    Set cel = Sheets("Database").Range("CJ3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q1" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CK3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q2" And c.Value Then cel = c.Caption
        End If
    Next c
      

    Set cel = Sheets("Database").Range("CL3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q3" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CM3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q4" And c.Value Then cel = c.Caption
        End If
    Next c


    Set cel = Sheets("Database").Range("CN3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q5" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CO3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q6" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CP3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q7" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CQ3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q8" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CR3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q9" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CS3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q10" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CT3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q11" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CU3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q12" And c.Value Then cel = c.Caption
        End If
    Next c

However, it's still not working exactly as I would like, and wondered if anybody can help? My sheet 'database' contains the demographic information for each respondent, identifiable by a field 'RNumber' which is entered into a textbox ('RNumber' on page1 of UserForm1). When I submit the form data, the RNumber field updates into the next available blank cell in column A on the Sheet!database, and all the other demographic data into the adjacent cells on the same row. I am then able to reload the information into the UserForm by searching for the relevant RNumber and double clicking the textbox in UseForm1. This is all working fine.

page6 of UserForm1 contains a short questionnaire (12 questions) which I want to conduct every month or so, to each respondent. The problem is that currently when I load the UserForm data for a given respondent, then submit the questionnaire - the data always goes into Row 3. What I would like is for Excel to look what number is loaded into textbox 'RNumber' on page1 of UserForm1, find the row that this number occurs in columnA of !Database and paste the questionnaire data into columns CJ:CU of that row.

I'm not sure if this is even clear, but would massively appreciate any help to amend this code?

Thanks

Rich
 
Upvote 0
I can only comment on code I provided which should, do what you want.

If still cannot get it to work, you post all code in your userform & will take a further look.

Dave
 
Upvote 0
Sorry Dave - my mistake, I had thought you were the original poster. As I'm sure you have realised I am not great with code - I can understand it to to the point that I can copy and amend here and there but I'm certainly not able to write from scratch. None of the code in this project is my work.

I think that the solution may lie in this bit of code which exists in Module1:

Code: 
Option Base 1

Function ControlNames() As Variant
ControlNames = Array("RNumber", "Initials", "Date", "Resp", "ERT", "Gender", "Height", "PIO", _
                   "VA1", "VA2", "VA3", "LongTermComments1", _
                    "VA4", "VA5, "VA6", "LongTermComments2", _
                    "VA7", "VA8", "VA9", "LongTermComments3", _
                    "VA10", "VA11", "VA12", "LongTermComments4", _
                    "OSD1Y", "OSDI3Y", "OSDI5Y", "OSDI8Y", "OSDI10Y")
                    
End Function


Code: 
Function IsComplete(ByVal Form As Object) As Boolean
    Dim i As Integer
    For i = 1 To UBound(ControlNames)
        IsComplete = CBool(Len(Form.Controls(ControlNames(i)).Text) > 0)
        If Not IsComplete Then
            MsgBox "Please Complete All Fields", 16, "Entry Required"
            Form.Controls(ControlNames(i)).SetFocus
            Exit Function
        End If
    Next i
End Function

The problem is that these fields are all text boxes and I don't know if it's possible to update a single cell from with the output of a group of option buttons using this method. I also appreciate that you already said that you can't comment on somebody else's code, so I completely understand if you can't offer and further advice. I can't remember who the original author of this code was - somebody on this forum though - so if anybody else if able to offer input I would really appreciate it.

many thanks

Rich
 
Upvote 0
Further info....

This is the code I have on the 'Update Record' command button:

Code: 
Private Sub UpdateRecord_Click()
    Dim i As Integer
    Dim IsNewRespondent As Boolean
    Dim msg As String
    
    IsNewRespondent = CBool(Me.UpdateRecord.Caption = "Add Record")
    
    msg = "Record Updated"
    
    If IsNewRespondent Then
    'New record - check all fields entered
    'If Not IsComplete(Form:=Me) Then Exit Sub
        r = StartRow
        msg = "New Respondent Added"
        ws.Range("A3").EntireRow.Insert
        ResetButtons IsNewRespondent
    End If
    
    On Error GoTo myerror
    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
        With Me.Controls(ControlNames(i))
            'check if date value
            If InStr(.Text, "/") > 0 And IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            Else
                ws.Cells(r, i).Value = .Text
            End If
        End With
    Next i
    'tell user what happened
    MsgBox msg, 48, msg
    
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"

    Application.ScreenUpdating = False
    Set data = ThisWorkbook.Worksheets("Database")
    data.Activate
    data.Range("A1:CM5000").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
   
    Worksheets("Front").Activate
   
    'Write question data to correct cells in Database
    Dim c As Control, cel As Range
    Set cel = Sheets("Database").Range("CJ3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q1" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CK3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q2" And c.Value Then cel = c.Caption
        End If
    Next c
      

    Set cel = Sheets("Database").Range("CL3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q3" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CM3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q4" And c.Value Then cel = c.Caption
        End If
    Next c


    Set cel = Sheets("Database").Range("CN3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q5" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CO3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q6" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CP3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q7" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CQ3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q8" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CR3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q9" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CS3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q10" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CT3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q11" And c.Value Then cel = c.Caption
        End If
    Next c
    

    Set cel = Sheets("Database").Range("CU3")
    For Each c In Me.Controls
        If TypeName(c) = "OptionButton" Then
            If c.GroupName = "Q12" And c.Value Then cel = c.Caption
        End If
    Next c
 
 
    
    Unload Me
    Application.ScreenUpdating = True
      
    
End Sub

obviously the repeated bit at the end is what I have inserted - and the part I referred to a few posts ago.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Multiple checkbox inside a cell
Replies
3
Views
411
6StringJazzer
6StringJazzer
R
  • Question
Check that the options in User Form selected
2
Replies
13
Views
1K
dmt32
D
Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
214
bobsan42
bobsan42
J
  • Question
Data Transfer from UserForm using Radio Buttons in Frames
Replies
2
Views
353
JaesunB
J
S
  • Solved
Using User form to load workbook, extract and combine desired data into new workbook or worksheet
2
Replies
10
Views
355
mumps
mumps

Forum statistics

Threads
1,214,788
Messages
6,121,603
Members
449,038
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top