Log In
Register
Home
Active Member
May 5, 2020
Offline
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
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
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
Answers Post
Active Member
May 5, 2020
Offline
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
Offline
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.
Active Member
May 5, 2020
Offline
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
Offline
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)
