+ Reply to Thread
Results 1 to 7 of 7

Macro that Blocks Data in a Column if Specific Data is Put in Another Column

  1. #1
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    108

    Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    Is there a way to create a simple macro where if column B contains "client", then it blocks the user from entering data in columns H and I for that respective row? This would be applied to a table that expands daily as new data is added.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,024

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    Right click on sheet name, view code, paste this code and save it. It should lock the cells based on the input. Untested.
    Please Login or Register  to view this content.
    Click the * to say thanks.

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,111

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    Another option:
    Start by unlocking all the cells in the sheet. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Replace the password (in red) with a password of your choosing. Close the code window to return to your sheet. Make an entry in column B and press the RETURN key.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    108

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    I tried both options and neither option worked. I changed "client" to my client's name, and I changed the column to M and N which apply to my worksheet. However, I noticed two things:
    1.
    Case Is = "client"
    Me.Range(Me.Cells(c.Row, "H"), Me.Cells(c.Row, "I")).Locked = True
    Me.Range(Me.Cells(c.Row, "I"), Me.Cells(c.Row, "I")).Locked = True
    Case Else
    Me.Range(Me.Cells(c.Row, "H"), Me.Cells(c.Row, "I")).Locked = False
    Me.Range(Me.Cells(c.Row, "I"), Me.Cells(c.Row, "I")).Locked = False

    Why is" H" once and "I" thrice used? Wouldn't "H" and "I" be used twice in the code? Also, can the code not allow a person to select the column, just skip over to column J in the respective row?

    When I tested it, I tried to enter something in the columns and I receive a debug error.

    Finally, I can't password protect the sheet because I have table in it. Excel won't extend tables if the sheet is protected. I would like to password protect the VBA so no one can view it, but is another post altogether.

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,111

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    In order to lock cells so that they cannot be edited, the sheet must be protected. What do you mean by "extend tables"? I tested the macro on some dummy data and it worked as you requested. Can you attach a copy of your file (de-sensitized if necessary)?

    To protect your macros, you have to protect your VBA Project. Do the following:
    -hold down the ALT key and press the F11 key to open the Visual Basic Editor
    -click on 'Tools' on the top menu
    -click 'VBAProject Properties'
    -click the 'Protection' tab
    -click the box to the left of 'Lock project for viewing' to put a check mark in it
    -enter your password and then confirm it and click 'OK'
    -close the VB Editor
    -save your workbook as a macro-enabled file and close it
    When you re-open the file, you will not be able to see the macros unless you enter the password. Keep in mind that this type of protection is not very strong and anyone who really wants to get at your macros can probably do it with a little research.

  6. #6
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    108

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    Hi, Mumps1.

    If the sheet is protected, Excel won't allow additions to the table inserted into the sheet. I attached a dummy excel sheet. I need a VBA macro that will force N/A into column M and N if the client is DOGGIE. Subsequently the user cannot alter the N/A in columns M and N for that row for client DOGGIE. The sheet cannot be protected because there are daily additions to the table. Excel, to my knowledge, won't allow additions to a table if the sheet is locked.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,488

    Re: Macro that Blocks Data in a Column if Specific Data is Put in Another Column

    How about
    Please Login or Register  to view this content.
    Last edited by Fluff13; 11-16-2019 at 05:01 PM.

+ 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. locking blocks of data - then sorting them by a column
    By Symbiot in forum Excel General
    Replies: 0
    Last Post: 08-29-2019, 03:22 PM
  2. Replies: 31
    Last Post: 11-09-2016, 05:00 AM
  3. [SOLVED] Loop for vba macro from a specific column to the last column with data
    By lcgar in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 10-07-2016, 11:41 AM
  4. [SOLVED] Data Parsing - Segregating rows data under specific column headers - Macro creation
    By harman83 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-14-2016, 04:55 PM
  5. Replies: 2
    Last Post: 09-28-2015, 07:57 PM
  6. [SOLVED] Mixing Blocks of Data from 2 Columns into 1 Column, with conditions
    By jocund.ky in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2012, 06:40 PM
  7. Clubbing blocks of Data for two entities in a Column Chart
    By e4excel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2009, 10:50 AM

Tags for this Thread

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