Hey guys I have a menu on the menu bar at the top of excel
called "Reports". Its right after the menu item "Help" at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the workbook
called "Report3.xls".

However when I try to code this in the onaction part of my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last line of
my code (above).

I need to be able to hardcode this into the code. I do
not want to create a module and have this code in it and
then reference this code in the onaction. I need to have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine

Todd,

The OnAction property must be a macro name, not a VBA statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine" wrote in
message ...
Hey guys I have a menu on the menu bar at the top of excel
called "Reports". Its right after the menu item "Help" at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the workbook
called "Report3.xls".

However when I try to code this in the onaction part of my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last line of
my code (above).

I need to be able to hardcode this into the code. I do
not want to create a module and have this code in it and
then reference this code in the onaction. I need to have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine

What do I do if I do not want to refer to a macro or
another procedure? I need to have it hard coded.

Thanks
Todd Huttenstine



-----Original Message-----
Todd,

The OnAction property must be a macro name, not a VBA
statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine"
wrote in
message ...
Hey guys I have a menu on the menu bar at the top of
excel
called "Reports". Its right after the menu item "Help"
at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the
workbook
called "Report3.xls".

However when I try to code this in the onaction part of
my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last line
of
my code (above).

I need to be able to hardcode this into the code. I do
not want to create a module and have this code in it and
then reference this code in the onaction. I need to
have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine


.

You can't hard code a VBA statement in to the OnAction property.
Why don't you want it to refer to a macro?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Todd Huttenstine" wrote in
message ...
What do I do if I do not want to refer to a macro or
another procedure? I need to have it hard coded.

Thanks
Todd Huttenstine



-----Original Message-----
Todd,

The OnAction property must be a macro name, not a VBA
statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine"
wrote in
message ...
Hey guys I have a menu on the menu bar at the top of
excel
called "Reports". Its right after the menu item "Help"
at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the
workbook
called "Report3.xls".

However when I try to code this in the onaction part of
my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last line
of
my code (above).

I need to be able to hardcode this into the code. I do
not want to create a module and have this code in it and
then reference this code in the onaction. I need to
have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine


.

Because the workbook I need it to close is the workbook
that the code is in. In order for this onaction method to
work I will need to have 2 workbooks open. One workbook
will have to contain the code to close the workbook. This
means 2 workbooks will have to be open. I didnt want to
have to do this. The reason I wanted to hardcode the
closeworkbook code in the onaction statement is because
then I wouldnt have to have a 2nd workbook open.


-----Original Message-----
You can't hard code a VBA statement in to the OnAction
property.
Why don't you want it to refer to a macro?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Todd Huttenstine"
wrote in
message ...
What do I do if I do not want to refer to a macro or
another procedure? I need to have it hard coded.

Thanks
Todd Huttenstine



-----Original Message-----
Todd,

The OnAction property must be a macro name, not a VBA
statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine"

wrote in
message ...
Hey guys I have a menu on the menu bar at the top of
excel
called "Reports". Its right after the menu
item "Help"
at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item
called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the
workbook
called "Report3.xls".

However when I try to code this in the onaction part
of
my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last
line
of
my code (above).

I need to be able to hardcode this into the code. I
do
not want to create a module and have this code in it
and
then reference this code in the onaction. I need to
have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine


.



.

A Macro can be written that will close the workbook that contains the Macro.
I think some more information is needed.

Do you want the "Uninstal" menu item to ONLY close the workbook file
Report3.xls or do you want it to close the active workbook?

Do you need to save Report3.xls before closing it?

Does the workbook Report3.xls create and delete your Reports menu?

If the OnAction part of your Menu Item code is

.OnAction "CloseMe"

then there a number of ways to code the Macro "CloseMe"



' Closes Report3.xls without saving
Sub CloseMe()
Application.DisplayAlerts = False
Workbooks("Report3").Close
End Sub

' Closes Report3.xls with saving
Sub CloseMe()
Workbooks("Report3").Save
Workbooks("Report3").Close
End Sub

' Closes ActiveWorkbook without saving
Sub CloseMe()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub

' Closes ActiveWorkbook with saving
Sub CloseMe()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


As long as the BeforeClose event of the workbook being closed has code to
remove the menu, or calls a Macro that removes the menu, you should be fine.

-gk-

"Todd Huttenstine" wrote in message
...
Because the workbook I need it to close is the workbook
that the code is in. In order for this onaction method to
work I will need to have 2 workbooks open. One workbook
will have to contain the code to close the workbook. This
means 2 workbooks will have to be open. I didnt want to
have to do this. The reason I wanted to hardcode the
closeworkbook code in the onaction statement is because
then I wouldnt have to have a 2nd workbook open.


-----Original Message-----
You can't hard code a VBA statement in to the OnAction
property.
Why don't you want it to refer to a macro?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Todd Huttenstine"
wrote in
message ...
What do I do if I do not want to refer to a macro or
another procedure? I need to have it hard coded.

Thanks
Todd Huttenstine



-----Original Message-----
Todd,

The OnAction property must be a macro name, not a VBA
statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine"

wrote in
message ...
Hey guys I have a menu on the menu bar at the top of
excel
called "Reports". Its right after the menu
item "Help"
at
the top. When you click on Reports, a drop down menu
comes out and in that meru is an item
called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks("Report3").Close

When I click on Uninstall, I want it to close the
workbook
called "Report3.xls".

However when I try to code this in the onaction part
of
my
code, I get the error message: "Expected Function or
Variable" and it highlights the .Close on the last
line
of
my code (above).

I need to be able to hardcode this into the code. I
do
not want to create a module and have this code in it
and
then reference this code in the onaction. I need to
have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine

Thank you

Todd
-----Original Message-----
A Macro can be written that will close the workbook that
contains the Macro.
I think some more information is needed.

Do you want the "Uninstal" menu item to ONLY close the
workbook file
Report3.xls or do you want it to close the active
workbook?

Do you need to save Report3.xls before closing it?

Does the workbook Report3.xls create and delete your
Reports menu?

If the OnAction part of your Menu Item code is

.OnAction "CloseMe"

then there a number of ways to code the Macro "CloseMe"



' Closes Report3.xls without saving
Sub CloseMe()
Application.DisplayAlerts = False
Workbooks("Report3").Close
End Sub

' Closes Report3.xls with saving
Sub CloseMe()
Workbooks("Report3").Save
Workbooks("Report3").Close
End Sub

' Closes ActiveWorkbook without saving
Sub CloseMe()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub

' Closes ActiveWorkbook with saving
Sub CloseMe()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


As long as the BeforeClose event of the workbook being
closed has code to
remove the menu, or calls a Macro that removes the menu,
you should be fine.

-gk-

"Todd Huttenstine"
wrote in message
...
Because the workbook I need it to close is the workbook
that the code is in. In order for this onaction method
to
work I will need to have 2 workbooks open. One workbook
will have to contain the code to close the workbook.
This
means 2 workbooks will have to be open. I didnt want to
have to do this. The reason I wanted to hardcode the
closeworkbook code in the onaction statement is because
then I wouldnt have to have a 2nd workbook open.


-----Original Message-----
You can't hard code a VBA statement in to the OnAction
property.
Why don't you want it to refer to a macro?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Todd Huttenstine"

wrote in
message ...
What do I do if I do not want to refer to a macro or
another procedure? I need to have it hard coded.

Thanks
Todd Huttenstine



-----Original Message-----
Todd,

The OnAction property must be a macro name, not a
VBA
statement.
Thus, create a macro that closes the file

Sub CloseFile()
Workbooks("Report3.xls").Close
End Sub

and assign that macro to the OnAction property:

..OnAction = "CloseFile"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Todd Huttenstine"

wrote in
message news:9ee101c486eb$083c42a0
...
Hey guys I have a menu on the menu bar at the top
of
excel
called "Reports". Its right after the menu
item "Help"
at
the top. When you click on Reports, a drop down
menu
comes out and in that meru is an item
called "Uninstall"
Below is my code that creates it.

Here is my code...
With ctrlPopUp
Set ctrlButtonA = .Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButtonA.Caption = "Uninstall"
ctrlButtonA.Style = msoButtonCaption
End With
ctrlButtonA.OnAction = Workbooks
("Report3").Close

When I click on Uninstall, I want it to close the
workbook
called "Report3.xls".

However when I try to code this in the onaction
part
of
my
code, I get the error message: "Expected Function
or
Variable" and it highlights the .Close on the last
line
of
my code (above).

I need to be able to hardcode this into the
code. I
do
not want to create a module and have this code in
it
and
then reference this code in the onaction. I need
to
have
the close workbook "Report3.xls" hardcoded in the
Uninstall menu item click.

How do I do this?


Thank you
Todd Huttenstine


.