Solved Close workbook after clicking a shape

Microsoft Excel 2010 - complete product...
April 27, 2015 at 07:12:39
Specs: Windows 10
Hi,

I have a Flowchart Shape that has a hyperlink to a certain sheet on another workbook. I would like to close the workbook after the shape has been clicked. I have the following code but it is not working?

Sub Close_Workbook()

Workbooks("VSM Training.xlsm").Close savechanges:=False

End Sub

Can this be done?

Thanks
Sandi


See More: Close workbook after clicking a shape

Report •

#1
April 27, 2015 at 13:00:03
What does "it is not working" mean?

It runs but it's not closing the workbook? It's not even running when you click the shape? It's throwing up an error? Something else?

Since we can't see your workbook from where we're sitting, we need a little more information before we can suggest a solution.

The only thing I can say is that there is nothing wrong with the code itself because it works fine for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 28, 2015 at 07:06:32
Hi,

It wasn't working at all it would open the hyperlink but not close the file. I did however come across a work around. I changed to the code below and had to change my shape to a command button for some reason for it to work.

Sub Open_Sign_Up_Sheet()

    ChDir "G:\Operations Excellence\Misc\Training"
    Workbooks.Open Filename:= _
        "G:\Operations Excellence\Misc\Training\Training Sign Up Sheet.xlsm"
    Sheets("Value Stream Mapping").Select
    Windows("VSM Training.xlsm").Activate
    ActiveWorkbook.Close SaveChanges:=False

End Sub

Thank you,
Sani


Report •

#3
April 28, 2015 at 08:21:26
✔ Best Answer
I'm glad you found a solution.

I did some more testing and it seems that assigning a hyperlink and a macro to the same shape either doesn't work at all or results in intermittant behavior.

For what it's worth, I was able to assign the following macro to a shape (not a Command Button) and it seems to work fine.

No need to ChDir if you don't want to. If you do, Excel will retain that path so that the next time you use File...Open, that is the directory you will see. You may not want that to be where you land. Since you included the full path name in the Workbooks.Open instruction, you shouldn't have to change directories first.

In addition, you shouldn't have to activate VSM Training.xlsm just to close it. You should be able to refer to it directly. Since the code is running in that workbook, you don't need to tell Excel to activate it.

Sub Open_Sign_Up_Sheet()
'Open Sign Up Workbook
    Workbooks.Open Filename:= _
        "G:\Operations Excellence\Misc\Training\Training Sign Up Sheet.xlsm"
'Activate Desired Sheet
    Sheets("Value Stream Mapping").Activate
'Close VSM Training
    Workbooks("VSM Training.xlsm").Close SaveChanges:=False
End Sub


Finally, I don't know if this will fit your needs, but you can eliminate the Sheets("Value Stream Mapping").Activate instruction in the VSM macro and place this code in the ThisWorkbook module of Training Sign Up Sheet:

Private Sub Workbook_Open()
'Activate Desired Sheet
 Sheets("Value Stream Mapping").Activate
End Sub

With that code in the ThisWorkbook module, Value Stream Mapping will become the ActiveSheet everytime the workbook is opened, regardless of whether the workbook is opened via code or manually.

I hope something here helps...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 28, 2015 at 09:20:31
Thank you that was very informative and your code is much cleaner. I was wondering what the ChDir meant :)

Thank you very much!!
Sandi


Report •

#5
April 28, 2015 at 09:24:06
I'm glad I could help. If you are going to be playing with VBA, you might want to review this tutorial:

http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Report •

Ask Question