Can you change a Chart title via VBA (1 Viewer)

Cliff67 · Dec 6, 2018

Hi Guys

Got a problem that I can't seem to solve. I don't use charts/graphs very often but on my latest build I'm taking support tickets and counting by month and year. The year of interest is selected on a form using a combo box. Some queries then sort the data to what I require.

what I'm trying to do now is add the selected combo box to the title bar on the chart programmatically. I just can't get the referencing correct for this.

The title needs to read
"Technical Support Tickets Opened by Month for " insert combo box reference
I just can't get the me.objectname bit right

any help would be appreciated

Cliff

isladogs · Dec 6, 2018

Are you trying to use the bound column of your combo?
If not, which column is it remembering that the numbering starts at 0?
What datatype -number or text? If its a year I assume number?
Finally may as well ask, what is the combobox name

Another method is to assign a variable e.g.
Dim intYear as Integer
IntYear = me.comboname

Then "....owned by month for " & intYear

Cliff67 · Dec 6, 2018

Hi Isla
The combo box us unbound on the form but uses a query to populate it, it only has 1 column and it is a number
the combo box name is CmbYear

this is the code for it

Private Sub Report_Open(Cancel As Integer)
Dim Ch As Chart

Printer.Orientation = acPRORLandscape
Printer.PaperSize = acPRPSA4
Me.Caption = "Technical Support Tickets by Month for " & Forms!frm_Reports!CmbYear
Ch.ChartTitle.Caption = "Technical Support Tickets by Month for " & Forms!frm_Reports!CmbYear

End Sub

if I use
Me.Graph0.ChartTitle.Caption = "Technical Support Tickets by Month for " & Forms!frm_Reports!CmbYear
then I get an error 438 - Object doesn't support the property or method

I would have thought this would have worked

Thanks

Cliff67 · Dec 6, 2018

Also using the ch.chartTitle.caption...

throw an error 91 - Object variable or with block variable not set

I really don't understand that one

isladogs · Dec 6, 2018

Can't check at the moment as about to log off.
Try the variable approach
If it still doesn't like that, create a function GetYear, set it equal to intYear and use GetYear() in your expression

Hang on....
Don't set a caption in the report_open event
Normally do so using the on format event for the section containing the caption

Good luck

Cliff67 · Dec 6, 2018

Feel such a numbnuts...
Set the
Me.Graph0.ChartTitle.Caption = "Technical Support Tickets by Month for " & Forms!frm_Reports!CmbYear

in the Detail_Format section and it works a treat

many thanks for your help, I knew there was something wrong but couldn't see the forest for the trees

isladogs · Dec 6, 2018

Perfect! Glad to have helped

Can you change a Chart title via VBA (1 Viewer)

Cliff67

Registered User.

isladogs

MVP / VIP

Cliff67

Registered User.

Cliff67

Registered User.

isladogs

MVP / VIP

Cliff67

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread