
Active Member

May 5, 2020

Hi, thank you for taking my question.
I have an Excel userform which has multiple controls including option boxes consisting of up to 8 choices. When a new record is added I find the first empty row in my spreadsheet and use the offset command to place the data in the correct column (eg: ActiveCell.Offset(0, 1) = txtInputDate.Value). For option boxes, I use the Caption from the option box selected and place it in the spreadsheet using a function as shown below:
Public Function FillOptionBoxes(stframe, stOffset)
Dim Ctrl As MSForms.Control
For Each Ctrl In frmNewRecords.Controls(stframe).Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl = True Then
ActiveCell.Offset(0, stOffset) = Ctrl.Caption
End If
Next
End Function
Now I want to be able to read this information back into the form in order to edit records. Is there a similar method I could use?
Thank you


Trusted Members
Moderators

November 1, 2018


Active Member

May 5, 2020

Thank you Velouria for your reply - it works perfectly! I appreciate your help.
I have put this code into a function called ReadOptionBoxes which I call like this:
stoffset = 35
stframe = "FrameGender"
Call ReadOptionBoxes(stframe, stoffset)
However, following on to this, since I have around 10-15 sets of option boxes (eg: Gender, Socio-economic status, marital status, etc), the code to fill the variables and call the function is quite repetitive and seems wasteful. I would like to set up a table with all the offsets and frame names (eg: FrameGender, offset 10; FrameSocioEcon, offset 11) and loop through these to read all the option groups. I just can't get my head around the best way to do this. I'd be grateful for any advice. Many thanks 🙂


Trusted Members
Moderators

November 1, 2018


Active Member

May 5, 2020

Thanks, I will try that. I've become a bit bogged down by a logic problem related to my last query. When I move up/down my records I use the UpdateDisplay Sub to select the row and populate the form. However, the info from the optionboxes doesn't reflect the correct info - it is one "move" behind i.e. it lags behind by one row when I spin up/down. I'm not sure if this makes sense but I'd be grateful if you could put me out of my misery! Many thanks, 🙂
Private Sub UpdateDisplay()
'called when record added/deleted/moved
With VMPTable
RecordPosition.Caption = CurrentRow & " of " & .ListRows.Count
Populate .ListRows(CurrentRow).Range
.ListRows(CurrentRow).Range.Select
End With
End Sub
Private Sub Populate(SelectedRow As Range)
...declare variables, etc
With SelectedRow
txtRefNo.Value = .Cells(1, 1).Value
txtInputDate.Value = .Cells(1, 2).Value
'Frame3 - Case Type (GBV/DV/Other)
stoffset = 34
stframe = "Frame3"
Call ReadOptionBoxes(stframe, stoffset)
End With
End Sub
Public Function ReadOptionBoxes(stframe, stoffset)
Dim Ctrl As MSForms.Control
For Each Ctrl In frmNewRecords.Controls(stframe).Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl.Caption = ActiveCell.Offset(, stoffset).Value Then
Ctrl = True
Exit For
ElseIf TypeName(Ctrl) = "OptionButton" And Ctrl.Caption <> ActiveCell.Offset(, stoffset).Value Then
Ctrl = False
End If
Next
End Function


Trusted Members
Moderators

November 1, 2018

Your ReadOptionBoxes routine uses the active cell, but your UpdateDisplay routine doesn't move the selection until after it's populated everything. If I were you, I'd pass the relevant range (or a row number) to the ReadOptionBoxes routine rather than relying on the active cell.
1 Guest(s)
