Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable cell based on another cell value

    Hello Everyone,

    Need help in developing a macro. I came across a scenario in which I wish to disable the cells in Column F, Column G & Column H and also the color if the corresponding cell value in Column D is 100% or N/A or Blank.
    For Example: If D2 is 100%, then F2, G2 & H2 should be disabled and also the color should been changed to grey. Hope able to explain.

    If some body in the group could help me with a macro would be of great help. Thanks in Advance. Dummy Data attached for your reference.

    Regards, Abhishek
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Abhishek,

    Here's some code for your consideration:
    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        Dim lCurRow As Long
        Dim isect As Range
        
        lCurRow = Target.Row()
        
    '*** Exit if in Col D! Othewise you wouldn't be able to change protection!
       If (Not (Application.Intersect(Range("D:D"), Target) Is Nothing)) Then Exit Sub
       
    '*** Exit if Multiple cells selected.
       If (Target.Count > 1) Then Exit Sub
       
       Select Case Cells(lCurRow, "D").Value
             Case "N/A", "", 1
                 Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbRed
                 MsgBox "Don't Touch ME"
                 [A1].Select
             Case Else
                 Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbWhite
                 MsgBox "Have your way with me!"
       End Select
       
       
    End Sub  'Worksheet_SelectionChange
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim lCurRow As Long
       Dim isect As Range
        
       lCurRow = Target.Row()
      
    '*** Exit if Multiple cells selected.
       If (Target.Count > 1) Then Exit Sub
    
       Set isect = Application.Intersect(Range("D:D"), Target)
       If isect Is Nothing Then Exit Sub  'Get out of here!
       
       Select Case Cells(lCurRow, "D").Value
             Case "N/A", "", 1
                 Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbRed
                 MsgBox "Don't Touch ME"
                 [A1].Select
             Case Else
                 Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbWhite
                 MsgBox "Have your way with me!"
       End Select
    
    End Sub  'Sub Worksheet_Change
    When a cell is selected in any column except D! The value in Col D of that Row will be checked for Blank, "N/A", or 1 (100%).

    If there is a match the cells in that Row will be turned RED and a message displayed (you can easily comment out or delete the message). After the message is dismissed the cursor will be moved to cell A1 thus preventing the user from making changes to the row.

    If you select a cell in Col D it will allow you to change it then immediately check to see if it should be turned red or turned back to white.

    Nothing will happen if multiple cells are selected.

    I'm sure I missed some condition but this should give you a good start.

    Here's your test file with the code in it: Dummy Data-RGv1.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many Thanks Retired Greek. It's a very good starting point.

    Actually I am fetching % value of Col D from another sheet (means it would be updated automatically). Hence could it be possible that the values of Col F,G & H would be disabled basis on values in Col D without selecting any cell of Col D.
    Hope it clarifies. Please let me know for further clarifications. Thank you.

    Best Regards,
    Abhishek

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Abhishek,

    In that case you can remove the line that allows access to column D as indicated in the comments. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RG for your reply.

    I have removed the line as indicated in the comments but not working. Don't know if I am doing something wrong. I need the columns would be disabled automatically without clicking. But still only after clicking the columns are getting disabled.

    Best Regards,
    Abhishek

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Abhishek,

    What exactly do you mean by disable? The code merely prevents the user from changing the cells. Do you want something else?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    Actually I am fetching % value of Col D from another sheet (means it would be updated automatically).
    ..sounds like this is a formula cell. So I think Abhishek needs to use the Worksheet_Calculate event.

    zeddy

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Interesting thought. That could be a mess though as you'd have to check every row every time a change caused a recalculation as there is no way to determine which cell caused the event to fire AFAIKT.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Well looks like I was wrong! There is a Workbook_SheetChange Event.

    Code:
    Option Explicit
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, _
                                     ByVal Source As Range)
              
       Dim lCurRow As Long
       Dim isect As Range
       Dim shtPROT As Worksheet
       Dim shtMe   As Worksheet
    
    '   MsgBox "Worksksheet: " & Sh.Name & vbCrLf & _
    '          "Source     : " & Source.Address, _
    '          vbInformation + vbOKOnly, "Status of Change"
    
    '*** Tests for correct sheet/column ***
       If (Sh.Name <> "Sheet2") Then Exit Sub
       If (Application.Intersect(Range("A:A"), Source) Is Nothing) Then Exit Sub
       If (Source.Count <> 1) Then Exit Sub
       
       lCurRow = Source.Row()  '*** If row in data source <> row in table need to adjust
       
       Set shtPROT = ActiveWorkbook.Sheets("Sheet1")   '*** Table Sheet
      
       shtPROT.Activate
       
       Application.ScreenUpdating = False
      
       shtPROT.Activate
         
         Select Case Cells(lCurRow, "D").Value
               Case "N/A", "", 1
                   Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbRed
               Case Else
                   Range(Cells(lCurRow, 1), Cells(lCurRow, 8)).Interior.Color = vbWhite
         End Select
       
        Sh.Select
        
       Application.ScreenUpdating = True
              
    End Sub   'Workbook_SheetChange
    It returns both the Sheet and the Range where the change was made so we can now work on a Single Cell!

    The code I've worked up above just does the coloring for testing purposes.

    Test Program File: Dummy Data-RGv2.xlsm

    If what the OP really wants is for the cells to be protected by excel I'll have to add code to protect/unprotect both the worksheet and the particular ranges affected. Before I tackle that I'll wait for the OP to post back.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RG,

    Sorry might I'm not able to explain properly. Actually I need what you've provided i.e. prevents the user from changing the cells.

    Actually the first 5 columns would get updated automatically from another sheet, hence I need that once I open up the sheet, the last 3 columns would get disabled basis on values of column D. Hope I'm clear now. Thank you.

    Best Regards,
    Abhishek

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Abhishek,

    Just to be sure could provide the Letters of the Columns to be disabled.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RG,

    It would be columns F,G & H to be disabled.

    Best Regards,
    Abhishek

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    It is a truth universally acknowledged, that a man in possession of a wealth of Excel knowledge must be in want of a new Function or Event.

    ..the Workbook_SheetChange Event does not get triggered by an updated formula cell value.

    I believe Abhishek is asking that, when the (formula) value in Column D is 100% or N/A or Blank, then corresponding cells in cols F,G,H are 'greyed out and locked' i.e. are 'disabled' for User input, whereas if Column D isn't "100% or N/A or Blank", then User input to corresponding cols F,G,H are permitted (and those cells are 'not greyed-out').

    So, painfully, whenever a calculate event is triggered, ALL values in col D would need to be checked etc etc etc
    (Although, I suspect, you could limit the Col D range to be checked to the sheet's active range???)

    zeddy

  14. #14
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Exactly this is what I need. Thanks Zeddy for making it more clearer which I can't be able to.

    Could it be possible if I limit the column D range basis on month i.e. column A. Let us suppose if I m in July, it will check the data for July & Jun months only.

    Best Regards,
    Abhishek

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Abhishek,

    Ok, I'm trying to get a handle on the entire process.

    In your posted example Column D had hard coded values. When you "fetch" as you say from the other sheet do you do this via a copy or are you actually using a reference formula to do that work.

    If you are using a formula do the rows correspond between the two sheets?

    Is simply moving out of the "protected" cell sufficient or do you want the cells actually locked via sheet level protection in excel?

    It would really help if you could post your entire workbook so we can see the inter-sheet relationships.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •