Hi everyone, Im neww to this forum and was hoping you could help me with.

I have a form that takes various info, check boxes, dates etc.. and on pressing the OK button transfers the data to my spead sheet, all works as I would like however I have decided that one of the textboxes (additional comments) requires to much text to be displayed neatly in the spreadsheet, so what I would like to do is when I press OK it sticks in the required cell as a comment tag so I can view it by hovering over it if I need it. I am having a real headache making this happen.

PS fairly new to VB but I feel this is probably a simple function but I cant find it.

I can convert to a comment if I select the cells then run simple macro like

Sub Test()
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Columns.Count
Selection.Cells(i, j).AddComment (Selection.Cells(i, j).Text)
Next
Next
End Sub

but I need it to be be automatic, ie converted to comment when I hit OK, also when I hit ok my form currently populates the next available free row. so each time I hit ok the comment box to convert moves to the next row.

Can anyone help.

Cheers

Dan



heres the code I have for the OK SUB "phonetextbox.value is the text box in question"


Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 Active
Sheets(1).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 8).Value = PhoneTextBox.Value ' this is the textbox i need to convert to a comment
Cells(emptyRow, 5).Value = ClubComboBox.Value
Cells(emptyRow, 2).Value = DateComboBox1.Value
Cells(emptyRow, 3).Value = DateComboBox2.Value
Cells(emptyRow, 4).Value = DateComboBox3.Value


If OptionButton1.Value = True Then
Cells(emptyRow, 1).Value = OptionButton1.Caption
End If

If OptionButton2.Value = True Then
Cells(emptyRow, 1).Value = OptionButton2.Caption
End If

If OptionButton3.Value = True Then
Cells(emptyRow, 1).Value = OptionButton3.Caption
End If

If OptionButton4.Value = True Then
Cells(emptyRow, 1).Value = OptionButton4.Caption
End If

If OptionButton5.Value = True Then
Cells(emptyRow, 1).Value = OptionButton5.Caption
End If

If OptionButton6.Value = True Then
Cells(emptyRow, 1).Value = OptionButton6.Caption
End If

If OptionButton7.Value = True Then
Cells(emptyRow, 1).Value = OptionButton7.Caption
End If

If OptionButton8.Value = True Then
Cells(emptyRow, 1).Value = OptionButton8.Caption

End If



If OptionButton9.Value = True Then
Cells(emptyRow, 6).Value = OptionButton9.Caption
End If

If OptionButton10.Value = True Then
Cells(emptyRow, 6).Value = OptionButton10.Caption
End If

If OptionButton11.Value = True Then
Cells(emptyRow, 6).Value = OptionButton11.Caption
End If

If OptionButton12.Value = True Then
Cells(emptyRow, 6).Value = OptionButton12.Caption
End If

If OptionButton13.Value = True Then
Cells(emptyRow, 6).Value = OptionButton13.Caption
End If

If OptionButton14.Value = True Then
Cells(emptyRow, 6).Value = OptionButton14.Caption
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value


End Sub