For Loop Combo Boxes with If Statement

Hi, I am a beginner to VBA looking for some help with a For Loop

I have a Userform with 18 Comboboxes named mid1, mid2, mid3 etc which would hold an item description and then 18 textboxes named mia1, mia2, mia3 etc which would hold corresponding currency amount (There are other textboxes on the userform as well)

On Userform Activate, I am trying to disable a mia textbox if the corresponding mid combobox is empty.

eg if mid3 is empty then mia3 needs to be Disabled.

This is what I have got so far:

Private Sub UserForm_Activate() 

Dim coll As New Collection
    Dim item As TextBox
    Dim i As Integer

    coll.Add Me.mia1
    coll.Add Me.mia2
    coll.Add Me.mia3
    coll.Add Me.mia4
    coll.Add Me.mia5
    coll.Add Me.mia6
    coll.Add Me.mia7
    coll.Add Me.mia8
    coll.Add Me.mia9
    coll.Add Me.mia10
    coll.Add Me.mia11
    coll.Add Me.mia12
    coll.Add Me.mia13
    coll.Add Me.mia14
    coll.Add Me.mia15
    coll.Add Me.mia16
    coll.Add Me.mia17
    coll.Add Me.mia18
    
Set i = 1
With i
    For Each item In coll
        If Me.Controls("mid" & i).text = ""
    Then
        item.Enabled = False
        item.BackColor = vbGrey
        i = i + 1
        Next item
        
    End With
End Sub

I keep getting a compile error when i is set to 1, where am I going wrong.

Also is a for loop the best way to achieve this?

Many thanks for all help.

I mean so instead having the code for each box change

in theory something like if Userform_Active? Or Any"mid" combobox_Change

I am planning on having 50 boxes and trying to avoid having 50 Combobox_Change


sorry if not making much sense I am new to this.

Thanks so much both of you

How would I use this code so that it would always be running when I am in the userform.

For example if I entered text in mid1 which would enable mia1, then before exiting the userform deleted the text in mid1, mia1 would still be enabled.

Currently i Have the following for each mid box

Private Sub mid8_Change()
If mid8.Text <> "" Then
        
    
        mia8.Enabled = True
        mia8.BackColor = vbWhite
        Else
        
        mia8.Text = ""
        mia8.Enabled = False
        mia8.BackColor = vb3DLight
        End If
        
End Sub

Are all the comboboxes identical?

Thanks very much, perfect

Expanding on the Sub CheckCombo, would there be a formula to add all the "mia" boxes" values and all the "moa" boxes, currently I have:
(tmi is another textbox in the userform for sum of all the mia combobox values, tmo is for all moa values)

    

tmi.Value = Val(mia1.Value) + Val(mia2.Value) + Val(mia3.Value) + Val(mia4.Value) + Val(mia5.Value) + Val(mia6.Value) + Val(mia7.Value) + Val(mia8.Value) + Val(mia9.Value) + Val(mia10.Value) + Val(mia11.Value) + Val(mia12.Value) + Val(mia13.Value) + Val(mia14.Value) + Val(mia15.Value) + Val(mia16.Value) + Val(mia17.Value) + Val(mia18.Value)

tmi.Value = Format$(tmi.Value, "0.00")

tmo.Value = Val(moa1.Value) + Val(moa2.Value) + Val(moa3.Value) + Val(moa4.Value) + Val(moa5.Value) + Val(moa6.Value) + Val(moa7.Value) + Val(moa8.Value) + Val(moa9.Value) + Val(moa10.Value) + Val(moa11.Value) + Val(moa12.Value) + Val(moa13.Value) + Val(moa14.Value) + Val(moa15.Value) + Val(moa16.Value) + Val(moa17.Value) + Val(moa18.Value)

tmo.Value = Format$(tmo.Value, "0.00")

I am thinking there is probably something much shorter

hmm, I'm not sure if what you're trying is possible without utilising the individual change events.

Do you need 50 comboboxes?

The change events could be shortened to 1 line, and then do the checking in a separate procedure..

Private Sub ComboBox1_Change()
    Call CheckCombo(Right(Me.ActiveControl.Name, 1))
End Sub

Private Sub ComboBox2_Change()
    Call CheckCombo(Right(Me.ActiveControl.Name, 1))
End Sub

Sub CheckCombo(cboxn As Integer)
    If Len(Me.Controls("Combobox" & cboxn)) = 0 Then
        Me.Controls("Textbox" & cboxn).Enabled = False
    Else
        Me.Controls("Textbox" & cboxn).Enabled = True
    End If
End Sub

Yes they are

Edit - That solution looks a lot better, Thanks. May I ask what the "Right" and "Screen" part indicate (why is screen used on the second combobox instead of Me.Active.Control like the 1st)

"Screen" should be me. updated now.
Right is taking the last character from the name of the current combobox.. mid1 = 1, mid2 = 2, 3, etc.. just realised I've forgotten to account for two digit numbers.

Edit: Updated version - Passes the entire name of the combobox, and uses Replace() to change the mid to mia, so the reference works for the textbox.

Private Sub mid1_Change()
    Call CheckCombo(Me.ActiveControl.Name)
End Sub

Private Sub mid20_Change()
    Call CheckCombo(Me.ActiveControl.Name)
End Sub

Sub CheckCombo(cbox As String)
    If Len(Me.Controls(cbox)) = 0 Then
        Me.Controls(Replace(cbox, "mid", "mia")).Enabled = False
    Else
        Me.Controls(Replace(cbox, "mid", "mia")).Enabled = True
    End If
End Sub

You can handle the events of all the comboboxes via a class.

Mind you having 50 identical checkboxes with corresponding textboxes seems quite a lot.

Have you considered another approach?

For example one combobox, one textbox, a listbox and a command button.

The user makes their selection in the combobox, enters the value in the textbox and then when they press the command button the selection and value are transferred to the listbox.

Additional buttons could be added to delete/edit previous entries.

You need to change the row.

.Column(0, .ListCount-1) = ComboBox1.Value
.Column(1, .ListCount-1) = TextBox1.Value

Thanks.

Ive added buttons to delete a row if its selected in the listbox and a button to clear all rows in the listbox.

What would be the best way to add a button so I can edit the selected row in the list box.

Perfect Thanks very much

My ListBox is called MoniesInList

I am trying to get a total of all items in the 2nd column and show in a Textbox, my code is:

Private Sub EditMoniesOut_Click()
Dim MySum As Double
Dim i As Long
MySum = 0
With MoniesInList
    For i = 0 To .ListCount - 1
      MySum = MySum + Val(MoniesInList.List(i, 2))
    Next
End With
TextBox1.Value = MySum
End Sub

I keep getting an error Invalid Use of Null, when it gets to the MySum = MySum + Val(MoniesInList.List(i, 2)) section, I am unsure why.

Hi I have most of this working.

I have a button in my userform when I click it creates a table at a selected bookmark in my word document and then copies my listbox(MoniesIn) column 1 to column 1 of the created table.

My code keeps getting stuck on the Horizontal Border Width formatting, I keep getting 'Run Time Error 5843 One of the values passed is out of Range'.

I believe I need to set my table as a variable (?), but unsure how. Also I need it to always select the table in this document.

This is my code, please help:

Private Sub CreateCompletionStatement_Click()

Dim x As Integer
Dim y As Integer

x = MoniesInList.ListCount

'GO TO BOOKMARK

Selection.GoTo What:=wdGoToBookmark, Name:="MoniesIn"
Selection.MoveDown Unit:=wdLine, Count:=1

'ADD TABLE

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=x, NumColumns:=2

With Selection.Tables(1)
.Rows.AllowBreakAcrossPages = False
.AllowAutoFit = False
.Rows.HeightRule = wdRowHeightExactly
.Rows.Height = CentimetersToPoints(0.7)
End With

With Selection.Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorGray20
End With

With Selection.Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorGray20
End With

With Selection.Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorGray20
End With

 With MoniesInList
 
 For x = 1 To MoniesInList.ListCount
 For y = 0 To MoniesInList.ListCount
     ThisDocument.Tables(1).Cell(Row:=x, Column:=1).Range.Text = Val(MoniesInList.List(y, 0))
    Next
    Next

End With

End Sub

What you need to do is first check the user has selected a row in the listbox, you can do that like this.

Dim idx As Long

    idx = ListBox1.ListIndex

    If idx=-1 Then
        MsgBox "No data selected."
        Exit Sub
    End If

We then need to load the data from the listbox to the textbox, so we continue the code like this.

TextBox1.Value = ListBox1.List(idx, 0)
TextBox2.Value = ListBox2.List(idx, 1)

To write the amended data back to the listbox we would reverse the above code.

Are there any empty rows in the column you are trying to sum?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

There's only 2 columns in the listbox those columns are zero-indexed.

What that means is that to refer to column 1 you use 0, to refer to column 2 you use 1, to refer to column 3 you use 2 and so on.

In your code you are referring to the, non-existent, third column.

 MySum = MySum + Val(MoniesInList.List(i, 1))

Got it Thanks

I have an add button which adds TextBox1 text to listbox Coulmn 1, and Textbox2 to Column 2. Then clears the text in both boxes and sets focus to textbox1 ready for a new entry.

The code runs fine:

Private Sub AddMoniesIn_Click()
MoniesInAmount.Value = Format$(MoniesInAmount.Value, "0.00")
With MoniesInList
   
.AddItem
.Column(0, .ListCount - 1) = MoniesInDescription.Value
.Column(1, .ListCount - 1) = MoniesInAmount.Value

End With
MoniesInDescription.Text = ""
MoniesInAmount.Text = ""
MoniesInDescription.SetFocus
End Sub

I am looking to setup my form so that is the user presses enter when in textbox2 the code will also run. I have the below code, yet it doesn't work no error code shows it just doesn't do anything. Is there anything missing.

Private Sub MoniesInAmount_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If (KeyAscii = 13) Then 
           Call AddMoniesIn_Click
   End If
End Sub

Also when I have searched for running vba on enter press some sources say use the Keypress Event and some KeyDown event is there any difference or need to use one over the other?