Chart Window Question

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
I recently added an option to chart some of my data and display the graph on a userform.

Here is the problem I am running into:

I click an option button on Userform1 to show the chart
Userform2 opens, Userform1 closes
I click an option button on Userform2 to select which set of data to use
The chart window appears in the background
Userform3 appears with the picture of the chart on it, but Userform2 is still visible (unwanted)
I click a command button on Userform3 to go back to the Userform1 (report menu)
The chart window, Userform2, Userform3, and any subsequent userforms stay visible on the screen, despite unload commands in the code.

How do I get the chart window to close? This is just a cosmetic problem, but I can't figure out how to get it to work properly.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
For each userform that loads another one, the first one will not be unloaded until the called form is, unless you are showing the forms modelessly. Essentially what you need for each form is:
Code:
Me.Hide
otherform.show ' here, control passes to the other userform and this code pauses
unload me ' or Me.Show depending on what you need
that way, as each form is hidden or unloaded, conrtol automatically passes back to the form that called it.
You can use:
Code:
Activechart.showwindow = false
to hide the chart window.
 
Upvote 0
Not true. The code works fine in all other areas of the workbook.

I use the command 'Unload UserformX' to close the unwanted forms before proceeding.

Only when the chart is displayed do I have any problems.

Here is the code to return to the previous form:

Code:
Private Sub CommandButton1_Click()
Unload Display_Chart
Reports_Menu.Show
End Sub

Display_Chart does not unload, and Reports_Menu shows on top of it.
 
Upvote 0
I thought I had already tried this, but just to confirm, here is the code modification I made after your suggestion:

Code:
Private Sub CommandButton1_Click()
ActiveChart.ShowWindow = False
Unload Display_Chart
Reports_Menu.Show
End Sub

This gave me an error message 'Method ShowWindow of Object Chart failed'.
 
Upvote 0
I'm not sure what you are suggesting is not true. If you have a form that calls another form like this:
Code:
sub commandbutton1_click()
Unload me
otherform.show
end sub
that sub does not actually end until the otherform has been closed. You can check that by stepping through the code, or adding a msgbox line or debug.print line before the end sub and seeing when it executes.
As far as the chart window goes, how are you showing it?
 
Upvote 0
Sorry, by 'not true' I simply meant that the problem shouldn't be with one form calling another. In all other applications where I've used the previous code sequence, the first form closes before the second form opens (or at least visually it does). It may still be technically paused, but for all intensive purposes, it is closed.

The actual chart is on a sheet called "Chart", which is an xlveryhidden sheet. Here is all of the code associated with the userform that displays the chart:

Code:
Private Sub CommandButton1_Click()

Unload Display_Chart
Reports_Menu.Show

End Sub

Code:
Private Sub CommandButton2_Click()

CommandButton1.Visible = False
CommandButton2.Visible = False
Display_Chart.PrintForm
CommandButton1.Visible = True
CommandButton2.Visible = True

End Sub

Code:
Private Sub UserForm_Initialize()

Set CurrentChart = Sheets("Chart").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\QA_AR_CHART.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"
Image1.Picture = LoadPicture(Fname)

End Sub

Code:
Private Sub UserForm_Terminate()

Kill ThisWorkbook.Path & "\QA_AR_CHART.gif"

End Sub
 
Last edited:
Upvote 0
Where does the chart window come in? Have you turned off screenupdating?
Re the forms, you do need to be wary of creating a loop if the third form loads the first again, especially if you have events that fire when the forms close. May not be an issue in this instance, but it's something to be aware of.
 
Upvote 0
I hadn't changed screenupdating previously, but I decided to combine that with the visibility of the veryhidden sheet where the chart is located.

I turned off screenupdating, changed the 'Chart' sheet to visible, exported the picture, loaded the picture onto the userform, changed the sheet back to veryhidden, then turned screenupdating back on.

It seems to work as I want it to now.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top