+ Reply to Thread
Results 1 to 5 of 5

audio alert

  1. #1
    Registered User
    Join Date
    10-07-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    audio alert

    relatively new to excel and completely new to VBA so i'm really stuck. I'm in the middle of and inventory and trying to make things simpler.
    In my spreadsheet i have colB filled with unique asset #s of all the items i should have on the shelf. I am using a barcode scanner to read these numbers into colA.
    I have set up 3 conditions and recorded them as a macro. 1) if a unique number then format cell fill color to red 2) if a duplicate number then format cell fill color of the matching cells to green 3) =countif($A:B$, A1)>2 format cell fill color of the matching cells yellow and the text to bold red.

    The idea is that when i scan a number and it's supposed to be there the 2 matches turn green, if i have something extra on my shelf that number will make the fill color red in colA. the 3rd condition allows me to know if i scan a number twice because it will turn the new cell and the 2 that have already matched to yellow. If all goes well i should have 2 columns of green cells. But that's dreaming, so if i have any red cells those are the items i have to investigate. Of course there should be no yellows.

    In addition I would like to 1) when a number is scanned and entered into colA that number is entered into the colA cell directly beside its coresponding colB cell regardless of which cell the cursor is in prior to the scan. 2) sound an audio arlert when the cells turn yellow so that i don't have to keep looking at the screen when i scan.

    I don't know how to modify the macro shown nor do i know how to use VBA to do the things i'm already doing plus the 2 additional conditions.

    This is the macro:

    Please Login or Register  to view this content.
    thanks bunches
    Last edited by shg; 10-07-2009 at 06:48 PM. Reason: change title

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA & macros

    Welcome to the forum.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-07-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: VBA & macros

    Thanks for the welcome and i hope the title is more suitable

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    re: audio alert

    It is much better, thank you, minus the spurious words that I will remove.

    I should have also pointed out that you need code tags -- I added them for you this time, please use them in the future.

    So col A already contains all the barcode numbers, new numbers go in Col B next to a matching value in col A, and if there is already a matched number in col B, the code beeps -- correct?

    What if there is no match in col A?

  5. #5
    Registered User
    Join Date
    10-07-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: audio alert

    Quote Originally Posted by shg View Post
    It is much better, thank you, minus the spurious words that I will remove.

    I should have also pointed out that you need code tags -- I added them for you this time, please use them in the future.

    So col A already contains all the barcode numbers, new numbers go in Col B next to a matching value in col A, and if there is already a matched number in col B, the code beeps -- correct?

    What if there is no match in col A?
    not quite right
    i'm given a worksheet with all unique numbers in colB starting at B2 and all cells have red fill color. These are the items i'm suppose to have on my shelves.
    I scan into colA; if there's a match in colB then both cells turn green. If there is no match colA cell turns red indicating i have something extra.
    If i rescan an item that already has a match then the 3 cells turn yellow.

    I would like to know how to 1) make the randomly scanned # move from the active cell into which it was scanned to the A cell beside its match in colB and then delete it from the cell into which it was scanned. 2) When a rescan occurs, turn the cells yellow and sound the audio alarm; in that way i don't have to look at the laptop all the time, when i hear the alarm i just look for the yellow cells and delete the extra one.

    In theory, when i'm done scanning, all matched numbers will be side by side and the cells will be green, any unique numbers, colA or colB will be in red cells and those will have to be investigated. There should be no yellows.
    The code in the original post is the macro recorded while doing the conditional formatting.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1