Forum Discussion
Cell as a button
- Mar 13, 2018
Hi Lisa,
Easily, you can wrap the formula with one of these functions:
- IFNA
- IFERROR
Examples:
=IFNA(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")
=IFERROR(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")
NOTE: IFNA is available in Excel 2013 or later.
Hope that helps
Haytham
Right-click the sheet tab.
Select 'View Code' from the context menu.
I created a Worksheet_BeforeDoubleClick event procedure. This is run automatically each time you double-click a cell.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("B2:B20"), Target) Is Nothing Then
Cancel = True
Select Case Target.Value
Case "X"
Target.Value = "P"
Case "O"
Target.Value = "X"
Case Else
Target.Value = "O"
End Select
End If
End Sub
The code first checks whether the cell is in the range B2:B20.
If so, it sets Cancel to True to prevent going into edit mode.
Then it changes the value of the cell depending on the current value.
- HansVogelaarJul 12, 2021MVP
It would be possible, but that would be dangerous - every time you click on or move to one of the cells, its value would change. That leaves too much room for mistakes.
- rbalzaJul 12, 2021Brass ContributorSorry, I was about to edit my question and realized that it's in macro 🙂 What my question was, is there a way to do it by single click?