Data Validation Question

Hi,

I would like to know if I can "call in" data validation using a macro.

I have a list in a certain worksheet, say Sheet "my list", and I want
to add data validation to Column A in another sheet "status". I tried
doing this manually, but then, when I run my parent macro for the
project, I have the "Clearcontents" option in that (so that when the
macro is run, it wont print out results for the previous run), and that
seems to remove the data validation property in Col A.

Can anyone help me with this? (I searched online, but most of the stuff
I found is either out of context, or too abstruse for me - I'm a
novice in VB programming, but am learning fast)

Thanks a lot,

Joe.

Columns("A:A").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="00", Formula2:="55"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

this add validation to column "A" for a whole number from 00 to 55. you can
edit any of the variable.
This was created using the macro recorder. Try using that to get the exact
validation you need, that way you can see proper Syntax









--
When you lose your mind, you free your life.


"Joe" wrote:

> Hi,
>
> I would like to know if I can "call in" data validation using a macro.
>
> I have a list in a certain worksheet, say Sheet "my list", and I want
> to add data validation to Column A in another sheet "status". I tried
> doing this manually, but then, when I run my parent macro for the
> project, I have the "Clearcontents" option in that (so that when the
> macro is run, it wont print out results for the previous run), and that
> seems to remove the data validation property in Col A.
>
> Can anyone help me with this? (I searched online, but most of the stuff
> I found is either out of context, or too abstruse for me - I'm a
> novice in VB programming, but am learning fast)
>
> Thanks a lot,
>
> Joe.
>
>

Thanks a lot, Ben. I have more questions now:

1. What is a macro recorder? How can I access it?

2. My list is a bunch of strings - not numbers. What would I have to
change to the above macro to achieve that?

Thanks,

Joe.

in excel on the main menu acess the following menu

Tools>Macros>Macro Recorder

what this does is records Actions that your perform manually in Excel and
writes them into VBA code in a module for you.

So what you need to do is

run the macro recorder
setup your data validation
stop the macro recorder

Then in VBA editor, you should now have the proper syntax for the data
validation you need.




--
When you lose your mind, you free your life.


"Joe" wrote:

> Thanks a lot, Ben. I have more questions now:
>
> 1. What is a macro recorder? How can I access it?
>
> 2. My list is a bunch of strings - not numbers. What would I have to
> change to the above macro to achieve that?
>
> Thanks,
>
> Joe.
>
>

Thanks a bunch, Ben!

Joe.

Ben,

I have a routine which adds a new row at row3 for addition of new data. The
routine includes the code listed below but has no effect. I even clicked
"Data"/"Validation"etc. and applied the settings manually. They work for the
row in question until the routine is run and then they move with the row of
data to row4. The routine specifies row3 after the new row has been added so
it should work but doesn't.

Do you have a solution for this dilemma?

Thanks,

Jim
--
Pops Jackson


"ben" wrote:

> Columns("A:A").Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, Formula1:="00", Formula2:="55"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
>
> this add validation to column "A" for a whole number from 00 to 55. you can
> edit any of the variable.
> This was created using the macro recorder. Try using that to get the exact
> validation you need, that way you can see proper Syntax
>
>
>
>
>
>
>
>
>
> --
> When you lose your mind, you free your life.
>
>
> "Joe" wrote:
>
> > Hi,
> >
> > I would like to know if I can "call in" data validation using a macro.
> >
> > I have a list in a certain worksheet, say Sheet "my list", and I want
> > to add data validation to Column A in another sheet "status". I tried
> > doing this manually, but then, when I run my parent macro for the
> > project, I have the "Clearcontents" option in that (so that when the
> > macro is run, it wont print out results for the previous run), and that
> > seems to remove the data validation property in Col A.
> >
> > Can anyone help me with this? (I searched online, but most of the stuff
> > I found is either out of context, or too abstruse for me - I'm a
> > novice in VB programming, but am learning fast)
> >
> > Thanks a lot,
> >
> > Joe.
> >
> >