Jul 11 2021 05:31 PM
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 :)
Jul 12 2021 02:22 AM
I'm afraid I don't understand your question. Can you explain in detail what you want to happen?
Jul 12 2021 02:44 AM
Jul 12 2021 03:59 AM
Jul 12 2021 04:01 AM
Again, I don't understand what you want. Perhaps a pivot table would be more efficient.
Jul 12 2021 04:07 AM
@Hans Vogelaar kinda like this. Do I have to create multiple macros or there's an efficient way to do this?
Jul 12 2021 04:19 AM
Multiple buttons are difficult to maintain.
In the attached version, you can double-click the light-green cells to toggle between the three characters.
Jul 12 2021 04:26 AM
Jul 12 2021 04:30 AM
SolutionRight-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.
Jul 12 2021 04:32 AM
Jul 12 2021 04:41 AM
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.
Jul 12 2021 05:32 AM
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)
May 27 2022 09:46 PM
May 28 2022 01:05 AM
That is a completely different question. Please start a new discussion and ask your question there.
Jul 12 2021 04:30 AM
SolutionRight-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.