Using MAX function with some blank values in userform

[CaptainK]

Hello,

I am trying to use the max function that returns the maximum value stored in 12 different comboboxes and stores it in a variable r. The boxes are named cb1, cb2, cb3, etc. The line of code I have is

r = WorksheetFunctions.Max(me.cb1.value, me.cb2.value, [...etc...], me.cb12.value)

but this is giving me run time error 1004, unable to get the max property of the WorksheetFunction class. I believe this is because the default value of the comboboxes are blank. I'd like to keep the default values to be blank, so is there a way to deal with this?

[6StringJazzer]

There might be a better way but this is the first one I thought of that I know works:

Please Login or Register  to view this content.

Note that "Me." is optional, because any reference is automatically assumed to be to a component of "Me.". Also note that Value is the default attribute of ComboBox so that is also optional.

[CaptainK]

Jeff,

That solution did not work. I am still receiving the same run time error. Do you have any alternate suggestions?

[6StringJazzer]

It worked in my file. If you attach yours I might be able to help further.

[CaptainK]

I have attached a spreadsheet with the userform to this post. You'll need to go into the VBA editor to see it. The spreadsheet it's a part of has some confidential data so I had to separate the form but I don't think it will make a difference. The code in question is in the topmost combobox labelled "# of releases" beneath the lengthy case setup. While the userform initializes fine, it crashes when I try to change the value in that combobox. Book4.xlsm

[6StringJazzer]

Your comboboxes return a value of " " (2 spaces) when there has been no selection. In my test they returned "" (null string).

When I modified your code to use this line of code it worked.

Please Login or Register  to view this content.

I would also suggest splitting the line up for readability

Please Login or Register  to view this content.

[6StringJazzer]

Thanks for the rep! If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."