Disable cell based on another cell value

Topic

New Reply

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

Reply | Quote

Replies

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: 45089-Dummy-Data-RGv1

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Abhishek,

In that case you can remove the line that allows access to column D as indicated in the comments. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Abhishek,

What exactly do you mean by disable? The code merely prevents the user from changing the cells. Do you want something else? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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: 45102-Dummy-Data-RGv2

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Actually I am fetching % value of Col D from another sheet

RG,

Using your code, if a user selects the developer tab then design mode, the sensitive cells in col F,G,H are left defenseless. Would a better approach be to lock the 3 columns and then the worksheet. Then using the Worksheet_change event and the target row, unlock F,G,H in that row if the conditions were met and to re-lock them if they were not met? If the user enters design mode, at least the cells would be locked except for rows that col D was 100%… but they would be unlocked anyway by conditions set.

Since the user states he is fetching the percent value, it does not sound like it is a calculated value but rather a passed value.

HTH,
Maud

Reply | Quote

Abhishek,

Just to be sure could provide the Letters of the Columns to be disabled. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG,

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

Best Regards,
Abhishek

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..here’s my interpretation:

The vba routine assigned to the button [Refresh] checks the data column [D]
This can be run after data is imported etc etc.

zeddy

Reply | Quote

Many Thanks Zeddy. It is working absolutely fine. Just 2 things if possible please;

1. We need to Refresh to run vba code, could it be possible if macro would run automatically at the moment new entry or change in Column D.
2. On running the macro, the sheet would be protected. Could it be possible that only the greyed cells would be protected.

Best Regards,
Abhishek

Reply | Quote

Hi Abhishek

See attached version.

With sheet protection, you define which cells, columns etc you want to allow data entry by formatting those cells and unticking the Locked status for those cells ie. use Format Cells>Protection

I have added a sheet code routine that checks for User changes made in column [D].
Any changes made in column [D] will trigger the [refresh] routine.
If you change a col [D] cell value form say, 100% to 85%, this will allow data entry in columns [F:H]

zeddy

Reply | Quote

Many Thanks Zeddy. It’s working absolutely fine.

Best Regards,
Abhishek

Reply | Quote