How do I pass a textbox on a spreadsheet to a function

[ucchris77]

Hi Guys,

Basically what I want to be able to do is to pass a textbox from an excel spreadsheet to a function and assign the text and font color of that textbox. I have this textbox on top of a chart to mark whether a goal was met based on chart data.

Here's what I am trying to pass to the function:

Please Login or Register  to view this content.

I'm getting a compile error: "Expected: ="

Here's the function that I'm using:

Please Login or Register  to view this content.

Thanks for your help.

[Andy Pope]

Are you are trying to use this in a worksheet cell?

Please Login or Register  to view this content.

[snb]

The way I would do it:

Please Login or Register  to view this content.

By the way: a Sub is a macro, not a function.

[ucchris77]

No I'm using a VBA macro. This function is part of larger macro which is run automatically when the workbook is opened.

[ucchris77]

Ah I made a mistake, you're right a sub isn't a function. I'm new to VBA.

Thanks for your help snb that looks like it would work, but what I want to do is pass the textbox as a parameter into this subroutine. Is there no way to do that? I'm going for an object oriented approach.

NOTE: Also this textbox is merely for displaying the letter it accepts input from the chart not the user. I actually wish there was something better to use than a textbox.

[Andy Pope]

Either add a Call or drop the brackets

Please Login or Register  to view this content.

[ucchris77]

Thanks, Andy that works perfectly.