Peer,

The solution is already in your code:

Set ws = Worksheets.Add

But you should use it this way:

Set ws = Worksheets.Add
ws.Name = postnaam.Text....

If all this code is run at the same time, you still have the name available:
worksheets("total sheet").range("D8").formula = "='" & ws.name & "'!$D$8"

HTH,
Bernie
MS Excel MVP

"Peer " wrote in message
...
I've quite a problem I think.
I've made a userform in which the name of a new sheet can be defined,
as follows:

Dim ws As Worksheet
Set ws = Worksheets.Add
ActiveSheet.Name = postnaam.Text (postnaam is the name I've given to
the textbox in which the name of the sheet is defined by user)

Later on I want to fill in a formula in another (total calculation)
sheet with a reference to the new sheet, for example:

worksheets("total sheet").range("D8").formula = "='name of the
sheet'!$D$8

The problem I now encounter is that I don't know which name the user
will use for his new sheet, so I can't make an absolute reference, yet
this is what excel needs for automatically updating the value in that
cell.

Please help me with this problem, I can't find a solution!

tnx in advance,
Peer


---
Message posted from http://www.ExcelForum.com/