Excel - Creating a triggered VBA to open/close a workbook 1

[beanxx]

How do I create a menu option or commandbar option triggered VBA script to automatically close and re-open a spreadsheet in order to pick up changes made on this spreadsheet by a second user.

 

[xlbo]

ok - this is gonna sound complicated but it isn't

Easiest way to do this is to create an "add-in"

To do this, on the computer which will be used to DISPLAY the data only, create a blank excel workbook
Go to the VBE (ALT + F11)

Insert a new module (Insert>Module)

paste the following into the module

Sub CreateCustomMenu()
Set myMenuBar = CommandBars.ActiveMenuBar
Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, _
    Temporary:=True)
newMenu.Caption = "Update"

Set ctrl1 = newMenu.Controls.Add(Type:=msoControlButton, ID:=1)
With ctrl1
    .Caption = "Refresh Data"
    .TooltipText = "Refresh Data"
    .Style = msoButtonCaption
    .OnAction = "getdata"
End With
End Sub

Sub getdata()
Dim strPath As String, strName As String

strPath = ActiveWorkbook.Path
strName = ActiveWorkbook.Name

Application.ScreenUpdating = False

ActiveWorkbook.Close (False)
Workbooks.Open (strPath & "\" & strName)

End Sub

Also, go to the WORKBOOK MODULE and create a workbook_OPEN event (choose "Workbook" from the right side dropdown and it will be automatically created)

In there, enter simply:

CreateCustomMenu

Once you have done this, go back to excel and do File>Save As

In the "type of file" options, there is "Add-in" with an extension of ".xla"
Choose this one
The directory will jump to the default add-ins directory
Give it a name and save it

Close the workbook

create a new blank workbook and go Tools>Add-ins
Check th etickbox next to your add-in's name - it will now be loaded whenever excel is opened on this machine

The code in the workbook_OPEN event will fire when excel is opened and create the custom menu. whenever you want to refresh the data, the code notes the path and name of the currently open workbook, closes it and then re-opens it - the "application.screenupdating" line ensures there is no flicker visible to the users.


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[beanxx]

Wow cheers Geoff! just stuck on the following bit....
Also, go to the WORKBOOK MODULE and create a workbook_OPEN event (choose "Workbook" from the right side dropdown and it will be automatically created......on the right side the only drop down references "CreateCustomMenu" and "getdata".

 

[xlbo]

Sorry - choose "workbook" from teh left side dropdown - you will need to doubleclick on the "ThisWorkbook" object in the properties window to get to the workbook module however - it will not be on the standard module that you have inserted

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[beanxx]

Yes I got it working that is fantastic. Thanks for all your help...Star duly awarded!!!

 

[xlbo]

my pleasure

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question