Restrict values in 1 column

[kathleenfarber]

Hi,

I am trying to restrict values in column D if column G has an entry of 1 and vice versa. I don't think Data Validation will work. Any ideas? Example is if they choose full service in Units A they are unable to choose Units B and vice versa. Any help appreciated.

	ANNUAL FEE	UNITS A	COST	ANNUAL FEE	UNITS B	COST
NSCLC	$100.00		( auto computes )	$60.00		(auto computes)
Late IO	$500.00		( auto computes )	$500.00		( auto computes )

 

[jasonb75]

I don't think Data Validation will work. Any ideas?

It will work fine with a custom validation rule.

For column D, use =G2="" (assuming starting from D2). For column G, use =D2=""

 

[mumps]

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. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D,G:G")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 4
            If Range("G" & Target.Row) <> "" Then
                If MsgBox("You have already chosen Units B." & Chr(10) & "If you want to choose Units A, you will have to delete Units B." & Chr(10) & "Do you want to delete Units B?", vbYesNo) = vbYes Then
                    Range("G" & Target.Row).ClearContents
                Else
                    Target.ClearContents
                End If
            End If
        Case Is = 7
            If Range("D" & Target.Row) <> "" Then
                If MsgBox("You have already chosen Units A." & Chr(10) & "If you want to choose Units B, you will have to delete Units A." & Chr(10) & "Do you want to delete Units A?", vbYesNo) = vbYes Then
                    Range("D" & Target.Row).ClearContents
                Else
                    Target.ClearContents
                End If
            End If
    End Select
    Application.EnableEvents = True
End Sub

 

[kathleenfarber]

This is great! Thank you both!

 

[mumps]

Glad to help.

 

[kathleenfarber]

@mumps would you be willing to help me merge these two macro's? I'm very new to this. Slowly learning!

The one you provided works perfectly! I just need to combine the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D5:D29,G5:G29")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 4
            If Range("G" & Target.Row) <> "" Then
                If MsgBox("You have already chosen units under LSIO COMBINED." & Chr(10) & "If you want to choose units under FULL SERVICE, you will have to delete the LSIO COMBINED." & Chr(10) & "Do you want to delete the LSIO COMBINED units?", vbYesNo) = vbYes Then
                    Range("G" & Target.Row).ClearContents
                Else
                    Target.ClearContents
                End If
            End If
        Case Is = 7
            If Range("D" & Target.Row) <> "" Then
                If MsgBox("You have already chosen units under FULL SERVICE." & Chr(10) & "If you want to choose units under LSIO COMBINED, you will have to delete units under FULL SERVICE." & Chr(10) & "Do you want to delete units under FULL SERVICE?", vbYesNo) = vbYes Then
                    Range("D" & Target.Row).ClearContents
                Else
                    Target.ClearContents
                End If
            End If
    End Select
    Application.EnableEvents = True
End Sub


Sub MainClearcells()
Range("D5", "D29").ClearContents
Range("G5", "G28").ClearContents
Range("J5", "J29").ClearContents
End Sub

 

[mumps]

Range("D5", "D29").ClearContents
Range("G5", "G28").ClearContents
Range("J5", "J29").ClearContents

When do you want to clear these cells? At what stage in the process do you want to clear these ranges?

 

[kathleenfarber]

I have a button that the user can press to clear the cells.

 

[mumps]

I thought you wanted to merge the two macros you posted. The Worksheet_Change macro you posted does exactly the same thing as the macro I suggested so I'm not sure how you want to combine them. As far as I can see, comparing the two, there is no need to merge them because they basically do the same thing. Please clarify in detail what you would like to do.

 

[kathleenfarber]

I see what you are saying now! I have a button on a different sheet to clear a different chart. Thought I could do the same on this sheet. The original macro you helped with is more than enough. It's been a day with this file LOL just want it done. Thanks again!