Problems with repeated call of userform

[Jan Kronsell]

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

 

[Bernie Deitrick]

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")

 

[Jan Kronsell]

I tried your solutin, but now i get runtime error 13 - Type mismatch in
stead.

Jan

 

[Jan Kronsell]

I tried your solutin, but now i get runtime error 13 - Type mismatch
in stead.

I also get the application or objects error in trhis statement alone

Sheets(1).Range("d" & r).Interior.ColorIndex = 16

but if I hardcode "D2" in the code, it works ok.The same goes for trhe other
statements

Jan

 

[Jan Kronsell]

And I can see that r is set to 2 in the first code, butr appears as 0 in the
second, so the value is not transferred between codes.

Now I solved the problem by placing an invisible textbox called rn on the
formula, and changing the first code to include

OD.rn = c.Row

and the lines in the click code to

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

But thanks for your help anyway.

Jan