Prevent duplicate entries in multiple columns using VBA

Firstly, I have very little programming experience but here is what I am trying to do...

I'd like to use vba to keep a user from entering a duplicate entry based off of two columns.

Ex: Column B is vendors & Column E is part number, there should be no duplicate vendor/part number combinations. Sometimes I get a type mismatch error and other times after making adjusments to the code as soon as I put a value in Column B I get the MsgBox without a value in Column E.

I've looked extensively and thought I found something that worked but after working on other code it didn't seem to work right. Here is all of my code:

' Runs events based on target changes

Private Sub Worksheet_Change(ByVal Target As Range)



    Confirmation Target

    'ChangeDirectCost Target



End Sub



' Keeps user from entering the same vendor / part # twice

Private Sub Confirmation(ByVal Target As Range)

    With Target

        If (.Column <> 2 And .Column <> 5) Or .Cells.Count = 1 Then Exit Sub



        If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then

            Application.DisplayAlerts = False

            .ClearContents

            Application.DisplayAlerts = True

            MsgBox "Vendor / Part # combination already exists!"

        End If



    End With

End Sub

Since I'm new at programming I'm trying to keep it simple, initally the code was under a Module but didn't seem to work at all until I put it under Sheet1.

Please help!

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

The only issue I could find is that you probably wanted to use '... Or .Cells.Count > 1'. Nice work.

– VBasic2008
Jan 18 at 22:16

Since you want to look for the combination, you should change your countif to a countifs, including both columns in the criteria. I would also turn application.enableevents to false before the ClearContents then back to true after

– wallyeye
Jan 19 at 1:39

Those changes seemed to fix it. Thank you!!!

– user10935563
Jan 21 at 15:06

Actually, sorry...I'm now getting my MsgBox after any addition to either column 2 or 5. It's not even waiting for me to input values into the other column. I made sure that none of my rows have blank values in case that was causing the issue. Any other suggestions...?

– user10935563
Jan 21 at 15:16

add a comment |

Firstly, I have very little programming experience but here is what I am trying to do...

I'd like to use vba to keep a user from entering a duplicate entry based off of two columns.

I've looked extensively and thought I found something that worked but after working on other code it didn't seem to work right. Here is all of my code:

' Runs events based on target changes

Private Sub Worksheet_Change(ByVal Target As Range)



    Confirmation Target

    'ChangeDirectCost Target



End Sub



' Keeps user from entering the same vendor / part # twice

Private Sub Confirmation(ByVal Target As Range)

    With Target

        If (.Column <> 2 And .Column <> 5) Or .Cells.Count = 1 Then Exit Sub



        If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then

            Application.DisplayAlerts = False

            .ClearContents

            Application.DisplayAlerts = True

            MsgBox "Vendor / Part # combination already exists!"

        End If



    End With

End Sub

Since I'm new at programming I'm trying to keep it simple, initally the code was under a Module but didn't seem to work at all until I put it under Sheet1.

Please help!

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

The only issue I could find is that you probably wanted to use '... Or .Cells.Count > 1'. Nice work.

– VBasic2008
Jan 18 at 22:16

Since you want to look for the combination, you should change your countif to a countifs, including both columns in the criteria. I would also turn application.enableevents to false before the ClearContents then back to true after

– wallyeye
Jan 19 at 1:39

Those changes seemed to fix it. Thank you!!!

– user10935563
Jan 21 at 15:06

Actually, sorry...I'm now getting my MsgBox after any addition to either column 2 or 5. It's not even waiting for me to input values into the other column. I made sure that none of my rows have blank values in case that was causing the issue. Any other suggestions...?

– user10935563
Jan 21 at 15:16

add a comment |

Firstly, I have very little programming experience but here is what I am trying to do...

I'd like to use vba to keep a user from entering a duplicate entry based off of two columns.

I've looked extensively and thought I found something that worked but after working on other code it didn't seem to work right. Here is all of my code:

' Runs events based on target changes

Private Sub Worksheet_Change(ByVal Target As Range)



    Confirmation Target

    'ChangeDirectCost Target



End Sub



' Keeps user from entering the same vendor / part # twice

Private Sub Confirmation(ByVal Target As Range)

    With Target

        If (.Column <> 2 And .Column <> 5) Or .Cells.Count = 1 Then Exit Sub



        If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then

            Application.DisplayAlerts = False

            .ClearContents

            Application.DisplayAlerts = True

            MsgBox "Vendor / Part # combination already exists!"

        End If



    End With

End Sub

Since I'm new at programming I'm trying to keep it simple, initally the code was under a Module but didn't seem to work at all until I put it under Sheet1.

Please help!

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

Firstly, I have very little programming experience but here is what I am trying to do...

I'd like to use vba to keep a user from entering a duplicate entry based off of two columns.

I've looked extensively and thought I found something that worked but after working on other code it didn't seem to work right. Here is all of my code:

' Runs events based on target changes

Private Sub Worksheet_Change(ByVal Target As Range)



    Confirmation Target

    'ChangeDirectCost Target



End Sub



' Keeps user from entering the same vendor / part # twice

Private Sub Confirmation(ByVal Target As Range)

    With Target

        If (.Column <> 2 And .Column <> 5) Or .Cells.Count = 1 Then Exit Sub



        If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then

            Application.DisplayAlerts = False

            .ClearContents

            Application.DisplayAlerts = True

            MsgBox "Vendor / Part # combination already exists!"

        End If



    End With

End Sub

Since I'm new at programming I'm trying to keep it simple, initally the code was under a Module but didn't seem to work at all until I put it under Sheet1.

Please help!

excel vba duplicates

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

edited Jan 18 at 22:21

VBasic2008

2,5812414

edited Jan 18 at 22:21

VBasic2008

2,5812414

edited Jan 18 at 22:21

VBasic2008

2,5812414

asked Jan 18 at 21:46

user10935563

asked Jan 18 at 21:46

user10935563

asked Jan 18 at 21:46

user10935563

The only issue I could find is that you probably wanted to use '... Or .Cells.Count > 1'. Nice work.

– VBasic2008
Jan 18 at 22:16

Since you want to look for the combination, you should change your countif to a countifs, including both columns in the criteria. I would also turn application.enableevents to false before the ClearContents then back to true after

– wallyeye
Jan 19 at 1:39

Those changes seemed to fix it. Thank you!!!

– user10935563
Jan 21 at 15:06

Actually, sorry...I'm now getting my MsgBox after any addition to either column 2 or 5. It's not even waiting for me to input values into the other column. I made sure that none of my rows have blank values in case that was causing the issue. Any other suggestions...?

– user10935563
Jan 21 at 15:16

add a comment |

The only issue I could find is that you probably wanted to use '... Or .Cells.Count > 1'. Nice work.

– VBasic2008
Jan 18 at 22:16

Since you want to look for the combination, you should change your countif to a countifs, including both columns in the criteria. I would also turn application.enableevents to false before the ClearContents then back to true after

– wallyeye
Jan 19 at 1:39

Those changes seemed to fix it. Thank you!!!

– user10935563
Jan 21 at 15:06

Actually, sorry...I'm now getting my MsgBox after any addition to either column 2 or 5. It's not even waiting for me to input values into the other column. I made sure that none of my rows have blank values in case that was causing the issue. Any other suggestions...?

– user10935563
Jan 21 at 15:16

The only issue I could find is that you probably wanted to use '... Or .Cells.Count > 1'. Nice work.

– VBasic2008
Jan 18 at 22:16

Since you want to look for the combination, you should change your countif to a countifs, including both columns in the criteria. I would also turn application.enableevents to false before the ClearContents then back to true after

– wallyeye
Jan 19 at 1:39

Those changes seemed to fix it. Thank you!!!

– user10935563
Jan 21 at 15:06

Actually, sorry...I'm now getting my MsgBox after any addition to either column 2 or 5. It's not even waiting for me to input values into the other column. I made sure that none of my rows have blank values in case that was causing the issue. Any other suggestions...?

– user10935563
Jan 21 at 15:16

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54261850%2fprevent-duplicate-entries-in-multiple-columns-using-vba%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Brtdku