Combobox, prevent duplicated entries/selections

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
Hi

I have a long list of numbers in a combobox

Code:
    Dim passk As Range
    
    With Sheets("Resurser")
UserForm4.ComboBox_kortnummer.Clear    'clear listbox
        For Each passk In .Range("l3", .Range("l" & Rows.Count).End(xlUp))
            If passk.Value <> "" Then UserForm4.ComboBox_kortnummer.AddItem passk.Value
        Next passk
    End With
The combobox is populated with above code.

I use the combobox to assign a number to userprofiles... the numbers is added to a table row that represent a user.

But i want to prevent that i select the same value twice.

For example
This is the combobox values:
1
2
3
4
5

I first select value 1 and press my save button.
Then the value is posted into my table on the row for the user i am working on.
All this works now.
But if i open a second user and select the value 1 again... i want a "warning" that this value is already in use on another row in the table.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Can you post the code for the Save button?
 
Upvote 0
Yes here it is.

I got one button that save an EDIT of the data in the table.
It looks like this:
Code:
    Dim RowIndex As Long
    RowIndex = UserForm4.ComboBox_personal.ListIndex + 8
     
    With UserForm4

        Sheets("REGISTER").Cells(RowIndex, 2).Value = UserForm4.TextBox_birthyear.Value
        Sheets("REGISTER").Cells(RowIndex, 2).Value = UserForm4.TextBox_name.Value

  End With

Then i got one button that adds a new item to the tabel

Code:
With Worksheets("REGISTER").Range("a7")                
    .ListObject.ListRows.Add (1)
    .Offset(1, 0).Value = UserForm4.TextBox_birthyear.Value
    .Offset(1, 1).Value = UserForm4.TextBox_name.Value

End With

all this works great.
i open the form... either enter data and add it as "new"
or via a listbox select already existing data and import it to the form and edit and save it.
 
Upvote 0
no solution to this?

tried searching but cant figure out how to alert the user of a userform that a dublicated value have been slected based on previous values in a column.
 
Upvote 0
How is the data organised?

Generally I'd suggest using something like Application.Match to check a range for a value but I can't figure out how your data is organised so I'm not sure what range you would actually need to check.
 
Upvote 0
how do you mean organised?

i have never heard of application.match or know what to answer on how my data is organised.

basically i want to check something like this.

Code:
If UserForm4.ComboBox_kortnummer.Value = "in table XXX in column YYYY" Then
MsgBox (UserForm4.TextBox_firstname.Value & " arbetar mindre än sin anställningsmängd" & vbNewLine & "skriv en förklaring i anteckningsfältet och klicka sedan på spara igen.")
End If

basically if the value that is selected in the combobox is located in the table already, then post a message.
best would be if it prevents from selecting that value and forces the user to select another value.
 
Upvote 0
Do you have the headers in the data?

What/where are they?

Are the users listed somewhere?

Application.Match is just a way to implement the MATCH worksheet functuon in VBA and could be used to find out if a value is in a range.
 
Upvote 0
Do you have the headers in the data?

What/where are they?

Are the users listed somewhere?

Application.Match is just a way to implement the MATCH worksheet functuon in VBA and could be used to find out if a value is in a range.


Yes, table have headers.
Table is located in a sheet called REGISTER
Table starts at A7:CJ7
Users are listed in the table, names, adress etc.
One of the headers in the table is called ID-numbers
This is the numbers that can not be duplicated on several users.
So when i select a user in my userform, then select a ID number in a combolist... i want to make sure that i dont select a number already added to another user.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top