How do I assign a number value to a button in Excel?

Question

I'm novice to computer program, but I want to learn.
I know how to enable the buttons in Excel using developer options.
I want to assign 'button 08' as a number value (in this case, the value is 1)
I want the value to be 0 if the button is in the off position.

Ultimately, this is a grading sheet for ESL University student in 5 categories. I want all the pressed buttons to assign a number value to an overall box labeled 'total'.

I'm using Excel 2013.

Please walk me through it like a child, I'm only slightly familiar with using c++ and eclipse.

Answer 1

I would use ActiveX command buttons, utilizing the Caption property to indicate the button status (On/Off) and Add/Subtract the appropriate value to the Total cell.



Access the Developer tab, click 'INSERT', select the ActiveX command button object, and click and drag a button onto the worksheet. Position it in cell C4 and size it as desired.



Right Click the button and select 'Properties'.



In the Name property at the top, change the name to: btnFluency1



Scroll down to the Caption property and delete the caption text



Close the Properties window.



Double click the command button to access the VBE.



Delete the Click event header/footer:



Private Sub btnFluency1_Click()

End Sub



Copy this event handler and paste it into the editing area you just cleared.



Private Sub btnFluency1_Click()

If Me.btnFluency1.Caption = "" Then

Range("A1").Value = Range("A1").Value + 1

Me.btnFluency1.Caption = "X"

ElseIf Me.btnFluency1.Caption = "X" Then

Range("A1").Value = Range("A1").Value - 1

Me.btnFluency1.Caption = ""

End If

End Sub



Close the VBE and exit Design Mode by clicking the Design Mode icon in the Developer tab



Now, click the button. The button Caption will display "X", and cell A1 value will increment by +1. Click the button again and the Caption will be deleted and cell A1 value will decrement by -1.



Repeat this process for all command buttons, modifying the values to be added/subtracted per the column headers. Name each button similarly to the naming convention used above. Change the four "A1" references to the cell reference that will contain the total value.

Answer 2

I believe the answer from Robert is correct regarding a button's ability to be pressed or depressed however you could create a shape that is similar to function to a button and give it the appearance of being pressed/depressed:

1. Create a textbox, change text and appearance to however you want (fill colour, borders, text alignment, etc.) but no special effects.

2. Right-click on your shape and select Run macro

3. In the macro dialog box select New

4. Enter the following code within the created macro:



Dim myShape As Shape

Set myShape = ActiveSheet.Shapes(Application.Caller)

If myShape.Shadow.Visible = msoFalse Then

myShape.Shadow.Visible = msoTrue

myShape.IncrementLeft (-3)

myShape.IncrementTop (-3)

Range("A1") = 0

Else

myShape.Shadow.Visible = msoFalse

myShape.IncrementLeft (3)

myShape.IncrementTop (3)

Range("A1") = 1

End If



5. Close the VB editor.

6. Now click the shape/button you created and it will change the value of A1 as well as give it a pressed/depressed look

Answer 3

Add the following code to your button;



Private Sub CommandButton1_Click()

Range("A1") = 1

End Sub



Where "A1" is the cell you want the 1 value. Make another button to change it to 0 as the value. A button cannot stay pressed. It is clicked. Therefore, another button needs to be created. Your other code for the second button should look like the following;



Private Sub CommandButton2_Click()

Range("A1") = 0

End Sub



Hope this helps...