Refreshing My Spreadsheet?

[Ste_Moore01]

Hi,

I have a macro that finds the username of the person who opens my workbook and pastes it in a certain cell, and it runs automatically when the workbook is opened.

I also have another cell which changes depending on the username.

The problem is, when I open the spreadsheet the cells which change depending on the username haven't been updated. If I click into the cell and then press Enter it then updates it.

Is there anyway to automatically update these cells?

 

[Andrew Poulsom]

Does the offending cell contain a formula? Is Calculation set to Automatic?

 

[kkjensen]

I don't know about finding the username of the user but if you want a macro to run automatically when a file is opened you need to add the following code to the "This Workbook" sheet in the VBA editor:

Private Sub Workbook_Open()
'*********INSERT CODE HERE****************

'****************************************
End Sub

The same sort of thing works for running macros before closing (i.e. if you want to make sure that these updated cells get saved every time)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'*********INSERT CODE HERE****************
ThisWorkbook.Save   'forces a save upon close
ThisWorkbook.Saved = True   'makes excel think it's been
                                           'saved so it won't ask "Do 
                                           'you want to save?" at exit
'****************************************
End Sub

 

[Ste_Moore01]

Thanks kkjensen but my macros work perfectly.

Andrew, the offending cell contains a formula and I am unsure how to set Calculation to Automatic.

 

[kkjensen]

Automatic updates are in the tools-->options-->Calculation menu up at the top. You can also insert "Calculate" into your macro whereever you want it to recalculate everything.

Question for you:

You way you're grabbing the username and putting it somewhere...can you give me the bit of code that does that? I've got files that get updated by people all over the network and I want to have the files save with a "Last Saved by:" somewhere on the sheet.

 

[Ste_Moore01]

Just so I'm not confusing you, I'll explain a little more.

My macro finds the username and pastes it in cell B1 in a worksheet named "USER DETAILS".

Then in another worksheet (currently named Sheet1 as I haven't changed it yet) a formula in cell B6 looks up the data in "USER DETAILS" cell B1 and finds their full name in a table also in "USER DETAILS".

The only problem is, cell B6 in "Sheet1" the result of the formula doesn't automatically change.

Hope that clears things up a little!

 

[Ste_Moore01]

Here you go.

Private Sub Workbook_Open()
    Dim name As String
    name = Environ("UserName")
    Sheets("USER DETAILS").[B1] = name
End Sub

That is the code I have in "ThisWorkbook" and it automatically runs when opened.

The "Sheets("USER DETAILS").[B1] = name" part pastes it in the work book and you'll need to modify this for where you want to paste it.

Please note, this only tells you the username.

 

[Andrew Poulsom]

At the end of your macro put:

Application.Calculate

 

[Ste_Moore01]

Thank you Andrew and thank you kkjensen!

You both been a great help!