Excel VBA

[kit_sune]

I wasn't sure if I should have added this here, or in the Excel section. I'm trying to do this in Excel, so here goes.


In my spreadsheet I'm using VBA to modify the contents of a pivot table. It works really well, actually. However, I'm also trying to include three radio/option buttons that would further filter down the table to only include the data that matches the button specified.

I have the following three (Form, not ActiveX) buttons: "WeeklyData", "MonthlyData", and "NoDateFilter".


I created a small sub to check if my buttons were working (because they didn't seem to be) and I discovered that each of the three buttons values are set to "False", even though I can clearly see that I've got one of them selected.


If I do the below vba I get "False False False" as a message, no matter which button I have selected.

I'e tried various different setups, but all others gave me errors, usually "Object Required."

I've tried adding .Value to the end of the button names. I've tried adding "Form." to the front. I've tried both! I've tried "ActiveWorkSheet."

I searched google to try and find an answer and none seemed to work. I just don't know anymore!


Please take a look, it's probably something simple.

Sub Test()
Dim WValue As Boolean
Dim MValue As Boolean
Dim NoValue As Boolean
NoValue = NoDateButton
WValue = WeeklyButton
MValue = MonthlyButton
MsgBox WValue & " " & MValue & " " & NoValue
End Sub

Also real quick, if I simply do this:

    MsgBox NoDateButton & " " & WeeklyButton & " " & MonthlyButton

I get a message box with nothing in it. If I add ".Value" to each of these I get an error "Object Required"


Does it matter that I'm using form buttons rather than ActiveX?

Thanks for the help.

I'm hoping that once I get past this issue I can move on with the function I'm writing.

Thanks again.
~Kit

 

[kevlray]

Just a couple of questions to clarify what you are doing. What version of Excel are you using? Are these buttons on a form or just on the spreadsheet? Are these buttons Option buttons or just a button? Do you have a macro attached to the buttons themselves?

I may not have all the answers (it has been a while since I did any code in Excel).

 

[kit_sune]

Version 2010
The radio/option buttons are just sitting on the spreadsheet. I moved them into some cells near the top. They are the ones that look like dots that become filled when you click them, but become unselected when you choose another.
There's no macro on them. The plan is to refer to their status when another button is pressed to help determine what information needs to be processed. Thanks for taking a look.

~Kit

 

[kevlray]

Normally a button of that sort would rely on a event once it selected. You could create some code to capture the value (in a global variable) in the buttons select event (not sure if that is the correct event), thus the value would be available 'outside' of the button's scope (where it would not have a value).

I hope this helps.

 

[Mihail]

Does it matter that I'm using form buttons rather than ActiveX?

Seems to matter.
From Excel help I understand that a Form button should be linked to a cell, unlike an ActiveX one that act on itself.