Urgent problem with worksheet_change event

[jonnyknowsbest]

I have a range of cells, E16 through H20. I only want to allow one of these cells to have a value at any one time.

Any ideas how i can achieve this?

 

[SkipVought]

Hi,

Select your range...

Data/Validation

Custom
[tt]
=COUNTIF($E$16:$H$20,E16)=1
[/tt]


Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[jonnyknowsbest]

Not sure if this will work. WHat i want to do is:

I have 3 cells, A1, B1, and C1. If i enter something into A1, i want B1 and C1 to be cleared. If i enter text into B1, i want A1 and C1 to be cleared.

 

[Zathras]

What you have described is not very intuitive for the average user.

I would consider a different user interface: Put radio buttons in each of your cells E16:H20 and use one (different) cell for the input value.

Then enter the value to be used and click the appropriate radio button.

 

[SkipVought]

I answered your original question, did I not?

I only want to allow one of these cells to have a value at any one time.

Please be CLEAR, CONCISE and COMPLETE.

right-click the sheet tab.

select Worksheet from the dropdown in the upper lh of the code window

select Change from the dropdown in the upper rh of the code window

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r as Range
  application.enableevents = false
  For each r in [E16:H20]
    if target.address <> r.address then
      if target.value = r.value then r.clearcontents
    end if
  next
  application.enableevents = true
End Sub

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[jonnyknowsbest]

Well dang!!!

I've been sat here for over 5 hours trying to sort this out, and Skip comes along and solves it in 7 / 8 lines of code.

Once again. Dang !!!!

Cheers Skip