Auto_Open Macro and Dropbox

[Zunit]

Hi,

I have some files saved in a Dropbox folder that is shared between two people. I want to set up an excel file on the dropbox to open another excel file when I open the first one.

But how to I do this using a macro? Below is the macro I use currently, but the problem is the user name in the link. When the other user opens the file the dropbox link is different.

Macro is below:

Sub Auto_Open()
'
'
    Workbooks.Open Filename:="C:\Users\User1\Dropbox\Fm Services\BMS\[BMS.xlsx]"
    ThisWorkbook.Activate
    
    
End Sub

The problem is User1. When User2 opens the file on his computer (via Dropbox) the macro doesn't work since it doesn't find the link.

Is there a way of linking the 'User1' part of the above to the name of the User of the computer that the file is on? Or some other way of getting around this?

Thanks for the asssitance.

 

[BarryL]

only thing I can think of is maybe an input box? depends how many users there are. if theres only two you wouldn't need an input box but if its a department of 50 then it would probably be easier.

try the below anyway. untested.

Sub Auto_Open()
mybox= inputbox  ("What is the user name?")

Workbooks.Open Filename:="C:\Users\" & mybox & "\Dropbox\Fm Services\BMS\[BMS.xlsx]"
    ThisWorkbook.Activate
    
    
End Sub

 

[Zunit]

Worked brillantly! Thanks a lot!