VBA Refresh

[dannyok90]

Hi, All

Sorry to come to the board with a complete problem, I usually like to bring a sort of solution or at least a bit of code I'm working with. But this time I have nothing and I'm completely stumped

I have a worksheet that is full of links to another worksheet but one particular cell link requires the user to input data in another workbook for it to populate.

So on workbook 1 Sheet 1 I have 'Cost', but the cell is locked and the sheet is protected. I have a hyperlink 'Click here to update cost' that opens the second workbook where they can put the cost in the relevant cell X out of it with a save and return to workbook 1. but obviously now the user would have to close workbook 1 and reopen it for the 'cost' to have appeared.

is there a VBA that could refresh workbook 1 on closure of workbook 2? or something similar? I don't know I'm open to ideas.

Thanks Guys!

Dan

 

[mumps]

It sounds to me that you may want to use a Workbook_BeforeClose event. This is a macro that you place in the code module for ThisWorkbook and the macro runs automatically when that workbook is closed.

 

[dannyok90]

Cool , thanks for that! I’ll go and do some research on that event. It’s cobfysybg because workbook 2 is a separate entity so it’s understand how workbook 1 will know run that macro on closure of workbook 2..

 

[mumps]

If you want the macro to run when you close workbook2, just place the macro in the ThisWorkbook code module for workbook2. Good luck with it.

 

[dannyok90]

I didn’t realise the typos in that last message I was at the lights when I replied ? yeah I understand! it’s just how I’m going to reference the other workbook in the code?

 

[mumps]

Reference it by using its full name plus the extension and the sheet name. Both workbooks should be open. If you have to open any workbook, then the full path to its location has to be included as well. For example:

Workbooks("Test.xlsx").Sheets ("Sheet1")

 

[dannyok90]

Okay, this is where it gets confusing.. so workbook 1, there is multiple copies of it (all with different information) but the same layout. They are stored in files that are particular to a specific project. Worksheet 2 is a single spreadsheet in a single location. So effectively each project manager can open up their own workbook 1 and follow the link to the single workbook 2. Would I have to include the file paths for all the workbook 1’s in the workbook before close event in workbook 2?

I guess the simple solution would just be to have a refresh button in workbook one, but that’s too simple haha.

 

[mumps]

Have Workbook1 open. Click the link in WB1. This opens WB2 as you described. Enter the cost in WB2. With the macro in WB2, when you close WB2, the copying and pasting is done automatically. The cost should appear in WB1. You don't have to close WB1 and then re-open it. By the way, you could also do the same thing with a Before_Save event or with both a Before_Save event and a Before_Close event. IF you use both events, the copy/paste will be done when you save WB2 or close it. I hoe I have understood correctly.

 

[dannyok90]

Okay great, It sounds like you understand what I’m trying to do so I’ll give it a go at work tomorrow and see if it works. thanks for taking the time to reply by the way, I’ll give you a shout tomorrow and let you know if it’s worked.