SOLVED

Cell as a button

Brass Contributor

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

 

rbalza_0-1626049830475.png

 

14 Replies

@rbalza 

I'm afraid I don't understand your question. Can you explain in detail what you want to happen?

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.

@rbalza 

See the attached version.

Hi Hans, much appreciated it. I kinda get how it works. However, is there an efficient way to do say if I have multiple buttons in it? What I was trying to do is that, fill the cells with multiple buttons and count how many 'x' were there.

@rbalza 

Again, I don't understand what you want. Perhaps a pivot table would be more efficient.

@Hans Vogelaar kinda like this. Do I have to create multiple macros or there's an efficient way to do this?

 

rbalza_0-1626087987667.png

 

@rbalza 

Multiple buttons are difficult to maintain.

In the attached version, you can double-click the light-green cells to toggle between the three characters.

This is freaking awesome and this is actually what I wanted :) But how'd you done it?
best response confirmed by rbalza (Brass Contributor)
Solution

@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.

Sorry, 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?

@rbalza 

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.

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

 

 

Hi Hans sorry to join this conversation. I have a different question relating to the use of buttons. How can I set up a button to open different tabs in the same workbook to save scrolling through them?

@Robmontie 

That is a completely different question. Please start a new discussion and ask your question there.

1 best response

Accepted Solutions
best response confirmed by rbalza (Brass Contributor)
Solution

@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.

View solution in original post