Forum Discussion

rbalza's avatar
rbalza
Brass Contributor
Jul 12, 2021
Solved

Cell as a button

Hi Friends, 

 

What were the ways to make a cell a button? I was trying to count the text in a button but don't actually know how to do it. Thanks 🙂

 

 

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 12, 2021

    rbalza 

    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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 12, 2021

    rbalza 

    If, with everyone's permission, I may add my humble opinion.
    My approach:
    With settings: right click on the button and then under "Format control" - "Properties" - "Depending on cell position and size".
    With VBA:

    Private Sub posbuttons()
Dim pos1 As Range
Set pos1 = Range("d7")
With Me CommandButton1
   .Height = pos1.Height
   .Width = pos1.Width
   .Top = pos1.Top
   .Left = pos1.Left
End With
End Sub

    The 10 and 5 as row and column variables in the code and the potato salad is ready! :))

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

     

    • rbalza's avatar
      rbalza
      Brass Contributor
      Jul 12, 2021
      Hi Hans, would like to actually include the text on the button on the countif formula. As you can see on the snippet, the "x" mark were counted as 2 using countif formula. Hence, would like to know the workaround if we can include on the countif whatever the text on the button is selected.

Resources