How to call a userform is a for each loop?

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Hello All!

Thanks for taking the time to read through this. I am still a novice at VBA but have learned quite a bit through trial and error, macro recorder, and YouTube. I am trying to finish a project I started MONTHS ago now that I have a better understanding of what I am doing but have hit an issue I cannot seem to resolve. Hopefully someone can help.
I have a userform with three combo boxes and a button. The first of the three populates at form initialization, the second populates depending on data from the 1st, and the 3rd depending on information form the 2nd. When the user makes a selection form the 3rd combobox, I need to search through a table in sheet 3 in the workbook to find every instance that a cell in a specific column ([options]) that is not blank. For each time it finds a cell that isn't, I am trying to have another userform open to gather information from another dropdown to fill the cell in the column to the left of the one that called the 2nd userform. Below is the code I was super excited I worked out, at least it worked with a message box. When i replace the msgbox with userform.show I get a runtime error (424 - object required). I have no idea what that mean nor how to fix it - can someone please help??

VBA Code:
Private Sub menuname_Change()

For Each X In Sheet3.Range("Table1[OPTION]").SpecialCells(xlCellTypeVisible)
    If X.Value <> "" Then
        UFOPTION.Show
    End If
Next X

For Each X In Sheet3.Range("Table1[QUANTITY]").SpecialCells(xlCellTypeVisible)
    If X.Value = "QUANTITY" Then
        MsgBox X.Address
    End If
Next X

End Sub

Thanks in advance for any suggestions or assistance.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I have been able to determine, I think, that the issue is with the 2nd userform that is called in the loop. I am trying to use a variable from the previous form to fill the label caption - is this possible?
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I found a work around by hiding a label that holds the data needed on the 1st userform. It would be nice to know how to do this in future, but the resolution I used is working for me.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top