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

 

[Norie]

bs0d

Yes, that's what I was thinking.

I also tried passing the actual chart/chartobject but couldn't get that to work.

 

[Domenic]

Maybe...

Public Function chkClick(oOleoObj As OLEObject, oChart As Chart, seriesNumber As Long)

    If TypeName(oOleoObj.Object) = "CheckBox" Then
        If oOleoObj.Object.Value = True Then
            oChart.FullSeriesCollection(seriesNumber).Format.Line.Visible = msoTrue
        Else
            oChart.FullSeriesCollection(seriesNumber).Format.Line.Visible = msoFalse
        End If
    End If

End Function

Function call:

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

 

[bs0d]

I'll have to try that, Domenic. Any chance you tested that? It'd be great to avoid the selection code. I was previously having some issues with avoiding a selection relating to charts (http://www.mrexcel.com/forum/excel-...chart-fullseriescollection-optimize-code.html)

 

[Domenic]

Yes, I tested it...

 

[Norie]

Domenic

I tried it in a similar way but couldn't get it to work, probably because I know next to nothing about manipulating charts in code.

Actually, I've just had another look at your code and I think I see where I was going wrong - never thought of using the Visible property.

 

[bs0d]

Yes, I tested it...

I confirmed it works for my application, thanks for the optimized code! This eliminates a nanosecond "flash" of the chart caused from the previous selection code.

 

[Domenic]

That's great, thanks for the feedback!

Cheers!