VBA to clear content of range row on DoubleClick

[TLMike]

Hi everyone. Beginner at VBA here.

I have a sheet with two ranges. Each range is a territory assigned to managers by state.
What I would like to do is use a DoubleClick function to add a 1 to the cell clicked. But, each state cannot be assigned to multiple managers. So, if another cell in that row is clicked, e.g., reassigned, the the other cells in that row should clear contents and place a 1 in the "new" cell.

I've gotten about halfway on this by adapting code from another sheet. (You ever write code and years later look at it and think "what monster wrote this".)
I appreciate any help on this.

 

[Peter_SSs]

Welcome to the MrExcel board!

Pretty hard to set up and test something when we don't really know what your worksheet or data looks like. Also, statements like "each state cannot be assigned to multiple managers." means nothing to us so could you provide a small set of sample dummy data with XL2BB (If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.) and explain again with eference to the sample data?

 

[TLMike]

Each table consists of a header row with manager names. The initial column is each US state. There is no other "data" to speak of. I want the cells to only contain a 1, or be blank.

Please observe row6, CA, and how there are 1s in columns C and E. This is a case that I don't want to happen.

My Goal: When I double click any cell in a given row, say B4, which would "reassign" Alabama to SMITH, I would like all the contents of row 4 to clear, to include cell D4, so that the 1 can be printed in cell B4.

I would have posted the VBA that I have already and a Minisheet, but I don't have Excel on this PC. Sorry about that.

 

[Peter_SSs]

I think this double-click event code does what you want.
It also removes the "1" if a cell already containing a "1" is double-clicked. Not sure if you wanted that as well.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim RngMgr As Range
 
  With Range("A1").CurrentRegion
    Set RngMgr = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
  End With
  If Not Intersect(Target, RngMgr) Is Nothing Then
    Cancel = True
    If Target.Value = 1 Then
      Target.ClearContents
    ElseIf IsEmpty(Target.Value) Then
      Intersect(Target.EntireRow, RngMgr).ClearContents
      Target.Value = 1
    End If
  End If
End Sub

 

[TLMike]

Thank you

 

[Peter_SSs]

You're welcome.

 

[TLMike]

I should report that it absolutely worked a charm. Thank you again.

 

[Peter_SSs]

Thanks for the follow-up.