+ Reply to Thread
Results 1 to 4 of 4

Creating a vba macro that opens a msgbox if more than one cell is not empty in a range

Hybrid View

  1. #1
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Creating a vba macro that opens a msgbox if more than one cell is not empty in a range

    I am somewhat of a beginner to macros and need help with solving this macro. I have 3 columns (col. G, col. H, and col. I) that a user can enter an "x" into any cells in each row for these 3 columns. However, I do not want the user to be able to choose more than one option per row within these 3 columns.

    These are the 3 different options that a user can choose from and type an "X" in the appropriate cell under one of these columns in each row.
    Column G = "Regulatory Obligation", Column H = "Hazardous Substance", Column I = "Fire Protection" (these are the titles of each of the 3 columns)
    Rows = 8:100
    ranges = G8:G100, I8:I100, H8:H100
    Full range = G8:H100

    I would like an error message box (msgbox) to appear if the user chooses more than 1 options (places an "X" in a 2nd or 3rd box). I would like this formula to work for all the rows G8:H8 down to G100:H100.

    Msgbox = "ERROR: you have chosen more than 1 option. Please review the options and choose ONLY one." (or something like that)

    AM_BPTemplate_USA-CANADA_v1.xlsm

    Can someone please help me? I greatly appreciate it.

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: Creating a vba macro that opens a msgbox if more than one cell is not empty in a range

    Hi,
    No need of a macro.

    . Select G8:I8
    . Go in Data > Data Validation
    . Select "Custom"
    . Type =COUNTA($G8:$I8)<=1
    . Go in "Error Alert" tab and type your error message
    . Click Ok
    . Select and Copy G8:I8 to G9:I100
    . Done !
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Creating a vba macro that opens a msgbox if more than one cell is not empty in a range

    The reason that I am asking for help on solving this macro is because I am already using Data Validation with those columns. I have created a drop-down list for G8:I100 allowing the user to choose option 1, 2 or 3 for each cell that in that range. As far as I know, it is not possible to have more than one Data Validation for a cell. If there is, I am not aware on how to do it. This is why I figured a macro would be used. What do you think?

  4. #4
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Creating a vba macro that opens a msgbox if more than one cell is not empty in a range

    Can someone help me with this? Please.

+ 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. [SOLVED] Msgbox all empty rows in a range
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-11-2014, 11:48 AM
  2. Display msgbox if two cells in range are not empty
    By loroverde in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2014, 12:49 AM
  3. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  4. Replies: 3
    Last Post: 04-12-2012, 01:57 PM
  5. How can I have a macro run (to displace a msgbox) once this workbook opens?
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2012, 10:35 AM
  6. Creating a "screenshot" image msgbox pop-up based on a cell range?
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-16-2011, 08:49 AM
  7. Msgbox - when cell is empty
    By KA_Analyst in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2007, 04:36 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