Avoid duplicated selection(s) across 15 comboboxes populated by a single range.

**royUK** · Aug 5th 2020

I'll take a look. I don't understand why I am getting an error when I try to load the form.

I'm busy at the moment but will get back to you as soon as possible.

Wozzie · Aug 5th 2020

Quote from royUK

I'll take a look. I don't understand why I am getting an error when I try to load the form.

I'm busy at the moment but will get back to you as soon as possible.

Thanks Roy, apologies I had removed the splashscreen but forgot to remove its show sub, corrected file attached,.

COPY_TEST_FRAMES2.xlsm

Woz.

**royUK** · Aug 5th 2020

I'll take a look later

**royUK** · Aug 5th 2020

Your comboboxes are not in numeric order. as they are in the original workbook. I think being in frames as well is causing the error on loading. Does it load for you.

The form is also way too big unless the user has impaired vision. Ir's very difficult to work with. I have quite a large monitor and it only shows part on the screen, and is very difficult to work with in the VB Editor. Also, those huge letters obscure the controls.

Wozzie · Aug 5th 2020

Quote from royUK

Your comboboxes are not in numeric order. as they are in the original workbook. I think being in frames as well is causing the error on loading. Does it load for you.

The form is also way too big unless the user has impaired vision. Ir's very difficult to work with. I have quite a large monitor and it only shows part on the screen, and is very difficult to work with in the VB Editor. Also, those huge letters obscure the controls.

I don't understand Roy, the file opens ok for me (original and the link above), the link shows how it looks on my 21.5" mac. And I when I built the framed userform I spent a long time ensuring comboboxes where in the correct order and the tags lined up with textbox numbers!

**royUK** · Aug 5th 2020

I created a new UserForm to replace yours. The controls are housed in a MultiPage control and I've adjusted the code to work with that.

I have also added a Class Control to handle the ComboBox change event to save having to enter it for each combobox.

It seems to work OK, but you test it. I'll check back tomorrow.

Please note that I have made the UserForm a more manageable size, and I think using bright colours in a UserForm should really be avoided.

**royUK** · Aug 5th 2020

When I checked it looked to me that the ComboBoxes and other controls weren't in numeric sequence. I have to go out now but I'll check back tomorrow.

Wozzie · Aug 5th 2020

Quote from royUK

I created a new UserForm to replace yours. The controls are housed in a MultiPage control and I've adjusted the code to work with that.

I have also added a Class Control to handle the ComboBox change event to save having to enter it for each combobox.

It seems to work OK, but you test it. I'll check back tomorrow.

Please note that I have made the UserForm a more manageable size, and I think using bright colours in a UserForm should really be avoided.

Display More

Wow, I see what you mean about those big letters! Something weird happened there as those letters don't show in my file as you can see in my screen shot above (posted seconds before I received this post from you).

Thanks for your new code which I will incorporate this evening and let you know how it goes.

Woz.

**royUK** · Aug 5th 2020

Looking at your image it seems to be the Frame Caption's font that is blown up

Wozzie · Aug 6th 2020

Quote from royUK

I created a new UserForm to replace yours. The controls are housed in a MultiPage control and I've adjusted the code to work with that.

I have also added a Class Control to handle the ComboBox change event to save having to enter it for each combobox.

It seems to work OK, but you test it. I'll check back tomorrow.

Please note that I have made the UserForm a more manageable size, and I think using bright colours in a UserForm should really be avoided.

Display More

Many, many thanks Roy, I have your code working on my MultiPage form and it has given me exactly what I wanted to achieve. Can I ask why I need the On Error code in the NoDupe sub? I have to confess I'm not sure what the error handling is doing!

Woz.

**royUK** · Aug 7th 2020

Actually, I was getting an error and couldn't see why. Going back to it now it jumped out at me.

Replace the code with this and it should work

Code

Sub NoDupe()
    With Me
        If ValueSelected(.MultiPage1.Pages(.MultiPage1.Value).ActiveControl.Value, Me) Then
            MsgBox "You have already selected this item!", vbCritical, "Selection Not Valid"
            .MultiPage1.Pages(.MultiPage1.Value).ActiveControl.ListIndex = -1
            Exit Sub
        End If
    End With
    EnterSelection
End Sub

Wozzie · Aug 7th 2020

Yes it works fine. Thank you for all your help and patience, I have learnt so much from you over the last week.

Woz

**royUK** · Aug 8th 2020

Pleased to help

Avoid duplicated selection(s) across 15 comboboxes populated by a single range.

Participate now!

Share