Hello,

I have a Worksheet in a Book that Im moving to another Workbook (€œWKB2€�).
Im looking to create a Macro which looks to see if €œWKB2€� is closed. If
its closed, I would like to insert the sheet from the first Workbook
(€œWKB1€�).

Since I only want to have it perform the action if the other workbook is
closed, is there also a way to create a message to alert the user in the
event the workbook is open - - - meaning don't move it in that case?

Thanks for your review €“ Roger

This is a function a friend helped me to create when I had a similar issue:
you will need to insert "Part 1" in your macro and use "Part 2" to create a
"public function" (similar to a new macro) in your module.

"Part 1" - put the following in your macro:

If WorkbookIsOpen("WORKBOOK NAME") < True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

"Part 2" - just copy and past this code in your module after your macro
(after the "End Sub"

Public Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open

Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If

End Function

What this will do is give "TRUE" if the workbook you name (in the IF
statement above) is open. Of course you can use the "IF...ELSE...END IF" to
give you a warning message should the workbook be open.

"Roger" wrote:

Hello,

I have a Worksheet in a Book that Im moving to another Workbook (€œWKB2€�).
Im looking to create a Macro which looks to see if €œWKB2€� is closed. If
its closed, I would like to insert the sheet from the first Workbook
(€œWKB1€�).

Since I only want to have it perform the action if the other workbook is
closed, is there also a way to create a message to alert the user in the
event the workbook is open - - - meaning don't move it in that case?

Thanks for your review €“ Roger

Just so there is no confusion, your IF... Then statement should look as
follows:

If WorkbookIsOpen("WKB2") < True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

Hope this helps.


"Jeff" wrote:

This is a function a friend helped me to create when I had a similar issue:
you will need to insert "Part 1" in your macro and use "Part 2" to create a
"public function" (similar to a new macro) in your module.

"Part 1" - put the following in your macro:

If WorkbookIsOpen("WORKBOOK NAME") < True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

"Part 2" - just copy and past this code in your module after your macro
(after the "End Sub"

Public Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open

Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If

End Function

What this will do is give "TRUE" if the workbook you name (in the IF
statement above) is open. Of course you can use the "IF...ELSE...END IF" to
give you a warning message should the workbook be open.

"Roger" wrote:

Hello,

I have a Worksheet in a Book that Im moving to another Workbook (€œWKB2€�).
Im looking to create a Macro which looks to see if €œWKB2€� is closed. If
its closed, I would like to insert the sheet from the first Workbook
(€œWKB1€�).

Since I only want to have it perform the action if the other workbook is
closed, is there also a way to create a message to alert the user in the
event the workbook is open - - - meaning don't move it in that case?

Thanks for your review €“ Roger

Thank you Jeff. This helps out immensely and gives me a good direction to
start from.

Appreciate you help - Roger




"Jeff" wrote:

Just so there is no confusion, your IF... Then statement should look as
follows:

If WorkbookIsOpen("WKB2") < True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

Hope this helps.


"Jeff" wrote:

This is a function a friend helped me to create when I had a similar issue:
you will need to insert "Part 1" in your macro and use "Part 2" to create a
"public function" (similar to a new macro) in your module.

"Part 1" - put the following in your macro:

If WorkbookIsOpen("WORKBOOK NAME") < True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

"Part 2" - just copy and past this code in your module after your macro
(after the "End Sub"

Public Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open

Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If

End Function

What this will do is give "TRUE" if the workbook you name (in the IF
statement above) is open. Of course you can use the "IF...ELSE...END IF" to
give you a warning message should the workbook be open.

"Roger" wrote:

Hello,

I have a Worksheet in a Book that Im moving to another Workbook (€œWKB2€�).
Im looking to create a Macro which looks to see if €œWKB2€� is closed. If
its closed, I would like to insert the sheet from the first Workbook
(€œWKB1€�).

Since I only want to have it perform the action if the other workbook is
closed, is there also a way to create a message to alert the user in the
event the workbook is open - - - meaning don't move it in that case?

Thanks for your review €“ Roger