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
Platinum 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)

 

 

Resources