MsgBox popping up on entering of certain text in any row of Column X

[andreashermle]

Dear Experts:

I need help with a macro:

As soon as a user enters a certain string, say "text" in any row of
Column A (with the exception of the column heading) , a msgbox is to
pop up, saying "This is not allowed!"

I would favour a macro solution, but if there is a built-in
functionality to perform this, that would be great too.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[OssieMac]

Hi Andreas,

Use Data Validation. (Unless the column header contains an invalid entry
then there is no reason not to apply the validation to the entire column.)

Instructions:
for xl2007
Select Column A or select from A2 to the bottom of the worksheet. (Select A2
and hold Ctrl and Shift keys down and continue to press down arrow until at
the bottom of the worksheet.)
Select Data ribbon
Select Data validation (Data tools block centre of ribbon)

For previous versions of xl
Select Column A or select from A2 to the bottom of the worksheet. (Select A2
and hold Ctrl and Shift keys down and continue to press down arrow until at
the bottom of the worksheet.)
Select Menu item Data -> Validation

Remainder for all versions;
On the Settings Tab
Allow field: Select Custom
Formula field: =A1<>"Text" (Use A2 if selection is A2 to bottom of
worksheet)

If you want an Input message then select Input Message tab and enter the
details.

If you want and and Error alert then select the tab and enter the details.

Note when you enter the data validation, after selecting the range, you
enter the formula as if it applies to the first cell of the selection only
and Excel looks after applying the correct formula to the rest of the
selection.

You can also use other formulas. for example do not allow "Text" or "Mytext"
or "Yourtext"

=AND(A1<>"Text",A1<>"Mytext",A1<>"YourText")

 

[andreashermle]

Hi Andreas,

Use Data Validation. (Unless the column header contains an invalid entry
then there is no reason not to apply the validation to the entire column.)

Instructions:
for xl2007
Select Column A or select from A2 to the bottom of the worksheet. (SelectA2
and hold Ctrl and Shift keys down and continue to press down arrow until at
the bottom of the worksheet.)
Select Data ribbon
Select Data validation (Data tools block centre of ribbon)

For previous versions of xl
Select Column A or select from A2 to the bottom of the worksheet. (SelectA2
and hold Ctrl and Shift keys down and continue to press down arrow until at
the bottom of the worksheet.)
Select Menu item Data -> Validation

Remainder for all versions;
On the Settings Tab
Allow field: Select Custom
Formula field: =A1<>"Text"      (Use A2 if selection is A2 to bottom of
worksheet)

If you want an Input message then select Input Message tab and enter the
details.

If you want and and Error alert then select the tab and enter the details..

Note when you enter the data validation, after selecting the range, you
enter the formula as if it applies to the first cell of the selection only
and Excel looks after applying the correct formula to the rest of the
selection.

You can also use other formulas. for example do not allow "Text" or "Mytext"
or "Yourtext"

=AND(A1<>"Text",A1<>"Mytext",A1<>"YourText")

--
Regards,

OssieMac










- Show quoted text -

Hi OssiMac,

thank you very much for your professional help. I really appreciate
it.

It works as desired.

Regards, Andreas