PDA

View Full Version : [SOLVED] Excel Optionbutton Calculations



Ladyj205
04-18-2018, 11:52 AM
:hi:
I did this before for word. All I'm trying to do is get the optionbuttons to calculate.

i'm using ms excel 2010. I have 4 optionbuttons for each section. I'm trying to get the Score = selected value * 10 for 2 sections. i need to get the sum of 2 results for grandtotal for both and scores.
im NOT having any success at it. i wont mind if someone use another number besides 10 for examples and steps.:crying:: pray2:


Private Sub OptionButton_1_0_Click()
If OptionButton_1_0 Then
Selection.Range(A6:A9).(B9)Range.Text = "0"
End If
End Sub

Private Sub OptionButton_1_1_Click()
If OptionButton_1_1 Then
Selection.Range(A6:A9).(B8)Range.Text = "10"
End If
End Sub

Private Sub OptionButton_1_2_Click()
If OptionButton_1_2 Then
Selection.Range(A6:A9).(B7)Range.Text = "20"
End If
End Sub

Private Sub OptionButton_1_3_Click()
If OptionButton_1_3 Then
Selection.Range(A6:A9).(B6)Range.Text = "30"
End If
End Sub


Private Sub OptionButton_1_0_Click()
If OptionButton_2_0 Then
Selection.Range(A11:A14).(B14)Range.Text = "0"
End If
End Sub

Private Sub OptionButton_1_1_Click()
If OptionButton_2_1 Then
Selection.Range(A11:A14).(B13)Range.Text = "10"
End If
End Sub

Private Sub OptionButton_1_2_Click()
If OptionButton_2_2 Then
Selection.Range(A11:A14).(B12)Range.Text = "20"
End If
End Sub

Private Sub OptionButton_1_3_Click()
If OptionButton_2_3 Then
Selection.Range(A11:A14).(B11)Range.Text = "30"
End If
End Sub

SamT
04-18-2018, 12:54 PM
I suspect this is a Word code structure
Selection.Range(A6:A9).(B9)Range.Text

In Excel, for Range B9, use:
ActiveSheet.Range("B9") = 10

The Use of "ActiveSheet" is discouraged, since the Active sheet can easily change. You can use Sheets("SheetName") to specify the sheet, or you can Set a variable to the sheet and use the Variable

Sheets("SheetName").Range("B9") = 10

Dim Sht as Worksheet 'Or as Object
Set Sht = ActiveSheet
'Or
Set Sht = Sheets("SheetName")

Sht.Range("B9") = 10

For code that is on/in that Sheet's Code Page, you do not have to specify the Range's Parent sheet
Range("B9") = 10

Note that Range Addresses are Strings and must be inside doublequote marks. Cells addresses are two numbers Cells(9, 2) = 10 or a Row number and a Column Letter(s) Cells(9, "B") = 10

Cells are used when convenient, and, are the best way to use cells in a Loop
Dim i As Long
For i = 1 to 10
Cells(1, i + 1) = i
Cells( i + 1, "A") = i * 10
Next

Cells are most often use when finding the last used Cell in a Row or Column
Dim LR As Long 'For Last row
LR = Cells(Rows.Count, "B").End(xlUp).Row

Dim NR As Long 'For Next Row
NR = Cells(Rows.Count, "B").End(xlUp).Row + 1

Dim LC As Long 'For Last Column
LC = Cells(1, Columns.Count).End(xlToLeft).Column
"End" is like the Keyborad Arrow keys,with the Parameter being the direction (XlUp), (xlDown), (xlToLeft), and (xlToRight)

Note: always Declare Row and Column number Variables as Longs.

Paul_Hossler
04-18-2018, 01:00 PM
1. Didn't understand the requirement in the Word forum either

2. Here's an example workbook using Excel concepts

If I understand rightly ... There are two groups of 4 option buttons, with only a single OB selectable in each group. Each OB selects a number and puts it in a cell

I used Form Controls (not ActiveX) to put 2 Frames and 4 OB in each frame.

Each OB has a macro




Option Explicit
Sub OB1_0_Click()
Range("A6").Value = 0
End Sub
Sub OB1_1_Click()
Range("A6").Value = 10
End Sub
Sub OB1_2_Click()
Range("A6").Value = 20
End Sub
Sub OB1_3_Click()
Range("A6").Value = 30
End Sub
Sub OB2_0_Click()
Range("A11").Value = 0
End Sub
Sub OB2_1_Click()
Range("A11").Value = 10
End Sub
Sub OB2_2_Click()
Range("A11").Value = 20
End Sub
Sub OB2_3_Click()
Range("A11").Value = 30
End Sub




Maybe this example will help

Ladyj205
04-18-2018, 01:13 PM
1. Didn't understand the requirement in the Word forum either

2. Here's an example workbook using Excel concepts

If I understand rightly ... There are two groups of 4 option buttons, with only a single OB selectable in each group. Each OB selects a number and puts it in a cell

I used Form Controls (not ActiveX) to put 2 Frames and 4 OB in each frame.

Each OB has a macro




Option Explicit
Sub OB1_0_Click()
Range("A6").Value = 0
End Sub
Sub OB1_1_Click()
Range("A6").Value = 10
End Sub
Sub OB1_2_Click()
Range("A6").Value = 20
End Sub
Sub OB1_3_Click()
Range("A6").Value = 30
End Sub
Sub OB2_0_Click()
Range("A11").Value = 0
End Sub
Sub OB2_1_Click()
Range("A11").Value = 10
End Sub
Sub OB2_2_Click()
Range("A11").Value = 20
End Sub
Sub OB2_3_Click()
Range("A11").Value = 30
End Sub




Maybe this example will help


you on the money Paul. :yes

everything got to be in the order in the attachment i had originally posted like this. where the 10s just still there and look good lol. just got the have to scores at the top and the grandtotal at the bottom

Ladyj205
04-18-2018, 02:06 PM
NEVERMIND I GOT IT!!!!!!!! THANKS