I have a problem with a form that contains a list box and some option boxes.
In my code, I am trying to compare two values:
Column C in my worksheet
Column(0) of my listbox
When the form opens, I select one or multiple rows in my listbox. Then, I select a code to apply. After doing so, I click the process button and for each of the rows selected in my listbox, I need to update the respective rows in my worksheet (column I) with the code that was selected in my form.
In the code I have, it works for one, but not for any more and I suspect it is an issue with the way I have written it.
Private Sub cmdProcess_Click()
Dim wkb As String, i As Integer, response As Integer, msg As String, title As String, style As String, _
intcounter As Integer, oCtrl As Control, k As String
'check for list box selection
If Me.lstTrans = vbNullString Then
'if nothing was selected, tell user and let them try again ->>
For Each oCtrl In frm_ledger_coding.fra1.Controls
'only option buttons
If TypeName(oCtrl) = "OptionButton" Then
'which one is checked?
If oCtrl.Value = True Then
'what's the tag?
k = oCtrl.Tag
'specifying cunter to start from row 2
intcounter = 2
If k = "" Then Exit Sub Else
'specifying list selection counts
For i = 0 To lstTrans.ListCount - 1
If lstTrans.Selected(i) Then
'Range H being the amount which always have a value
While .Range("H" & intcounter).Value <> ""
'compare the key in column c with the list value in column 0 (bound column)
If .Range("C" & intcounter).Value = lstTrans.List(i) Then
Range("I" & intcounter).Value = k
intcounter = intcounter + 1
I have enclosed a working file with test data to help.
Can someone help me get this working?