I guess its because Range("a" & r) is not interpreted correctly. But how do I transfer c.Row from
my first code to the code on the button?

That is not your problem. A text box value is a string, so you need to cast (change) the variable
to a different type. So, change

Sheets(1).Range("a" & r).Value = Format(Me.TextBox1.Value,
"mm-dd-yyyy")

to this:


Sheets(1).Range("a" & r).Value = Format(CDbl(Me.TextBox1.Value),
"mm-dd-yyyy")


--
HTH,
Bernie
MS Excel MVP


"Jan Kronsell" wrote in message
...
In ThisWorkbook I have the following code:'

Public r As Integer

Private Sub Workbook_Open()
For Each c In Range("a2:a100").Cells
If c.Value = "" Then
Exit Sub
Else
r = c.Row
If c.Value <= Date Then
If MsgBox("Deadline er overskredet med " & Date - Range("a2").Value & _
" dage." & vbCrLf & "Ønsker du at reagere?", _
vbCritical + vbYesNo) = vbYes Then
Load OD
OD.Show
Unload OD
Else
Exit Sub
End If
End If
End If
Next c
End Sub

For each cell in range("a2:a100") it test if the date in the cell. is before actual date. If it is
it displays a messagebox. Replying Yes in the msgbox, displays a userform. This works okay.

On the OK button in the userform, I have the following code:

Public r As Integer
Private Sub CommandButton1_Click()
If Me!TextBox1 < "" Then
Sheets(1).Range("a" & r).Value = Format(Me.TextBox1.Value, "mm-dd-yyyy")
End If
If Me!TextBox2 < "" Then
Sheets(1).Range("b" & r).Value = Me.TextBox2.Value
End If

If Me!TextBox3 < "" Then
Sheets(1).Range("c" & r).Value = Me.TextBox3.Value
End If

If OptionButton1 = True Then
Sheets(1).Range("d" & r).Value = "Undersøg"
Sheets(1).Range("d" & r).Interior.ColorIndex = 16
End If

If OptionButton2 = True Then
Sheets(1).Range("d" & r).Value = "Grib ind"
Sheets(1).Range("d & r").Interior.ColorIndex = 17
End If

If OptionButton3 = True Then
Sheets(1).Range("d" & r).Value = "Acceptabel"
Sheets(1).Range("d & r").Interior.ColorIndex = 18
End If

If OptionButton4 = True Then
Sheets(1).Range("d" & r).Value = "Kontakt kunde"
Sheets(1).Range("d" & r).Interior.ColorIndex = 19
End If

Unload Me

End Sub

My problem is, how to get tghe content of the userform written to cells in the same row as is
currently tested in the first code? As is I get an Application defined or obejct defined error in

Sheets(1).Range("a" & r).Value = Format(Me.TextBox1.Value, "mm-dd-yyyy")

I guess its because Range("a" & r) is not interpreted correctly. But how do I transfer c.Row from
my first code to the code on the button?

Jan