+ Reply to Thread
Results 1 to 9 of 9

Restricting data type in a column

  1. #1
    Forum Contributor
    Join Date
    09-15-2012
    Location
    Jamaica
    MS-Off Ver
    Microsoft 365
    Posts
    244

    Restricting data type in a column

    Hello,

    I want to be able to restrict the data type that can be entered in range "Q5:Q6267". Only the value "BKS" and "" (empty cell) should be allowed, anything else should trigger a message that i will create. I dont want to use the data validation process, i want to do it in VBA coding. Below is what i have so far but it doesnt work, can you tell me what i am doing wrong, thank you.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restricting data type in a column

    Try this...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 05-29-2014 at 08:28 PM. Reason: Typo
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    09-15-2012
    Location
    Jamaica
    MS-Off Ver
    Microsoft 365
    Posts
    244

    Re: Restricting data type in a column

    AlphaFrog thank you for your code it works great. I am trying to do the same thing you did for me with column "Q", to column "R, U, V". but everything i try doesn't work. I have been at this since yesterday with no success. Can you help me with adding the same concept to columns "R, U and V", thank you.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Restricting data type in a column

    Reusing complete previous code from AlphaFrog a suggestion
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-15-2012
    Location
    Jamaica
    MS-Off Ver
    Microsoft 365
    Posts
    244

    Re: Restricting data type in a column

    PCI thank you for responding. I made an error in asking for what i want. The other cells will not check for "BNS", "Q" i checking for "BNS", while "R" will be checking "FGB", "U" will be checking for "FCIB" and "V" will be checking for "SAG".

    Thats why i am having so much problems with getting it done, any help will be greatly appreciated. thanks in advance.

  6. #6
    Forum Contributor
    Join Date
    09-15-2012
    Location
    Jamaica
    MS-Off Ver
    Microsoft 365
    Posts
    244

    Re: Restricting data type in a column

    This is what i have so far, but when it fires once, the macro doesn't seem to work after that. So yes it works, but only on the first attempt, doesn't work for a second attempt, thats weird.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Restricting data type in a column

    See next code
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    09-15-2012
    Location
    Jamaica
    MS-Off Ver
    Microsoft 365
    Posts
    244

    Re: Restricting data type in a column

    Thank you all for helping, PCI the code works, but when i add the message box to the code (i added it below the cell.ClearContents code), the message box basically warns / advises the customer on why what they entered was removed. Once i add the message box the code only executes once and i cant seem to get it to fire again. But everything else works well.

    I was thinking on running the code without the error message box, but when the cell is cleared the user may not know what they are doing wrong. So on second thoughts i do believe the error message box would be important. Kindly assist further if possible thank you.
    Last edited by kmakjop; 06-02-2014 at 09:49 AM.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Restricting data type in a column

    Try something like this...

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Restricting a max number of a column +1 in a text box on a userform
    By How How in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 04:03 PM
  2. [SOLVED] restricting column repeats to a certain number
    By hbangalore in forum Excel General
    Replies: 6
    Last Post: 03-26-2012, 07:10 PM
  3. Restricting the search to one column
    By cheaperThanAPro in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-11-2007, 04:49 PM
  4. [SOLVED] Restricting copied text to one Column
    By Viking in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2006, 09:30 AM
  5. create macro to move label type data to column data
    By JonathonWood9 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2005, 07:06 PM

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