VBA Custom Function - Form Control Name Object not Supported?

[bs0d]

I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.

The error is:

Object doesn't support this property or method.

Here is my function:

Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long)

Sheets(tabName).ChartObjects(chartName).Activate
    
If Sheets(tabName).checkboxName.Value = True Then
    
    ActiveChart.FullSeriesCollection(seriesNumber).Select
    With Selection.Format.Line
        .Visible = msoTrue
    End With
Else
    ActiveChart.FullSeriesCollection(seriesNumber).Select
    With Selection.Format.Line
        .Visible = msoFalse
    End With

End If

End Function

The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".

How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.

Thanks

 

[Andrew Poulsom]

Where is your Checkbox from, ActiveX or Form Controls? You can't use a string as the explicit name of an object.

 

[Norie]

What type of checkbox do you want to pass to the function?

 

[Domenic]

Try...

If Sheets(tabName).CheckBoxes(checkboxName).Value = True Then

Hope this helps!

 

[spoon_db]

You declare it as a string.

 

[bs0d]

Where is your Checkbox from, ActiveX or Form Controls? You can't use a string as the explicit name of an object.

Sorry all, it's an ActiveX checkbox.

Domenic, I tried your code with no luck, perhaps because it's ActiveX

edit
Ok, I got it to work following this example: http://www.mrexcel.com/forum/excel-...rols-variables-visual-basic-applications.html

Thanks for the input guys.

 

[Andrew Poulsom]

Then it's:

If Sheets(tabName).OLEObjects(checkboxName).Object.Value = True Then

 

[Domenic]

Sorry all, it's an ActiveX checkbox.

Domenic, I tried your code with no luck, perhaps because it's ActiveX

I think you mentioned "form control" in your post, hence my code. For an ActiveX checkbox, try...

If Sheets(tabName).OLEObjects(checkboxName).Object.Value = True Then

Hope this helps!

 

[Norie]

Why not pass the checkbox itself to the function?

 

[bs0d]

Why not pass the checkbox itself to the function?

Here is the working code:

Public Function chkClick(checkboxName As OLEObject, tabName As String, chartName As String, seriesNumber As Long)

Sheets(tabName).ChartObjects(chartName).Activate
    
If checkboxName.Object.Value = True Then
    
    ActiveChart.FullSeriesCollection(seriesNumber).Select
    With Selection.Format.Line
        .Visible = msoTrue
    End With
Else
    ActiveChart.FullSeriesCollection(seriesNumber).Select
    With Selection.Format.Line
        .Visible = msoFalse
    End With

End If

End Function

function call:

chkClick Sheets("Sheet 1").OLEObjects("chkTest"), "Sheet 1", "myChart", 1

Norie - Is this solution what you were referring to?

Domenic, sorry about the name reference as a form control. I was thinking of it as a form control and wasn't specific in the initial post that it was ActiveX.

Thanks Mr. Poulsom, that's the code I used along with defining the function parameter as an OLEObject.