• New guy so go easy!!

    Trying to validate (maybe validate is the incorrect term) a cell's data but its a part of an automatic process which cannot happen. So my question is how do I create a macro that will display a message when a linked cell is a specific value?


  • Welcome to OzGrid,

    If you want to be able to check constantly as the user enters data in these cells, then you will need to use formulas.

    If you rather have it that you can check the cells anytime you want, then you could use a Sub.

    In order to use formulas, you could generate a formula for each cell in question doing the following:

    Sub GenerateFormulas
         'Range, and worksheet to set formulas
         for each cell in worksheets("Sheet 1").Range("A1:Z10")
              'fill the formula for what you need
              cell.formula = "=if(value, true, false)"
         next cell
    end sub

    if you just want to check wheneer you want, you can do the following:

    Sub CheckCells
         'Range, and worksheet to check
         for each cell in worksheets("Sheet 1").Range("A1:Z10")
              if cell.value = 66 then
                     'display message
         next cell
    end sub
  • Cell Validation

    Depending on how data ends up in the cells I'm not so sure that there is any need to use code (VBA) to perform validation.

    If data ends up in a cell by linking or importing then maybe vba code is necessary but if you want to validate values / text etc as you enter then Excel's built in Data Validation should more than suffice.

    There are some excellent examples of Data Validation at Data validation when combined with Dynamic Named Ranges is extremely powerful and could replace vba code of reasonable complexity.

    Alternatively you could do a search of the forum looking for postings on the subject....there are lots of postings.

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!