Excel with VB Script - Option Buttons with If statement 1

[spitfire184]

Hello.

Not entirely sure this is the right forum to post this in, apologies if it isn't.

I'm in a situation in Excel whereby I wish to have questions with option buttons. Upon clicking a 'save' button, a VB script is executed which checks which option buttons are selected and adds a 1 to the value of a cell on another sheet.

Here is my code:

Sub Button2_Click()
If OptionButton1.Value = True Then
Sheet2.Cells(3, 2).Value = Sheet3.Cells(3, 2).Value + 1
End If
End Sub

I suppose it looks naive, I'm not very experienced, but I have searched extensively and tried other solutions.

The code produces this error:

Run-time error '424':
Object required

I would greatly appreciate any assistance leading to the resolution of this problem. Thanks.

 

[SkipVought]

Hi,

You realize that you could do this without any VB code, using FORMS Option Buttons, Grouped by row.

For each group of OBs, the first returns 1, the second, 2, etc, and can be linked to a cell.

So your formula can count all the 1's in the column containing the linked cells, using the COUNTIF function.

Pretty simple!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[spitfire184]

Hey. Thanks for your reply.

Yes, I could do something like that, I have considered something similar, but I don't think it can work because I need the user to be able to fill in the options, the result to be saved and then the options reset.

If I used your suggestion then surely as soon as the form was reset, the associated cells would clear and the cells using count would also loose their values.

There is a cell on the other sheet for each option button to store how many times it has been selected.

This would all be very easy to do with VB scripting if it weren't for this error. Someone humour me.

 

[HarleyQuinn]

What line do you get your error?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[spitfire184]

Sorry, I should have said.

If OptionButton1.Value = True Then

That is the line that is apparently in error.

 

[SkipVought]

Assuming that your button is on sheet1...

If Sheet1.Optionbuton1.value = True Then
[code]

Skip,
[sup][glasses]Don't let the [b]Diatribe[/b]...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[spitfire184]

Yes, it is sheet1.
When I try that, it gives:

Compile error:
Method or data member not found

It highlights '.OptionButton1' in blue, when I click ok, it highlights 'Sub Button2_Click()' in yellow.

 

[HarleyQuinn]

The option button on Sheet1 is called OptionButton1 isn't it?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[spitfire184]

Yes, well, 'Option Button 1'.

 

[HarleyQuinn]

If you take the space out it will work fine. Space in names of controls, object and variables etc. are generally a bad idea, if you have to seperate out words then underscores are prefered to spaces.

Also, to use a control in the way Skip's described, if you just type in Sheet1 followed by a . the intellisense should give you a list of available options (methods, properties, controls etc.) that you can use on the Sheet1 object (most objects behave in this way).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[spitfire184]

Success, sort of.

I always avoids spaces in names, I use Camel Case, but I could find no way of editing the name of the OBs, only their label.

The code works if I use an activeX control instead of a form control. I was sure I'd tried this but I was tired by then and must have gotten something wrong.

I've always appreciated intellisense, but it was sod all help in this case, unless you count the fact that it wasn't helping as some sort of hint, in which case it was helpful.

I've never done any of this in Excel before and clearly I just don't know how to use the form controls properly.

Thanks for everyone's help and patience.

 

[SkipVought]

"I could find no way of editing the name of the OBs, only their label."

Sub test()
    Dim shp As Shape, i As Integer
    i = 1
    For Each shp In ActiveSheet.Shapes
        With shp
            If .Name Like "Option*" Then
                shp.Name = "obn" & i
                i = i + 1
            End If
        End With
    Next
End Sub

Works for either FORM Option Button 1 or Control Toolbox OptionButton1

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}