Hi guys,
This is probably a question of vocab - I didn't know the right terms to
search for, so my apologies if this has been answered.

I need to prompt a user to select a chart and then set that chart to a
variable in my code. The trick is that the chart will be in a
different workbook; is there a way to pop up a message, allow the user
to activate a different workbook and then return to the message a click
OK? I don't know if msgbox or inputbox would allow this.

The general schematic is as follows:

Dim ch as Chart
dim ans as Integer
On Error Resume Next
ans = MsgBox("Please Select a Chart to Modify", Buttons:=vbOKCancel)
'this is where I need to allow the user to go activate a chart
'in another workbook, and then come back and click OK.
If ans = vbOK Then
Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "no chart selected"
Else
'chart okay; more code here
End If
Else
'user canceled out
End If

Thanks in advance for your help,
Andrew

" wrote:
I need to prompt a user to select a chart and then set that chart to a
variable in my code. The trick is that the chart will be in a
different workbook; is there a way to pop up a message, allow the user
to activate a different workbook and then return to the message a click
OK? I don't know if msgbox or inputbox would allow this.

MsgBoxes are modal, which means that you can't do other things while one is
on screen. The only options for modality for a MsgBox are Application modal,
which means it stops only Excel, and System modal, which means that the
MsgBox stays live and halts all actions even if you switch applications.
Neither of these helps you.

A UserForm will do what you want. UserForms can be displayed non-modally.
In the VBE, Insert a UserForm in the VBA project for your starting workbook.
Then double click on the form in the Project Manager to display the form
designer. Hit F4 to see the form's properties. Look for "ShowModal" in the
list of properties and set it to False. (Double click on True to change it
to False.)

Drag a button from the Toolbox palette and place it on the form. Double
click the button to see the code for its click event. Enter something like

Debug.Print ActiveWorkbook.Name & " " & ActiveChart.Name
Unload Me

into the event handler.

Go back to the form designer and hit F5 to run the form. The UserForm will
stay on top of the other windows, but you should be able to move it around to
get at and click on charts and whatnot on all the open workbooks in Excel.
You should even be able to make selections from the menu and open new
workbooks.

When you click on the button, the code will write the name of the new
ActiveWorkbook and the ActiveChart in the Immediate Window (assuming you've
actually selected a chart.) Otherwise, the code outline you posted would
work.