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
Hi,
It looks to me like you just need to alter the logic slightly:
For Each Ctrl In frmNewRecords.Controls(stframe).Controls
If TypeName(Ctrl) = "OptionButton" And Ctrl.Caption = ActiveCell.Offset(0, stOffset).Value Then
Ctrl = True
Exit For
End If
Next
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 🙂
You could just put them into a 2 column table/range, then simply process each row using the first column as the frame name and the second as the offset value.
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
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.
