SOLVED

Contributor

# 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

14 Replies

# Re: Cell as a button

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

# Re: Cell as a button

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.

# Re: Cell as a button

See the attached version.

# Re: Cell as a button

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.

# Re: Cell as a button

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

# Re: Cell as a button

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

# Re: Cell as a button

Multiple buttons are difficult to maintain.

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

# Re: Cell as a button

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

# Re: Cell as a button

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.

# Re: Cell as a button

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?

# Re: Cell as a button

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.

# Re: Cell as a button

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)

# Re: Cell as a button

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?