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:

Please Login or Register  to view this content.

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.

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

Please Login or Register  to view this content.

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.

Are all the comboboxes identical?

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..

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

I am thinking there is probably something much shorter

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.

Please Login or Register  to view this content.

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.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

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.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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?

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:

Please Login or Register  to view this content.